Testing across multiple DBMS vendors

Question

Take this query:

  SELECT *
    FROM MyTable
   WHERE MyColumn = 'SomeValue'
ORDER BY SomeFakeQualifier.MyColumn DESC

It seems that SqlServer just ignores the qualifier in this case. Should you add a JOIN, then it will consider it.

This really isn't an issue, until you want to make your queries viable across DBMS vendors. Oracle, for instance, will complain, rightfully so, that the qualifier is invalid.

Yes, we use an ORM which eliminates a lot of this, but we still need JDBC for some actions (it's the nature of our product and its support for dynamic queries). In fact, it is because of this that this issue came up - someone copied a JPA named query to a JDBC-provided query, but left in the object name instead of the table name.

So I suppose the question is: has anyone else come across this? If so, what's the best way to test your code to ensure it will work across the "main three" DBMS's (SqlServer, Oracle, DB2)? We have a QA team, but there seems like there should be a better way to unit test for these idiosyncrasies.

Note, we always try to enforce the writing of ANSI SQL to avoid issues, but some things, like the aforementioned issue, can slip through

I hope this makes sense. I can provide more context if necessary.

TIA

Answer 1

Your solution here is really unit tests of your queries combined with continuous integration. You have queries in the form of JDBC code. It's easy enough to write unit tests to connect to your relevant databases and run the queries in such a manner that you at least see if they execute, even if you don't care about the results, which is the standard you're talking about.

Arguably you can extend this to measure the performance and/or the results but I find these sorts of tests are extremely fragile and tend to break very, very quickly as more changes inevitably happen.