I am currently working on creating regression tests for an existing java/spring project that uses a sybase database. I was asked to spin an H2 database for the tests and run the existing queries on it to test the results.
In summary, the code calls the real, existing APIs but passes the H2 database as the db source instead of sybase (by using
-DdbServer=tcp:[host]:[port]/./testDatabase
when calling the spring application hosting the code).
The problem I'm facing is that a couple of the existing sql queries called by the APIs use select COLUMNS into #tempTable, which unfortunately doesn't seem to be supported by H2.
My question is: How can I go around this problem? Is there another equivalent command I could use in the queries that would be supported by BOTH sybase and H2? Is there a way to create a wrapper around the H2 database so that it could catch the "select into" statement and change it into an H2 supported statement?
I've looked online but I couldn't find anything. I know that H2 supports create table NewTable as SELECT but unfortunately Sybase does not, and I specifically need the same SQL query to work for both.
Any and all help will be appreciated!
This is what I ended up doing:
I changed the sql from
select columns
into #tempTable
from OtherTable
to:
create #tempTable(columns columnsType)
insert into #tempTable(columns)
select (columns) from OtherTable
This is supported by both H2 and Sybase and doesn't seem to affect the performance. It is a bit more annoying as you have to create all the columns for the new table but otherwise the change is pretty straight forward.
The potential issue with this is that H2 wants a semi-colon at the end of the create statement, while Sybase does not, so I fixed that by creating a custom JdbcOperations class that only gets called when using H2 and adds the semicolons before processing the query (I also had to use it to deal with the hash symbol (#) that Sybase uses to create temporary tables).