ANSI Standard SQL

[JohnLewis25]

Is it possible to configure mySQL in such a way that it only processes ANSI standard SQL, and produces an error for anything else?

I'd like to do this with a view to ensuring that code is portable to other platforms.

 

[sleipnir214]

Sure, edit the source code and recompile. ______________________________________________________________________

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[JohnLewis25]

I don't understand.

Are you saying there IS a way to ensure ANSI standards are enforced or are you saying you can only make the code portable by editing it?

 

[sleipnir214]

Nobody's database server adheres to strict ANSI SQL92. Everybody enhances the existing spec, and modifies the behavior of the SQL commands that exist in the spec.

The only way I know of to modify MySQL's behavior so that it does only strict ANSI SQL92 and returns errors on everything else is to modify the source code of the database server itself. Then compile and run MySQL from your customized source code. It shouldn't take you more than a couple of months, provided that you are up on the internals of database servers in general, and MySQL's internals in specific.


What most people do to make their DBMS access code portable is to modularize their code, and write plugins that provide functionality to a specific database server.
______________________________________________________________________

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[JohnLewis25]

Ok. I see what you mean now - edit the source code of MySQL itself. No thanks

I'm going to do what you say and modularize my code - in fact I would anyway, even without this issue.

The specific difficulty I've been having is as follows. When I so a query like

SELECT * FROM TAB01 WHERE NAME = 'BOB'

I expected it to return only rows in which the NAME column contains the value 'BOB' (i.e. case sensitive - this is what the other SQL databases I've used do) whereas in fact it returns rows with values such as 'Bob' and 'bob' as well. One solution I've found to this is to use

SELECT * FROM TAB01 WHERE BINARY NAME = 'BOB'

(is this the best way? Are there any alternatives?)

But, of course, this produces an error on other database systems because the use of the BINARY keyword in this way is not allowed.

I've also found that if I use BINARY in the CREATE TABLE statement which originally creates the table then the original SELECT does what I want.

 

[sleipnir214]

Use of the BINARY keyword is MySQL's standard way of handling case-specific lookups. But there is no single cross-platform way of forcing it.

Unfortunately, I don't know what the SQL standard is -- I've never been curious enough to pony up the thousand dollars or more that the spec documents cost. ______________________________________________________________________

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[JohnLewis25]

If no implementations actually enforce the full standard then it would probably be a waste of money.

Thanks for your help anyway.

Should I be able to use the CAST function in MySQL? Somebody I know is telling me I should, but I keep getting errors whichever way I try it.

 

[sleipnir214]

What version of MySQL are you running? According to

http://www.mysql.com/doc/en/Cast_Functions.html

CAST was added to MySQL in version 4.0.2 ______________________________________________________________________

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[JohnLewis25]

That explains it!

My system is at version 3.23.49, although somebody told me it should work from 3.23.2.

Upgrade required, I think.

Thanks.