Is there any way to test that SQL scripts contain standard SQL with java/junit tests?
Currently we have sql scripts for creating a database etc. in a Postgres db, but when using hsqldb everything fails. That's why I wonder if any java tools exist for testing if sql statements are standard sql.
Or would it just be wise to create different sets of scripts per database vendor? If so, is there a way to test if a given script works with postgres/hsqldb?
I've usually made a very simple java-wrapper that tests this code by using a localhost-connection with some standard user/pass settings.
Remember to use a temporary database or a known test-database so your tests doesn't destroy anything important.
Reason for above is that I have had the need for specific databases (non standard features etc).
If you only want to test standard sql-stuff for junit tests (like syntax, selects etc), I would consider using a embedded sql database in java (ususally memory only). That way it is easy to test lots of stuff without the need to install a db and also without the risk of destoring other installations.
The H2 database supports different modes, which may help you with postgres testing, I've found that our sql often contains functions which are not supported but H2, but you can create your own "stored procedures" which actually invoke a static Java method to work around this. If you want to support different database vendors you should go down the vendor specific script route, unless you are doing really basic queries.
If you have the available resources I would recommend setting up a fully fledged UAT environment which you can use to test against a live postgres database, as even seemingly minor db configuration differences can impact query plans in unexpected ways.
It sounds like you're looking for an SQL syntax parser and validator. The only Java SQL parser with which I'm familiar is Zql, but I've never actually used it.
A similar question was asked early last year, and the best answer there turned out to be writing your own parser with ANTLR.
Different vendors expose different additional features in their SQL implementation.
You may decide the set of databases to test. Then use http://www.dbunit.org/ to simplify the testing job.
The best tool for checking SQL statements for conformance to Standard SQL is HSQLDB 2.0. This is especially true with data definition statements. HSQLDB has been written to the Standard, as opposed to adopting bits of the Standard over several versions.
PostgresSQL has been slowly moving towards standard SQL. But it still has some "legacy" data types. HSQLDB allows you to define types with the CREATE TYPE statement for compatibility with other dialects. It also allows you to define functions in SQL or Java for the same purpose.
The best policy is to use standard SQL as much as possible, with user-defined types and functions to support an alternative database's syntax.