Why do PreparedStatements fail with temp tables?

[Joseph Weinstein]

Hi all. Can MS explain why this code causes the exception it does?

Statement s = c.createStatement();
s.executeUpdate("create table #foo(bar int)");
s.executeUpdate("insert #foo values(1)");
ResultSet r = s.executeQuery("select * from #foo");
while (r.next()) System.out.println( r.getInt(1) );

// A plain statement works fine so far... we get what we expect...

PreparedStatement p = c.prepareStatement("select * from #foo where bar = ?");
p.setInt(1,1);

// Fails here, on the executeQuery()...
r = p.executeQuery();

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid object name
'#foo'.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source
)
at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.executeQuery(Unknown Source)

I also altered the code to retrieve the real name of the tempdb object,
#foo__________________________...00000000053F, which I used to
create the prepared statement query, and it failed in the same way....

thanks,
Joe Weinstein at B.E.A.

[Jim Moring]

I suspect that the driver is using a seperate connection for each statement
and temp tables are scoped to the connection. This is true even if you use
the same Connection object. Try "SelectMethod=cursor" on the connection in
question. That might fix the problem.

I hope this helps

Jim

"Joseph Weinstein" <j...@bea.com> wrote in message
news:3CF40A9D...@bea.com...

[David Murphy]

Nice call. The driver does appear to use >1 connection if there are >1
active statements open on the connection. Your explanation exactly matches
the issue and the code presented.

David Murphy
NetDirect
www.j-netdirect.com

"Jim Moring" <jmor...@hotmail.com> wrote in message
news:#g7t0MzBCHA.1432@tkmsftngp04...