Temporary tables using JDBC with null ResultSet

Question

I am executing a stored procedure via standard JDBC Connection using MS SQL Driver version 3.0.

I have found that when I create and insert data into a temporary table the stored procedure doesn't execute properly.

The Java code won't throw a exception, but the javax.sql.ResultSet will be null.

The point of failure in the stored procedure is when I un-comment the INSERT INTO #TBL CLM_NAME VALUES('VAL')

When I execute the statement using SQL Studio Manager it executes without hassle and the data as expected.

Has anyone come across this or know why its the case?

Initially I thought its because of the SQL driver and I still think it is?

Thanks.

Answer 1

Maybe this will help:

It is recommended that if you want to use temp tables, you should not call "prepareStatement". You can directly execute the query from the statement object.

For example:

String sql = "select uuid, name from Component";

Statement stmt = dbCon.createStatement();
ResultSet rs = stmt.executeQuery(sql);

If you must call "prepareStatement", then you will need to create a real table and, if needed, delete the table afterwards.