Kevin Grittner wrote:
Management has simply given a mandate that the software be independent of OS and database vendor, and to use Java to help with the OS independence. ... we write all of our queries in ANSI SQL in our own query tool, parse it, and generate Java classes to run it.
A better solution, and one I've used for years, is to use OS- or database-specific features, but carefully encapsulate them in a single module, for example, "database_specific.java". For example, when I started supporting both Oracle and Postgres, I encountered the MAX() problem, which (at the time) was very slow in Postgres, but could be replaced by "select X from MYTABLE order by X desc limit 1". So I created a function, "GetColumnMax()" that encapsulates the database-specific code for this. Similar functions encapsulate and a number of other database-specific optimizations. Another excellent example: I have a function called "TableExists(name)". To the best of my knowledge, there simply is no ANSI SQL for this, so what do you do? Encapsulate it in one place. The result? When I port to a new system, I know exactly where to find all of the non-ANSI SQL. I started this habit years ago with C/C++ code, which has the same problem: System calls are not consistent across the varients of Unix, Windows, and other OS's. So you put them all in one file called "machine_dependent.c". Remember the old adage: There is no such thing as portable code, only code that has been ported. Cheers, Craig
