Spring JDBC and Sybase Temp Tables
I have a problem, which I think is common, but I haven't had any luck finding and answer to.
Here is what I need to do - API to get User Info -
1. create a temp table (like #my_user_ids)
2. populate the temp table with user Ids I'm interested in (could be over 500 ids)
3. use select inner join with temp table to get the info
4. drop temp table.
In regular SQL I can do that by just executing all of these statements on one connection, but I'd like to not break the spring jdbc and stay within that.
The API has other methods within the same class that does not need single connection (like get all user info) - so I don't want to switch to SingleConnectionDataSource for the entire class.
I tried making the call transactional (put @Transactional), whic allowed me to create, populate and use the temp table, but does not alllow me to drop it
(com.sybase.jdbc3.jdbc.SybSQLException: The 'DROP TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.)
What is the right way to do this within spring jdbc?