in reply to Re: Re: SQL/DBI
in thread SQL/DBI

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.

Replies are listed 'Best First'.
OT SQL differences
by Arguile (Hermit) on Oct 20, 2001 at 14:50 UTC
    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]
      Last I checked, MySQL didn't even support joins, much less views or even *gasp* stored procedures.
[reply]

In Section Seekers of Perl Wisdom