My response usually takes about 20 minutes. The first 18 minutes are comprised solely of me standing with my feet shoulder-width apart, back slightly bowed, arms raised and fists clenched, face turned skyward, screaming a single unbroken roar of sheer frustration and unbridled hatred. I then spend the last two minutes explaining the following answer: standards are frameworks for communication, not instructions for execution.
SQL and ODBC (and JDBC, etc. etc. etc.) define ways for two systems to communicate with each other. They do NOT define how either party in the conversation should actually behave. Thus, a system that understands SQL or ODBC is not necessarily going to behave the way you want it to. Heck, it might not even understand what you're asking of it.
To re-use an old example, consider the case sensitivity problems I've described in previous posts. SQL Server understands SQL and ODBC, but as those examples show, it isn't necessarily going to execute your query the way you want it to. And that problem can arise just between two different installs of the same RDBMS - imagine the problems trying to move to an entirely different system!
Let me close with a fun little analogy:
ODBC is to SQL as ears are to English
All humans (or almost all, anyway) have ears. Americans and Australians both "speak English". But would you expect an American to be able to communicate perfectly with an Australian? They might be able to figure things out, but it wouldn't happen perfectly the first time. They both share common frameworks for communication, but not all the words mean the same thing.
So, after surveying a half-dozen or so databases, here's what I came up with.
Continue reading "Updating One Table from Another" »
Beware Quoted Identifiers). Object names in Microsoft SQL Server, for example, may or may not be case sensitive, depending on the collation chosen for the database (or, in pre-SQL2005, the collation chosen for the entire database instance).
Assume you create a table as follows:
CREATE TABLE foo(x varchar(10))
Now consider the following queries:
SELECT * FROM foo
SELECT * FROM Foo
Whether one or both of these queries will succeed will depend on the collation setting for the database to which you're connected. For application developers, this can be a nightmare; requiring that the SQL Server database be either case-sensitive or case-insensitive would seem to be the only way to ensure that an application can function predictably.
As if that weren't unpredictable enough: MySQL goes one better and makes table name case sensitivity dependent on the operating system. If your MySQL server is running on Linux or Unix, your table names will be case sensitive. If it's running on Windows, they'll be case-insensitive. Index and other object names will always be case-sensitive, however.