in reply to SQL/DBI

Since most useful databases support SQL I don't understand how the syntax changes between them. mSQL, mySQL, PostGreSQL, Oracle all support SQL 92. Some of them omit commands, like leaving out rollback if they don't support transactions, but that isn't a different syntax, just a cut down command set.

Do you actually have an example of a 'syntax change' that you could show us? In your example that query will run on all the databases that I mention above.

The delayed syntax checking is annoying but nothing stops you from logging into the database from a command shell and running your queries directly to check. There are large parts of Perl that only explode when you execute them, so this is at least consistent.

Finally there is a module that does what you want, it's called Tie::DBI. It presents tables as hash variables and you can write to or read from them.

You could conceivably write a module that checks your SQL at compile time, which would be kind of convenient.

____________________
Jeremy
I didn't believe in evil until I dated it.

Replies are listed 'Best First'.
Re: Re: SQL/DBI
by Arguile (Hermit) on Oct 20, 2001 at 11:22 UTC

    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.
[reply]
[d/l]
[select]
      I wasn't really thinking about extensions - I was talking about core SQL. Obviously if you use DBMS specific features they aren't going to be portable. However most DBMS that I have used (at least the four I list) make more than a passing attempt to be SQL compatible. I've never used MS SQL (and with luck it may never happen) so I don't know if it accepts LIMIT clauses. Does it? Then your second example would run on MSSQL and everyone would be happy.

      As you say DDL is a huge problem but I usually do the schema separately to the program and yes, they have to be ported for each DBMS.

      I do agree with you that views are a good idea, and again most good DBMSs should support them. I see most of the problem to be unsupported language features, rather than fundamental incompatibilities. T

      ____________________
      Jeremy
      I didn't believe in evil until I dated it.

[reply]
        My initial post, and more so this one go off on a bit of a tangent.

        You're right, most DBMSs are pretty good at supporting a core set of syntax. The problem is the core is so incredibly limited. The reason I like using the example in the previous post (and below) is it demonstrates a very, very common task with no standard whatsoever.

        • MS SQL uses TOP (so does Informix IIRC)
        • Oracle uses ROWNUM and you specify offset and limit in the WHERE clause (easiest)
        • Postgres uses LIMIT and OFFSET or the short form LIMIT x, y
        • MySQL can only handle the short form of LIMIT

        To do anything over trivial, and be even close to effecient, it's nigh on impossible not to get into a dialect of SQL.

        Even 'core' SQL implementations can vary quite dramatically. A great example of this are the JOIN and UNION statements. Some DBMSs won't properly implement the various join types, which will result in the wrong results being returned. Others will perform an implicit sort on the PK for some strange reason. UNION can be even more subtle as some DBMSs perform an implicit DISTINCT on the result set. This might not seem to be a syntax change at first glance, but you can often get the correct (or at least de facto standard) result returned by altering the query for that DBMS.

        While I agreed with most of your post, I think I had a bit of a knee jerk reaction to your statement about SQL. As a DBA who has to switch between all sorts of DBMSs it's a point of frustration to me :) While I doubt it's as bad as the browser wars of the 90's were for JS and HTML/CSS people it can seem close.

[reply]

In Section Seekers of Perl Wisdom