SQL Validator

clever writes: "Since open standards are good for pretty much everyone except closed source, multi billion, software companies that try to lock their customers into their proprietary technology I thought that the Slashdot readers might welcome a chance to help shape the next SQL-standard. The Mimer SQL Validator lets you validate any given SQL-statement against SQL-92, SQL-99 and, get this, SQL-200x (Draft spec, 2002-01-11). It stores your statement, if you'll allow it, and it will be used by the ISO SQL-200x standards committee to make statistics on commonly used features and errors to be used in the standards work. So, don't ask what SQL can do for you. Ask what you can do for SQL. In the process you will find out the proprietary features that you are already using."

Comments

Isn't this a sales pitch?

[PoiBoy]

I looked at the examples on their site, and several of them gave results showing syntax used which is outside the core SQL 99 standard, and they mentioned Mimer SQL several times. I can't figure this out: are they trying to get you to use their version, or what? What's this Mimer SQL stuff?

Re:Isn't this a sales pitch?

[fredrika]

No, this is exactly the point. The sql validator validate the SQL and tells you what is and what isn't standard SQL. If Mimer specific SQL is used that's printed. It's the examples that's not standard SQL that is interesting as far as I can see.
For example, the following example gives to warnings saying that Mimer SQL vendor extension is used and that Constraint management is used:
CREATE TABLE t (c BIGINT CONSTRAINT c_gt_0 CHECK(c > 0))

If you change it to the following it will say that Constraint management is used and that a vendor reserved word is used (varchar2 is Oracle specifik I think).
CREATE TABLE t (c VARCHAR2 CONSTRAINT c_gt_0 CHECK(c > 0))

That the result says the statement is outside Core SQL 99 doesn't mean it's not standard SQL. The Core SQL 99 is just a part of SQL 99.

Mimer SQL is just a relational database.

Great tool, but not solving the problem...

[bigmouth_strikes]

select * from
pretty much conforms to most SQL standards, so now we only need to stop developers from writing bad, albeit conforming, SQL.

Re:Great tool, but not solving the problem...

[smittyoneeach]

Bad? Why not write

SELECT COUNT * FROM ( ByzantineSubqueryString );
or
SELECT * FROM ViewICreatedToEncapsulateAFewThings;

As with many tools, I'm arguing that * has its place.

SQL validator

[possible]

If ANSI and ISO really wanted to help SQL, they would make the fricking SQL standards available in electronic form without cost, instead of charging hundreds of dollars for them.

I remember at the last company I worked for, we had one battered copy of the SQL92 standard (which is basically just an annotated BNF grammar) that we shared among the whole office.

Re:SQL validator

[Prowl]

right on!!! mod this up. post it on the frontpage. start

Re:SQL validator

[MrBlack]

Absotively!!! After hearing so much about what was and wasn't ANSI 92 compliant I went to the ANSI site. I was very disappointed to see they were charging for the standards. I think sql92 was $20 for the electronic version. Not much, but enough to make me think twice.

Re:SQL validator

[axlrosen]

I think you shouldn't be allowed to criticize someone for charging money for something, without explaining how they *should* get their money. There are alternative ways to fund a standards organization, but each has their disadvantages too...

Reminds me of HTML/CSS validators and standards

[krs-one]

This reminds me of the HTML/CSS validators that the W3 (w3.org) set forth since HTML and CSS were concieved. However, since they set for the standards, it seems everyone should follow them. Obviously, this is not true. IE support for CSS2 is almost non-existant, with Mozilla coming in with the most support.

I think that this will become the same way. There will be many databases out there that support some of the standards, but not all of them. Some databases will be far ahead of others in standards support.

I am what you would call a standard zealot for just about any standard. Standards help any programmer (or any person for that matter) when doing anything. Standards (or at least what they _should_ do) set out a group of rules that everyone should follow in order to make portability extremely easy (again, this is from a programmers POV). However, you have different companies who want to create their own standards (and then not even follow them, MS comes to mind :)) and some companies who honestly try to support all of the standards they can (Mozilla).

I hope that DB's start to support the most current standard and fast. That way, writing abstracted DB code in any language will become *extremely* easy.

-Vic

Re:Reminds me of HTML/CSS validators and standards

[smittyoneeach]

I hope that DB's start to support the most current standard and fast. That way, writing abstracted DB code in any language will become *extremely* easy.

