Testing Multiple Database Vendors
Take this query:
SELECT *
FROM MyTable
WHERE MyColumn = 'SomeValue'
ORDER BY SomeFakeQualifier.MyColumn DESC
It seems like SqlServer is just ignoring the qualifier in this case. If you add JOIN
, then he will consider it.
This really isn't a problem until you want to make your queries viable for the DBMS vendors. Oracle, for example, will complain, rightfully, that the qualifier is invalid.
Yes, we are using an ORM which removes a lot of this, but we still need JDBC for some actions (this is 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 the query named JPA with the query provided by JDBC, but left the object name instead of the table name.
So my guess is the question is: has anyone else come across this? If so, what is the best way to test your code to make it work in the "main triple" (SqlServer, Oracle, DB2) DBMS? We have a QA team, but it seems like there should be a better way to unit test for these features.
Please note that we are always trying to enforce ANSI SQL writing to avoid problems, but some things like the above problem can slip through
Hope this makes sense. I can provide more context if needed.
TIA
a source to share
Your solution here is unit tests of your queries combined with continuous integration . You have your requests as JDBC code. It's easy enough to write unit tests to connect to your respective databases and run the queries in such a way that you can at least make sure they run, even if you don't want the results that are the standard you're talking about ..
Perhaps you can expand this to measure performance and / or results, but I find these kinds of tests are extremely fragile and tend to break very, very quickly as new changes inevitably occur.
a source to share