none
JDBC and Temp Tables

    Question

  • I'm having trouble creating and using temp tables over JDBC. 


    Actually, it's not so much creating as referring to a created table.  What appears to be happening is that the table is created and immediately dropped, so a subsequent statement cannot refer to the newly created table.


    I've already done a lot of research and experimentation and found the selectMethod=cursor connection property.  Setting this property seems to have no effect.


    Example queries:


    1) select some.columns into #diffComp from some.table where .....
    2) select * from #diffComp join foo on ......


    The second query depends on the temp table #diffComp created by the first query.  When I trace this in Eclipse, the value returned by getUpdateCount() on the first query is the correct number of rows.  The second query is executed immediately aferwards, and ALWAYS throws :

    com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name '#diffComp'.


    I'm also able to look inside the connection objet in Eclipse, and can confirm that the activeConnectionProperties member DOES contain selectMethod=cursor.

    Any suggestions?
    Thursday, July 19, 2007 7:31 PM

All replies

  • I upgraded my SQL driver to 1.2CTP but still get the same problem.
    Thursday, July 19, 2007 8:12 PM
  • Here's a completely self-contained example.  The second call to execQuery() throws t SQLException

    /**
     *
     */
    package net.jhmg.test;

    import java.sql.*;
    import java.util.Properties;

    /**
     * @author jgarrison
     *
     */
    public class DBTest
    {
        public DBTest()
            throws SQLException, ClassNotFoundException
        {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:1433";
            Properties dbProps = new Properties();
            dbProps.put("databaseName",     "jgarrison");
            dbProps.put("user",             "jgarrison");
            dbProps.put("password",         "jgarrison");
            dbProps.put("selectMethod",     "cursor");
            dbProps.put("lastUpdateCount",  "true");
            
            Connection dbCon = DriverManager.getConnection(connectionUrl, dbProps);

            execQuery(dbCon, "select uuid,name from Component");
            
            execStmt(dbCon,"select uuid,name into #tempTable from Component");

            execQuery(dbCon, "select uuid,name from #tempTable");
        }
        
        public void execStmt(Connection dbCon, String sql)
        throws SQLException
        {
            System.out.println("SQL=" + sql);
            PreparedStatement ps = dbCon.prepareStatement(sql);
            ps.execute();
            int uc = ps.getUpdateCount();
            ps.close();
            System.out.println("UpdateCount=" + uc);
        }

        public void execQuery(Connection dbCon, String sql)
            throws SQLException
        {
            System.out.println("SQL=" + sql);
            PreparedStatement ps = dbCon.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                System.out.println(rs.getString(1) + " " + rs.getString(2));
            }
            rs.close();
            ps.close();
        }
        
        public static void main(String[] args)
            throws SQLException, ClassNotFoundException
        {
            new DBTest();
        }
    }



    Thursday, July 19, 2007 9:00 PM
  • Hi,

     

    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.

     

    HTH,

    Jimmy

    Friday, July 20, 2007 9:05 PM
    Moderator
  • Can you explain why it works this way? 

    And, also why this HUGE restriction isn't documented somewhere?
    Friday, July 20, 2007 9:09 PM
  • Thank-you for the feedback about the JDBC documentation.  We will look into the matter to see how we can best resolve the issue in future documentation releases.

     

    Jimmy

     

    Thursday, July 26, 2007 1:03 AM
    Moderator