And does your technical argument run afoul of the business model? Being able to port anything to Insert_DB_Here might or might not provide any ROI.

Re:Reminds me of HTML/CSS validators and standards

[Chacham]

HTML and SQL are very different, and I do not think they can be compared when it comes to standards.

HTML has very few common clients. So, only the smaller clients care about the standards, the big fish, just write their own, and HTML authors are forced to follow.

SQL is different. There are quite a few databases out there. It even got to the point where they created ODBC. No one or two databases dominate the market.

Thus, the push is for an HTML standard is wanted more by people than companies. The push for a(n) SQL standard is more by companies than people. When people want a standard, it's free. Not so with companies.

HTML is a presentation language, and usally comes *after* the need is already there. So, people always want to get the standards up to date, and are easier to write, since it was already implemented.

SQL 88 seems to be the most common. How many even care to go further than that, because the basics are already there? While later standards are nice, they are not nearly in need of updates as much as HTML is.

Thus, HTML standards are public before they're even written. SQL is written from the standards. This also means, that companies want to pay for the SQL standard, whereas companies would rather get eveyone to support their HTML, forcing them to distribute it for free.

Re:Reminds me of HTML/CSS validators and standards

[wilhelm9]

In a long term it is a economic disaster to use proprietary features for a product or project with a reasonably long life length. Proprietary vendors use the same strategy as drug dealers. Just get the programmers to use our proprietary features, and then they're stuck with us for their life. Even the real drug addicts are better off, they can (after all) quit.

The reason this isn't understood in the industry today is because of its age. I am sure, in 20 years standards will be something natural for software engineers. But, we won't see any new Microsoft or Oracle companies (with 50% profit margins) either, since competition among software vendors will be tougher.

offtopic...

[Person 112375793]

... If you write lots of SQL it might do you some good to check out a data modelling language like Xplain. You can even get software that will take your modelling scripts and convert them to various SQL dialects.

heres one...

[edrugtrader]

SELECT * FROM comments
WHERE article='this one'
AND has_been_modded_up=true

^ this should obviously return 0.

Re:heres one...

[wilhelm9]

Yeah. But your query obviously used a feature that is not SQL-92 or Core SQL-99... :-)

ANSI charges for almost all the standards

[Anonymous Coward]

ANSI charges for all the standards. This, in my opinion, sucks badly.

However, you often CAN find a DRAFT (working version) of the standard available for free.

Products' SQL support is pathetic

[The Pim]

The common SQL DMBS implementations have truly abysmal standards support. They're really not even close. Oracle doesn't support the standard outer join syntax (and doesn't support full outer joins at all). It gets confused between the empty string and NULL. MS SQL Server doesn't support the standard concatenation operator (using '+' instead of '||'). PostgreSQL honors non-standard escape characters in string literals. And those are basic language elements--forget about anything interesting like domains and system tables. Basically, if you pick a random feature from the standard, you're likely to find missing or broken support in many or most implementations. (In my experience, Oracle is the worst, and PostgreSQL is the best.)

I'm not going to get into why support is so bad (my glib answer: databases suck). But I don't know of any other major standard that is so routinely disregarded. It's been a fond hope of mine that PostgreSQL would commit to standards support and (slowly) pull the rest of the industry along. But I see no indication of that.

I program SQL with two books at my side: "SQL Instant Reference" (Gruber) and "A Guide to the SQL Standard" (Date and Darwen). To me, they are lone voices of sanity! (It's hard to find a book that covers SQL the standard, not some implementation.) When I can't write a portable SQL statement because of product infidelities, I add a workaround with a big comment full bile and invectives. It's good for the soul. :-)

I hope this project helps build some momentum for SQL standards support. I'm not doing an SQL projects right now, but when I do, I'll definitely support this effort.

^honors^requires

[brlewis]

With PostgreSQL, you must escape backslashes in strings. In standard SQL you must not. This is one point where PostgreSQL is as bad as MySQL.

Re:^honors^requires

[The Pim]

It works both ways:

• It's a bug that it honors '\n'
• It's a bug that it requires '\\'

SQL Pet Peaves

[Tablizer]

The large syntax difference between INSERT and UPDATE. They should be interchangable. It makes it hard to reuse SQL strings in code. What the fwlk were they thinking?

It would also be nice if there was a way to have it replace if already there, otherwise insert.