Archive for May, 2009

Spring JDBC and Sybase Temp Tables

Friday, May 8th, 2009

I had a problem with using temp tables and Spring JDBC code lately. The main problem was that in Sybase a temp table can be accessed only on the same connection which created it. When executing several SQl statements in default Spring JDBC usage (DriverManagerDataSource using connection pool) could give different connections from connection pool for each of the statements. So in default implementation we cannot garantee that a temp table is visible. One possible fix is to use transaction (@Transactional) which would ensure that same connection is used. In our case, Sybase is configured in a way that does not allow us to drop temp table in the same transaction as create and select from it.

com.sybase.jdbc3.jdbc.SybSQLException: The ‘DROP TABLE’ command is not allowed within a multi-statement transaction in the ‘tempdb’ database.

 So the only way out was to us SingleConnectionDataSource for one method as described in