|Perl Monk, Perl Meditation|
Re: Re: SQL/DBIby Arguile (Hermit)
|on Oct 20, 2001 at 11:22 UTC||Need Help??|
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:
[ ] 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.