Spring JDBC and Sybase Temp Tables

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 http://forum.springsource.org/showthread.php?p=239922&posted=1#post239922

Leave a Reply

You must be logged in to post a comment.