If only that were so. There are as many 'dialects' of SQL as there are DMBS's pratically. Without even getting into extensions such as P/L SQL, T-SQL, PgSQL there are many differences. SQL92 is like the HTML/CSS specifications, Oracle and MSSQL being the Netscape 4.x and IE of the DBMS world with Postgres, mySQL, mSQL, etc. somewhere in the mozilla, Opera, etc. area ;)

While the main DML statements are generally interoperable, various keywords and clauses are specific to packages. For example:

SELECT TOP 10 *
FROM table

SELECT *
FROM table
LIMIT 10
OFFSET 1

SELECT *
FROM table
LIMIT 10[, 1]

[download]
[ ] denotes an optional piece

The first works in MS SQL the later forms work in Postgres and MySQL (though Pg also supports the short form). I might even be confusing myself as I have to talk Informix (akin to MS in this regard I believe), Oracle, or DB2 on occasion as well. Now it's not just big bad MS doing this, I only chose the example because it was easy syntax. Pratically every DBMS has 'standard' command, in the sense they're used often, that are subtly (or not so subtly) incompatible with their peers.

DDL is a whole different story, it's far far worse for compatibility as it's more directly tied to the internals of the DMBS you're working with (a quick example is datatype names).

Now skipping to the original question

As you've already stated, portability isn't an issue to you -- or you wouldn't ask for MySQL specific. And variable interpolation also isn't an issue as you can't check dynamic statements except on runtime (I'm not talking data binding with ? here). So why not just skip the whole prepare section? Store the SQL in the database itself as a stored procedure or view. That way the execution plan is already worked out; a grand total of once for the entire application. You can then just call it via do without any penalties.

Note: I don't use MySQL much as it lacks the features I need, so I'm not sure it has views or something similar. It didn't have SPs the last time I used it.

In reply to Re: Re: SQL/DBI by Arguile
in thread SQL/DBI by tomazos

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.