Can't reach temp table with using prepared statement for second time

Question

I am using temporary tables inside my code in order to [some long sequnce of reasons here] in SQL Server, Java. I was executing my sql queries with using Stament object in java. However, recently I decided to use PreparedStatement in order to avoid injection thing.

My problem is when create a temporary table with using PreparedStatement, I can not reach it with the same prepared statement again. Here is a simple illustration:

sql = "select * into #someTable from (select someColumns from someOtherTable where smth = ? and smth2 = ?)"
PreparedStatement preparedStatement = conn.prepareStatement(sql);
for(int i=0; i<parameters.size(); i++){
   preparedStatement.setString(i+1, parameters.get(i).toString());
}

this.rs = preparedStatement.executeQuery();

Until here, it is ok. After getting ResultSet and doing something with it, or without getting a resultSet just for preparedStatement.execute() does not makes difference, I can not reach the #someTable object again.

sql = "select count(*) from #someTable"
preparedStatement = conn.prepareStatement(sql);
this.rs = preparedStatement.executeQuery();

Here this.rs = preparedStatement.executeQuery(); part gives 'Invalid object name #someTable'. I am doing all of the things above with using one Connection object only and without closing or reopening it. I need to use that temp table again. Is there any way to create temp table with PreparedStatement object in java and reuse this temp table again and again? Regards,

score 2 · Answer 1 · answered Apr 15 '20 at 08:17

Rather late to the party, but facing the same problem and finding the above answer wrong:

Read this article about the problem: https://learn.microsoft.com/en-us/sql/connect/jdbc/using-usefmtonly?view=sql-server-2017

I found that using a PreparedStatement to create the temp table wouldn't work, but if I changed to use a Statement to create the temp table it would work (even without the useFmtOnly). So start with this (from the MS article) and build on it:

final String sql = "INSERT INTO #Bar VALUES (?)";
try (Connection c = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
    try (Statement s = c.createStatement()) {
        s.execute("CREATE TABLE #Bar(c1 int)");
    }
    try (PreparedStatement p1 = c.prepareStatement(sql); PreparedStatement p2 = c.prepareStatement(sql)) {
        ((SQLServerPreparedStatement) p1).setUseFmtOnly(true);
        ParameterMetaData pmd1 = p1.getParameterMetaData();
        System.out.println(pmd1.getParameterTypeName(1)); // prints int
        ParameterMetaData pmd2 = p2.getParameterMetaData(); // throws exception, Invalid object name '#Bar'
    }
}

Tanner · Answer 2 · 2018-12-13T11:10:10.913

0

The temp table you create in the first statement exists for the scope\lifetime of that request. As soon as you call another query, you're in a different scope so it is no longer present as it would have been cleaned up.

Solutions are either make 2 requests in the same call (not great) or create a global temp table that can be accessed by the second query (still not great).

The better solution is to create a stored procedure that does everything you need, with the temp table creation, querying and tidy up encapsulated in the procedure.

PS I can't see any surrounding code, but beware of SQL Injection when building queries in code like this.

Related info: Scope of temporary tables in SQL Server

edited Dec 13 '18 at 11:10

answered Dec 13 '18 at 11:04

Tanner

22,205
9
65
83

Thanks for your answer. I am doing all of these stuffs with using 1 connection only and without closing and reopening it. – JollyRoger Dec 13 '18 at 11:07
@JollyRoger but you're making more than one call? first to create the table and second to return some data, so they have different scopes. – Tanner Dec 13 '18 at 11:11
Can you explain what you mean by the call. These 2 parts of the code actually stands together in a method. – JollyRoger Dec 13 '18 at 11:13
You call in to SQL Server to build the temp table on one line of code, you then try to query that temp table on a seperate line of code. So the code can be in the same method, but you're having 2 seperate conversations with SQL Server and the temp table doesn't exist in the second conversation. – Tanner Dec 13 '18 at 11:16
Yes that is correct, but for example if I use Statement instead of PreparedStatement, I can access more than once to the #tempTable. Does Statement object do not create another call? And I need to take some columns from #tempTable and also the count of the #tempTable. Is there any way to do these together without creating #tempTable twice? – JollyRoger Dec 13 '18 at 11:19

Can't reach temp table with using prepared statement for second time

2 Answers2