How to test sql scripts are standard sql in junit tests?

Question

Is there a way to verify that SQL scripts contain standard SQL with java / junit checks?

Currently we have sql scripts for creating a database, etc. in db Postgres, but when using hsqldb everything fails. So I wonder if there are any java testing tools if SQL expressions are standard sql.

Or would it just be wise to create different sets of scripts for the database provider? If so, is there a way to check if this script works with postgres / hsqldb?

Answer 1

The H2 database supports various modes that can help you with postgres testing. I found that our sql often contains functions that are not supported, but H2, but you can create your own "stored procedures" that actually call the static Java method to get around this. If you want to support different database providers, you have to go along the vendor-specific script route, unless you are doing really basic queries.

If you have the resources available, I would recommend setting up a full-fledged UAT environment that you can use to test against the live postgres database, since even slight differences in db configuration can unexpectedly affect query plans.

Answer 2

I usually made a very simple java-wrapper that tests this code using a localhost connection with some standard user / password settings.

Remember to use a temporary database or a well-known test database so that your tests are not ruined by anything important.

The reason above is because I had a need for specific databases (non-standard functions, etc.).

If you want to test the standard sql material for junit tests (e.g. syntax, selects, etc.), I would like to use the built-in sql database in java (only for service memory). Thus, it is easy to check many things without having to install db and also without the risk of destroying other installations.

Answer 3

It looks like you are looking for a SQL parser and validator. The only Java SQL parser I am familiar with is Zql, but I have never used it.

A similar question was asked at the beginning of last year, and the best answer was written by your ANTLR parser.

Answer 4

Different vendors provide various additional features in their SQL implementation.

You can select a set of tests for testing. Then use http://dbunit.sourceforge.net to simplify the test case.

Answer 5

The best tool for checking SQL statements for compliance with the SQL standard is HSQLDB 2.0. This is especially true for data definition statements. HSQLDB was written to the standard, as opposed to adopting bits of the standard in several versions.

PostgresSQL is slowly moving towards standard SQL. But it still has some "obsolete" data types. HSQLDB allows you to define types using 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 custom types and functions to support alternative database syntax.

Answer 6

ZQL works great with Junit ..

ZqlParser parser = new ZqlParser(any input stream); try{ parser = parser.readStatement(); }catch(ParseException e){ // if sql is not valid it caught here }