Problem with TEMP Tables on MS SQL Server

From: Evgeny Ryabitskiy (evgeny.ryabitski..mail.com)
Date: Wed Jan 27 2010 - 13:30:45 EST

  • Next message: Lachlan Deck: "Re: EJBQL client-side challenge"

    Hello 2 everyone.

    Today I found some not nice thing....

    In this Code:

    DataContext CTX = DataContext.createDataContext();

    //begin some transaction
    Transaction transact = Transaction.internalTransaction(null);
    Transaction.bindThreadTransaction(transact);
    Transaction.getThreadTransaction().begin();

    //create temp table
    CTX.performQuery("CreateTempTable", true);

    //delete from that temp table
    CTX.performQuery("DeleteFromTempTable", true);
                    
    Transaction.getThreadTransaction().commit();

    Result:

    INFO QueryLogger: Created connection pool: jdbc:jtds:sqlserver://******
            Driver class: net.sourceforge.jtds.jdbc.Driver
            Min. connections in the pool: 2
            Max. connections in the pool: 10
    INFO QueryLogger: --- transaction started.
    INFO QueryLogger: --- will run 1 query.
    INFO QueryLogger: Opening connection: jdbc:jtds:sqlserver://**********
            Login: diasoft
            Password: *******
    INFO QueryLogger: +++ Connecting: SUCCESS.
    INFO QueryLogger: Detected and installed adapter:
    org.apache.cayenne.dba.sqlserver.SQLServerAdapter
    INFO QueryLogger: CREATE TABLE #MyTempTable (SomeID int)
    INFO QueryLogger: --- will run 1 query.
    INFO QueryLogger: DELETE FROM #MyTempTable
    INFO QueryLogger: *** error.
    java.sql.SQLException: Invalid object name '#MyTempTable'.
            at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
            at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
            at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
            at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
            at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
            at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
            at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:560)
            at org.apache.cayenne.access.jdbc.SQLTemplateAction.execute(SQLTemplateAction.java:135)
            at org.apache.cayenne.access.jdbc.SQLTemplateAction.performAction(SQLTemplateAction.java:107)
            at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:59)
            at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:273)
            at org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:301)
            at org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:60)
            at org.apache.cayenne.access.DataDomainQueryAction$1.transform(DataDomainQueryAction.java:273)
            at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:826)
            at org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:270)
            at org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:110)
            at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:746)
            at org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:217)
            at org.apache.cayenne.access.DataContextQueryAction.execute(DataContextQueryAction.java:54)
            at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1395)
            at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1384)
            at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1578)
            at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1559)
            at ru.diasoft.cayenne.TestTempTable.main(TestTempTable.java:27)
    Exception in thread "main" org.apache.cayenne.CayenneRuntimeException:
    [v.2.0.4 October 12 2007] Query exception.
            at org.apache.cayenne.access.DataDomainQueryAction.nextQueryException(DataDomainQueryAction.java:428)
            at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:280)
            at org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:301)
            at org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:60)
            at org.apache.cayenne.access.DataDomainQueryAction$1.transform(DataDomainQueryAction.java:273)
            at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:826)
            at org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:270)
            at org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:110)
            at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:746)
            at org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:217)
            at org.apache.cayenne.access.DataContextQueryAction.execute(DataContextQueryAction.java:54)
            at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1395)
            at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1384)
            at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1578)
            at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1559)
            at ru.diasoft.cayenne.TestTempTable.main(TestTempTable.java:27)
    Caused by: java.sql.SQLException: Invalid object name '#MyTempTable'.
            at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
            at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
            at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
            at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
            at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
            at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
            at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:560)
            at org.apache.cayenne.access.jdbc.SQLTemplateAction.execute(SQLTemplateAction.java:135)
            at org.apache.cayenne.access.jdbc.SQLTemplateAction.performAction(SQLTemplateAction.java:107)
            at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:59)
            at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:273)
            ... 14 more

    Any Ideas how to handle it?



    This archive was generated by hypermail 2.0.0 : Wed Jan 27 2010 - 13:31:24 EST