Spring provides a wonderful mechanism to standard the way that connections to an underlying database are managed. The JDBC Template abstracts the developer from having to understand any particular vendor, and provides a neat way to translate vendor specific SQL errors into a standardised format.

It doesn’t go far enough. SQL can no longer be treated as a standard, but instead should be viewed as a recommendation. The differences in SQL dialects between vendors is unbelievable, and inexcusable. However there isn’t a standardised translation service available. In an earlier example I outlined the different SQL statements required to allow MySQL and Oracle to update the same row in the database:

MySQL

UPDATE TABLE1(date, username)
VALUES ("2009-01-01", "fred");

Oracle

UPDATE TABLE1(date, username)
VALUES(TO_DATE("01-JAN-2009"), "fred")

Clearly this it is undesirable to have to write these different SQL statements in an application, there is too much scope for error and poor testing to let a faulty statement through. We could use Hibernate, represent the whole thing in objects, mapping files and HQL, but that’s the programming equivalent of taking a sledgehammer to a nut. It would work (usually) but it will certainly not be pretty, nor light weight.

What is really needed is a translator, similar to the concept of Hibernate’s dialects. The translator could be part of the template, either determined automatically from the database, or wired in by configuration. Developers would then simply execute statements on the template as normal, with it handling the conversion to the relevant dialect for the database:

template.update(
    "UPDATE TABLE1(date, username) "
    + "VALUES (\"2009-01-01\", \"fred\");");

This would make the JDBC template a universal fit for accessing databases.

As we all know, the ANSI SQL standard is now the computer equivalent of English. Its claimed to be universal, major databases claim to speak it; but alas much like English has now splintered into several derivative langauges, SQL has many dialects.

I recently hit this issue when I tried to migrate a simple application from using MySQL to Oracle. Yes I know its going backwards, the reasons for the move were beyond my control. This application made the simplest of simple calls to the database, e.g.

INSERT INTO TABLE1(date, username)
VALUES ("2009-01-01", "fred");

Yes its that basic. One would assume that this would work on all major databases that claim to speak SQL, but you would be wrong. Running this on Oracle (using the thin client) produced an error. Aparently the ; at the end of the statement is not recognised. Hmm… Ever seen the ANSI SQL-92 standard Oracle? So remove it and we move to iteration 1 of the conversion to Oracle

INSERT INTO TABLE1(date, username)
VALUES ("2009-01-01", "fred")

Does it run? No.

What’s wrong this time? The date. Lets see, the date is in ISO 8601 format, so it must need something else. Aha, the TO_DATE function, ok

INSERT INTO TABLE1(date, username)
VALUES (TO_DATE("2009-01-01"), "fred")

Invalid month.

What? 01 is an invalid month? January anyone? Revert to google. Ok, different format for dates. Seems like ISO 8601 dates are not welcome in the land of Oracle.

INSERT INTO TABLE1(date, username)
VALUES (TO_DATE("01-JAN-2009"), "fred")

Success! Unbelievably 3 iterations of this simple SQL statement were required to get it to work on Oracle, when it was previously working on MySQL.

I’ve left out the other example which made use of an autoincrement column in MySQL. Selecting from a sequence from some table named DUAL just makes no sense. Why DUAL? How did the Oracle guys come up with this? It breaks my heart.

Seriously database people, this is the equivalent of me going up to the Queen of England and saying “I CAN HAS CHEEZBURGER?”