You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Lance J. Andersen" <La...@Sun.COM> on 2007/02/23 16:41:42 UTC

Global Temp tables

Does anyone have an idea as to why the gobal table cannot be found.

Here is the trace output.

Regards
lance

[TopLink Fine]: 
ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DECLARE 
GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT 
NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255), 
STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, ADDR_ID INTEGER, 
MANAGER_EMP_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER, 
PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED
[TopLink Fine]: 
ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DECLARE 
GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY (EMP_ID INTEGER NOT NULL, 
SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED
[TopLink Fine]: 
ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--INSERT 
INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION) SELECT 
t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0, 
CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND 
(t1.EMP_ID = t0.EMP_ID))
[TopLink Fine]: 
ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DELETE 
FROM session.TL_CMP3_EMPLOYEE
[TopLink Fine]: 
ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DELETE 
FROM session.TL_CMP3_SALARY
[TopLink Warning]: 
UnitOfWork(31852201)--Thread(Thread[AWT-EventQueue-0,6,main])--Local 
Exception Stack:
Exception [TOPLINK-4002] (Oracle ${_EssentialsProductName} - 
${_EssentialsProductVersion} (Build 070220Dev)): 
oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: org.apache.derby.client.am.SqlException: Table 
'SESSION.TL_CMP3_EMPLOYEE' does not exist.Error Code: -1
Call:INSERT INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION) 
SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0, 
CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND 
(t1.EMP_ID = t0.EMP_ID))
Query:UpdateAllQuery()

Re: Global Temp tables

Posted by "Lance J. Andersen" <La...@Sun.COM>.
Thanks Mamta,  I noticed that as well in the docs after i figured out 
that PRIMARY KEY() was the culprit.  Too bad this cannot be done on a 
temp table.

The code is being generated by a JPA implementation which is why it took 
a while to figure out the cause.

Thanks for your time.  Perhaps we can look to loosening some of the 
restrictions in the future.

Regards
Lance

Mamta Satoor wrote:
> Lance, I agree that the error message is not helpful in figuring out 
> what is wrong.
>  
> I am just copying part of the information from the doc about some 
> restrictions on global tables.
>
> Derby does not support the following on temporary tables:
>
>     * index support
>     * synonyms, triggers and views on SESSION schema tables (including
>       physical tables and temporary tables)
>     * LOCK TABLE
>     * constraints and primary keys
>     * generated-column-spec
>     * importing into temporary tables
>
> Mamta
>
>
>
>  
> On 2/23/07, *Lance J. Andersen* <Lance.Andersen@sun.com 
> <ma...@sun.com>> wrote:
>
>     I think part of the problem is the application is using PRIMARY
>     KEY (EMP_ID) in the creation of the table:
>
>     : DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER, 
>     *PRIMARY KEY (EMP_ID)*) ON COMMIT DELETE ROWS NOT LOGGED
>
>
>     The error message is not that helpful :-(
>
>     Error code -1, SQL state 42995: The requested function does not
>     apply to global temporary tables.
>     Line 1, column 2
>
>
>     Lance J. Andersen wrote:
>>     Mamta,
>>
>>     Thanks for taking the time to respond.
>>
>>
>>     I had the developer run this using the embedded driver and
>>     attached the log.  It looks like the prepare is failing on the
>>     DECLARE.
>>
>>
>>     I have attached the log for your reference.
>>
>>     Regards
>>     Lance
>>
>>     Mamta Satoor wrote:
>>>     Lance, I am sure you have already checked following but wanted
>>>     to throw them out anyways
>>>     1)Is the temporary table getting referenced by the same
>>>     connection that created it?
>>>     2)Does your particular scenario work under embedded Derby?
>>>      
>>>     Also, once the connection that created the global table closes,
>>>     the global table cease to exist.
>>>      
>>>     I think it will be worth checking the script under embedded
>>>     Derby to rule out Network Server as the culprit.
>>>      
>>>     Mamta
>>>
>>>      
>>>     On 2/23/07, *Lance J. Andersen* <Lance.Andersen@sun.com
>>>     <ma...@sun.com>> wrote:
>>>
>>>         Does anyone have an idea as to why the gobal table cannot be
>>>         found.
>>>
>>>         Here is the trace output.
>>>
>>>         Regards
>>>         lance
>>>
>>>         [TopLink Fine]:
>>>         ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DECLARE
>>>         GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE (EMP_ID
>>>         INTEGER NOT
>>>         NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME
>>>         VARCHAR(255),
>>>         STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER,
>>>         ADDR_ID INTEGER,
>>>         MANAGER_EMP_ID INTEGER, START_DATE DATE, END_DATE DATE,
>>>         DEPT_ID INTEGER,
>>>         PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED
>>>         [TopLink Fine]:
>>>         ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DECLARE
>>>         GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY (EMP_ID
>>>         INTEGER NOT NULL,
>>>         SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS
>>>         NOT LOGGED
>>>         [TopLink Fine]:
>>>         ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--INSERT
>>>         INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION) SELECT
>>>         t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0,
>>>         CMP3_SALARY t1 WHERE ((t0.F_NAME =
>>>         'testUpdateUsingTempStorage') AND
>>>         (t1.EMP_ID = t0.EMP_ID))
>>>         [TopLink Fine]:
>>>         ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DELETE
>>>
>>>         FROM session.TL_CMP3_EMPLOYEE
>>>         [TopLink Fine]:
>>>         ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DELETE
>>>
>>>         FROM session.TL_CMP3_SALARY
>>>         [TopLink Warning]:
>>>         UnitOfWork(31852201)--Thread(Thread[AWT-EventQueue-0,6,main])--Local
>>>
>>>         Exception Stack:
>>>         Exception [TOPLINK-4002] (Oracle ${_EssentialsProductName} -
>>>         ${_EssentialsProductVersion} (Build 070220Dev)):
>>>         oracle.toplink.essentials.exceptions.DatabaseException
>>>         Internal Exception: org.apache.derby.client.am.SqlException
>>>         : Table
>>>         'SESSION.TL_CMP3_EMPLOYEE' does not exist.Error Code: -1
>>>         Call:INSERT INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM,
>>>         VERSION)
>>>         SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM
>>>         CMP3_EMPLOYEE t0,
>>>         CMP3_SALARY t1 WHERE ((t0.F_NAME =
>>>         'testUpdateUsingTempStorage') AND
>>>         (t1.EMP_ID = t0.EMP_ID))
>>>         Query:UpdateAllQuery()
>>>
>>>
>>     ------------------------------------------------------------------------
>>
>>     2007-02-23 21:18:01.266 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_EMP_PROJ WHERE EXISTS(SELECT 
>>     t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) AND t0.EMP_ID = CMP3_EMP_PROJ.EMPLOYEES_EMP_ID) :End prepared statement
>>     2007-02-23 21:18:01.266 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_SALARY WHERE EXISTS(SELECT 
>>     t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) AND t1.EMP_ID = CMP3_SALARY.EMP_ID) :End prepared statement
>>     2007-02-23 21:18:01.266 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_EMPLOYEE WHERE NOT EXISTS(SELECT 
>>     t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE (t1.EMP_ID = t0.EMP_ID) AND t0.EMP_ID = CMP3_EMPLOYEE.EMP_ID) :End prepared statement
>>     2007-02-23 21:18:01.282 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_ADDRESS WHERE (COUNTRY = 'testUpdateUsingTempStorage') :End prepared statement
>>     2007-02-23 21:18:01.297 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: UPDATE CMP3_EMPLOYEE_SEQ SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ? :End prepared statement with 2 parameters begin parameter #1: 50 :end parameter begin parameter #2: EMPLOYEE_SEQ :end parameter 
>>     2007-02-23 21:18:01.297 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: SELECT SEQ_COUNT FROM CMP3_EMPLOYEE_SEQ WHERE SEQ_NAME = ? :End prepared statement with 1 parameters begin parameter #1: EMPLOYEE_SEQ :end parameter 
>>     2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE, STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with 5 parameters begin parameter #1: 2 :end parameter begin parameter #2:  :end parameter begin parameter #3:  :end parameter begin parameter #4:  :end parameter begin parameter #5: testUpdateUsingTempStorage :end parameter 
>>     2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared statement
>>     2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE, STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with 5 parameters begin parameter #1: 1 :end parameter begin parameter #2:  :end parameter begin parameter #3:  :end parameter begin parameter #4:  :end parameter begin parameter #5: testUpdateUsingTempStorage :end parameter 
>>     2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared statement
>>     2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE, STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with 5 parameters begin parameter #1: 3 :end parameter begin parameter #2:  :end parameter begin parameter #3:  :end parameter begin parameter #4:  :end parameter begin parameter #5: testUpdateUsingTempStorage :end parameter 
>>     2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared statement
>>     2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement
>>     2007-02-23 21:18:01.329 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), End compiling prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement
>>     2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement with 12 parameters begin parameter #1: 401 :end parameter begin parameter #2: null :end parameter begin parameter #3: 1 :end parameter begin parameter #4: testUpdateUsingTempStorage :end parameter begin parameter #5: null :end parameter begin parameter #6: 1 :end parameter begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter begin parameter #9: 17 :end parameter begin parameter #10: null :end parameter begin parameter #11: null :end parameter begin parameter #12: null :end parameter 
>>     2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID) VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1: 100 :end parameter begin parameter #2: 401 :end parameter 
>>     2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement with 12 parameters begin parameter #1: 403 :end parameter begin parameter #2: null :end parameter begin parameter #3: 3 :end parameter begin parameter #4: testUpdateUsingTempStorage :end parameter begin parameter #5: null :end parameter begin parameter #6: 3 :end parameter begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter begin parameter #9: 18 :end parameter begin parameter #10: null :end parameter begin parameter #11: null :end parameter begin parameter #12: null :end parameter 
>>     2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID) VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1: 300 :end parameter begin parameter #2: 403 :end parameter 
>>     2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement with 12 parameters begin parameter #1: 402 :end parameter begin parameter #2: null :end parameter begin parameter #3: 2 :end parameter begin parameter #4: testUpdateUsingTempStorage :end parameter begin parameter #5: null :end parameter begin parameter #6: 2 :end parameter begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter begin parameter #9: 16 :end parameter begin parameter #10: null :end parameter begin parameter #11: null :end parameter begin parameter #12: null :end parameter 
>>     2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID) VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1: 200 :end parameter begin parameter #2: 402 :end parameter 
>>     2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
>>     2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
>>     2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2357), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
>>     2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE
>>      (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
>>     2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE
>>      (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
>>     2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY
>>      (EMP_ID INTEGER NOT NULL, SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
>>     2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY
>>      (EMP_ID INTEGER NOT NULL, SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
>>     2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: INSERT INTO session.TL_CMP3_EMPLOYEE
>>      (EMP_ID, ROOM_NUM, VERSION) SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) :End prepared statement
>>     2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: INSERT INTO session.TL_CMP3_EMPLOYEE
>>      (EMP_ID, ROOM_NUM, VERSION) SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) :End prepared statement
>>     2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DELETE FROM session.TL_CMP3_EMPLOYEE
>>      :End prepared statement
>>     2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DELETE FROM session.TL_CMP3_EMPLOYEE
>>      :End prepared statement
>>     2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DELETE FROM session.TL_CMP3_SALARY
>>      :End prepared statement
>>     2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DELETE FROM session.TL_CMP3_SALARY
>>      :End prepared statement
>>     2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Rolling back
>>     2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
>>     2007-02-23 21:19:36.516 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2358), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
>>     2007-02-23 21:19:36.516 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_EMP_PROJ WHERE EXISTS(SELECT 
>>     t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) AND t0.EMP_ID = CMP3_EMP_PROJ.EMPLOYEES_EMP_ID) :End prepared statement
>>     2007-02-23 21:19:36.516 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_SALARY WHERE EXISTS(SELECT 
>>     t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) AND t1.EMP_ID = CMP3_SALARY.EMP_ID) :End prepared statement
>>     2007-02-23 21:19:36.532 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_EMPLOYEE WHERE NOT EXISTS(SELECT 
>>     t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE (t1.EMP_ID = t0.EMP_ID) AND t0.EMP_ID = CMP3_EMPLOYEE.EMP_ID) :End prepared statement
>>     2007-02-23 21:19:36.547 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_ADDRESS WHERE (COUNTRY = 'testUpdateUsingTempStorage') :End prepared statement
>>     2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE, STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with 5 parameters begin parameter #1: 3 :end parameter begin parameter #2:  :end parameter begin parameter #3:  :end parameter begin parameter #4:  :end parameter begin parameter #5: testUpdateUsingTempStorage :end parameter 
>>     2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared statement
>>     2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE, STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with 5 parameters begin parameter #1: 2 :end parameter begin parameter #2:  :end parameter begin parameter #3:  :end parameter begin parameter #4:  :end parameter begin parameter #5: testUpdateUsingTempStorage :end parameter 
>>     2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared statement
>>     2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE, STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with 5 parameters begin parameter #1: 1 :end parameter begin parameter #2:  :end parameter begin parameter #3:  :end parameter begin parameter #4:  :end parameter begin parameter #5: testUpdateUsingTempStorage :end parameter 
>>     2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared statement
>>     2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement with 12 parameters begin parameter #1: 406 :end parameter begin parameter #2: null :end parameter begin parameter #3: 3 :end parameter begin parameter #4: testUpdateUsingTempStorage :end parameter begin parameter #5: null :end parameter begin parameter #6: 3 :end parameter begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter begin parameter #9: 19 :end parameter begin parameter #10: null :end parameter begin parameter #11: null :end parameter begin parameter #12: null :end parameter 
>>     2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID) VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1: 300 :end parameter begin parameter #2: 406 :end parameter 
>>     2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement with 12 parameters begin parameter #1: 405 :end parameter begin parameter #2: null :end parameter begin parameter #3: 2 :end parameter begin parameter #4: testUpdateUsingTempStorage :end parameter begin parameter #5: null :end parameter begin parameter #6: 2 :end parameter begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter begin parameter #9: 20 :end parameter begin parameter #10: null :end parameter begin parameter #11: null :end parameter begin parameter #12: null :end parameter 
>>     2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID) VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1: 200 :end parameter begin parameter #2: 405 :end parameter 
>>     2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement with 12 parameters begin parameter #1: 404 :end parameter begin parameter #2: null :end parameter begin parameter #3: 1 :end parameter begin parameter #4: testUpdateUsingTempStorage :end parameter begin parameter #5: null :end parameter begin parameter #6: 1 :end parameter begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter begin parameter #9: 21 :end parameter begin parameter #10: null :end parameter begin parameter #11: null :end parameter begin parameter #12: null :end parameter 
>>     2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID) VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1: 100 :end parameter begin parameter #2: 404 :end parameter 
>>     2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
>>     2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
>>     2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2359), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
>>     2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE
>>      (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
>>     2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE
>>      (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
>>     2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY
>>      (EMP_ID INTEGER NOT NULL, SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
>>     2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY
>>      (EMP_ID INTEGER NOT NULL, SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
>>     2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: INSERT INTO session.TL_CMP3_EMPLOYEE
>>      (EMP_ID, ROOM_NUM, VERSION) SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) :End prepared statement
>>     2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: INSERT INTO session.TL_CMP3_EMPLOYEE
>>      (EMP_ID, ROOM_NUM, VERSION) SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) :End prepared statement
>>     2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DELETE FROM session.TL_CMP3_EMPLOYEE
>>      :End prepared statement
>>     2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DELETE FROM session.TL_CMP3_EMPLOYEE
>>      :End prepared statement
>>     2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DELETE FROM session.TL_CMP3_SALARY
>>      :End prepared statement
>>     2007-02-23 21:19:36.610 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DELETE FROM session.TL_CMP3_SALARY
>>      :End prepared statement
>>     2007-02-23 21:19:36.610 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Rolling back
>>     2007-02-23 21:19:36.610 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
>>       
>
>

Re: Global Temp tables

Posted by Mamta Satoor <ms...@gmail.com>.
Lance, I agree that the error message is not helpful in figuring out what is
wrong.

I am just copying part of the information from the doc about some
restrictions on global tables.

Derby does not support the following on temporary tables:

   - index support
   - synonyms, triggers and views on SESSION schema tables (including
   physical tables and temporary tables)
   - LOCK TABLE
   - constraints and primary keys
   - generated-column-spec
   - importing into temporary tables

Mamta



On 2/23/07, Lance J. Andersen <La...@sun.com> wrote:
>
> I think part of the problem is the application is using PRIMARY KEY
> (EMP_ID) in the creation of the table:
>
> : DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER, *PRIMARY KEY (EMP_ID)*) ON COMMIT DELETE ROWS NOT LOGGED
>
>
> The error message is not that helpful :-(
>
> Error code -1, SQL state 42995: The requested function does not apply to
> global temporary tables.
> Line 1, column 2
>
>
> Lance J. Andersen wrote:
>
> Mamta,
>
> Thanks for taking the time to respond.
>
>
> I had the developer run this using the embedded driver and attached the
> log.  It looks like the prepare is failing on the DECLARE.
>
>
> I have attached the log for your reference.
>
> Regards
> Lance
>
> Mamta Satoor wrote:
>
> Lance, I am sure you have already checked following but wanted to throw
> them out anyways
> 1)Is the temporary table getting referenced by the same connection that
> created it?
> 2)Does your particular scenario work under embedded Derby?
>
>  Also, once the connection that created the global table closes, the
> global table cease to exist.
>
> I think it will be worth checking the script under embedded Derby to rule
> out Network Server as the culprit.
>
> Mamta
>
>
> On 2/23/07, Lance J. Andersen <La...@sun.com> wrote:
> >
> > Does anyone have an idea as to why the gobal table cannot be found.
> >
> > Here is the trace output.
> >
> > Regards
> > lance
> >
> > [TopLink Fine]:
> >
> > ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DECLARE
> > GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT
> > NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255),
> > STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, ADDR_ID INTEGER,
> > MANAGER_EMP_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER,
> > PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED
> > [TopLink Fine]:
> >
> > ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DECLARE
> > GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY (EMP_ID INTEGER NOT NULL,
> > SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED
> > [TopLink Fine]:
> >
> > ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--INSERT
> > INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION) SELECT
> > t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0,
> > CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND
> > (t1.EMP_ID = t0.EMP_ID))
> > [TopLink Fine]:
> > ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DELETE
> >
> > FROM session.TL_CMP3_EMPLOYEE
> > [TopLink Fine]:
> >
> > ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DELETE
> > FROM session.TL_CMP3_SALARY
> > [TopLink Warning]:
> > UnitOfWork(31852201)--Thread(Thread[AWT-EventQueue-0,6,main])--Local
> > Exception Stack:
> > Exception [TOPLINK-4002] (Oracle ${_EssentialsProductName} -
> > ${_EssentialsProductVersion} (Build 070220Dev)):
> > oracle.toplink.essentials.exceptions.DatabaseException
> > Internal Exception: org.apache.derby.client.am.SqlException : Table
> > 'SESSION.TL_CMP3_EMPLOYEE' does not exist.Error Code: -1
> > Call:INSERT INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION)
> > SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0,
> > CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND
> > (t1.EMP_ID = t0.EMP_ID))
> > Query:UpdateAllQuery()
> >
>
> ------------------------------
>
> 2007-02-23 21:18:01.266 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_EMP_PROJ WHERE EXISTS(SELECT t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) AND t0.EMP_ID = CMP3_EMP_PROJ.EMPLOYEES_EMP_ID) :End prepared statement
> 2007-02-23 21:18:01.266 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_SALARY WHERE EXISTS(SELECT t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) AND t1.EMP_ID = CMP3_SALARY.EMP_ID) :End prepared statement
> 2007-02-23 21:18:01.266 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_EMPLOYEE WHERE NOT EXISTS(SELECT t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE (t1.EMP_ID = t0.EMP_ID) AND t0.EMP_ID = CMP3_EMPLOYEE.EMP_ID) :End prepared statement
> 2007-02-23 21:18:01.282 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_ADDRESS WHERE (COUNTRY = 'testUpdateUsingTempStorage') :End prepared statement
> 2007-02-23 21:18:01.297 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: UPDATE CMP3_EMPLOYEE_SEQ SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ? :End prepared statement with 2 parameters begin parameter #1: 50 :end parameter begin parameter #2: EMPLOYEE_SEQ :end parameter
> 2007-02-23 21:18:01.297 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: SELECT SEQ_COUNT FROM CMP3_EMPLOYEE_SEQ WHERE SEQ_NAME = ? :End prepared statement with 1 parameters begin parameter #1: EMPLOYEE_SEQ :end parameter
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE, STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with 5 parameters begin parameter #1: 2 :end parameter begin parameter #2:  :end parameter begin parameter #3:  :end parameter begin parameter #4:  :end parameter begin parameter #5: testUpdateUsingTempStorage :end parameter
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared statement
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE, STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with 5 parameters begin parameter #1: 1 :end parameter begin parameter #2:  :end parameter begin parameter #3:  :end parameter begin parameter #4:  :end parameter begin parameter #5: testUpdateUsingTempStorage :end parameter
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared statement
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE, STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with 5 parameters begin parameter #1: 3 :end parameter begin parameter #2:  :end parameter begin parameter #3:  :end parameter begin parameter #4:  :end parameter begin parameter #5: testUpdateUsingTempStorage :end parameter
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared statement
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement
> 2007-02-23 21:18:01.329 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), End compiling prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement
> 2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement with 12 parameters begin parameter #1: 401 :end parameter begin parameter #2: null :end parameter begin parameter #3: 1 :end parameter begin parameter #4: testUpdateUsingTempStorage :end parameter begin parameter #5: null :end parameter begin parameter #6: 1 :end parameter begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter begin parameter #9: 17 :end parameter begin parameter #10: null :end parameter begin parameter #11: null :end parameter begin parameter #12: null :end parameter
> 2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID) VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1: 100 :end parameter begin parameter #2: 401 :end parameter
> 2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement with 12 parameters begin parameter #1: 403 :end parameter begin parameter #2: null :end parameter begin parameter #3: 3 :end parameter begin parameter #4: testUpdateUsingTempStorage :end parameter begin parameter #5: null :end parameter begin parameter #6: 3 :end parameter begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter begin parameter #9: 18 :end parameter begin parameter #10: null :end parameter begin parameter #11: null :end parameter begin parameter #12: null :end parameter
> 2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID) VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1: 300 :end parameter begin parameter #2: 403 :end parameter
> 2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement with 12 parameters begin parameter #1: 402 :end parameter begin parameter #2: null :end parameter begin parameter #3: 2 :end parameter begin parameter #4: testUpdateUsingTempStorage :end parameter begin parameter #5: null :end parameter begin parameter #6: 2 :end parameter begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter begin parameter #9: 16 :end parameter begin parameter #10: null :end parameter begin parameter #11: null :end parameter begin parameter #12: null :end parameter
> 2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID) VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1: 200 :end parameter begin parameter #2: 402 :end parameter
> 2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
> 2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
> 2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2357), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
> 2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY (EMP_ID INTEGER NOT NULL, SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY (EMP_ID INTEGER NOT NULL, SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: INSERT INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION) SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: INSERT INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION) SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DELETE FROM session.TL_CMP3_EMPLOYEE :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DELETE FROM session.TL_CMP3_EMPLOYEE :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DELETE FROM session.TL_CMP3_SALARY :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DELETE FROM session.TL_CMP3_SALARY :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Rolling back
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
> 2007-02-23 21:19:36.516 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2358), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
> 2007-02-23 21:19:36.516 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_EMP_PROJ WHERE EXISTS(SELECT t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) AND t0.EMP_ID = CMP3_EMP_PROJ.EMPLOYEES_EMP_ID) :End prepared statement
> 2007-02-23 21:19:36.516 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_SALARY WHERE EXISTS(SELECT t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) AND t1.EMP_ID = CMP3_SALARY.EMP_ID) :End prepared statement
> 2007-02-23 21:19:36.532 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_EMPLOYEE WHERE NOT EXISTS(SELECT t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE (t1.EMP_ID = t0.EMP_ID) AND t0.EMP_ID = CMP3_EMPLOYEE.EMP_ID) :End prepared statement
> 2007-02-23 21:19:36.547 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_ADDRESS WHERE (COUNTRY = 'testUpdateUsingTempStorage') :End prepared statement
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE, STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with 5 parameters begin parameter #1: 3 :end parameter begin parameter #2:  :end parameter begin parameter #3:  :end parameter begin parameter #4:  :end parameter begin parameter #5: testUpdateUsingTempStorage :end parameter
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared statement
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE, STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with 5 parameters begin parameter #1: 2 :end parameter begin parameter #2:  :end parameter begin parameter #3:  :end parameter begin parameter #4:  :end parameter begin parameter #5: testUpdateUsingTempStorage :end parameter
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared statement
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE, STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with 5 parameters begin parameter #1: 1 :end parameter begin parameter #2:  :end parameter begin parameter #3:  :end parameter begin parameter #4:  :end parameter begin parameter #5: testUpdateUsingTempStorage :end parameter
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared statement
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement with 12 parameters begin parameter #1: 406 :end parameter begin parameter #2: null :end parameter begin parameter #3: 3 :end parameter begin parameter #4: testUpdateUsingTempStorage :end parameter begin parameter #5: null :end parameter begin parameter #6: 3 :end parameter begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter begin parameter #9: 19 :end parameter begin parameter #10: null :end parameter begin parameter #11: null :end parameter begin parameter #12: null :end parameter
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID) VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1: 300 :end parameter begin parameter #2: 406 :end parameter
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement with 12 parameters begin parameter #1: 405 :end parameter begin parameter #2: null :end parameter begin parameter #3: 2 :end parameter begin parameter #4: testUpdateUsingTempStorage :end parameter begin parameter #5: null :end parameter begin parameter #6: 2 :end parameter begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter begin parameter #9: 20 :end parameter begin parameter #10: null :end parameter begin parameter #11: null :end parameter begin parameter #12: null :end parameter
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID) VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1: 200 :end parameter begin parameter #2: 405 :end parameter
> 2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement with 12 parameters begin parameter #1: 404 :end parameter begin parameter #2: null :end parameter begin parameter #3: 1 :end parameter begin parameter #4: testUpdateUsingTempStorage :end parameter begin parameter #5: null :end parameter begin parameter #6: 1 :end parameter begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter begin parameter #9: 21 :end parameter begin parameter #10: null :end parameter begin parameter #11: null :end parameter begin parameter #12: null :end parameter
> 2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID) VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1: 100 :end parameter begin parameter #2: 404 :end parameter
> 2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
> 2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
> 2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2359), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY (EMP_ID INTEGER NOT NULL, SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY (EMP_ID INTEGER NOT NULL, SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: INSERT INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION) SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: INSERT INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION) SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DELETE FROM session.TL_CMP3_EMPLOYEE :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DELETE FROM session.TL_CMP3_EMPLOYEE :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DELETE FROM session.TL_CMP3_SALARY :End prepared statement
> 2007-02-23 21:19:36.610 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DELETE FROM session.TL_CMP3_SALARY :End prepared statement
> 2007-02-23 21:19:36.610 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Rolling back
> 2007-02-23 21:19:36.610 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
>
>
>

Re: Global Temp tables

Posted by "Lance J. Andersen" <La...@Sun.COM>.
I think part of the problem is the application is using PRIMARY KEY 
(EMP_ID) in the creation of the table:

: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER, *PRIMARY KEY (EMP_ID)*) ON COMMIT DELETE ROWS NOT LOGGED


The error message is not that helpful :-(

Error code -1, SQL state 42995: The requested function does not apply to 
global temporary tables.
Line 1, column 2


Lance J. Andersen wrote:
> Mamta,
>
> Thanks for taking the time to respond.
>
>
> I had the developer run this using the embedded driver and attached 
> the log.  It looks like the prepare is failing on the DECLARE.
>
>
> I have attached the log for your reference.
>
> Regards
> Lance
>
> Mamta Satoor wrote:
>> Lance, I am sure you have already checked following but wanted to 
>> throw them out anyways
>> 1)Is the temporary table getting referenced by the same connection 
>> that created it?
>> 2)Does your particular scenario work under embedded Derby?
>>  
>> Also, once the connection that created the global table closes, the 
>> global table cease to exist.
>>  
>> I think it will be worth checking the script under embedded Derby to 
>> rule out Network Server as the culprit.
>>  
>> Mamta
>>
>>  
>> On 2/23/07, *Lance J. Andersen* <Lance.Andersen@sun.com 
>> <ma...@sun.com>> wrote:
>>
>>     Does anyone have an idea as to why the gobal table cannot be found.
>>
>>     Here is the trace output.
>>
>>     Regards
>>     lance
>>
>>     [TopLink Fine]:
>>     ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DECLARE
>>     GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT
>>     NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255),
>>     STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, ADDR_ID
>>     INTEGER,
>>     MANAGER_EMP_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID
>>     INTEGER,
>>     PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED
>>     [TopLink Fine]:
>>     ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DECLARE
>>     GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY (EMP_ID INTEGER NOT
>>     NULL,
>>     SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT
>>     LOGGED
>>     [TopLink Fine]:
>>     ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--INSERT
>>     INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION) SELECT
>>     t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0,
>>     CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND
>>     (t1.EMP_ID = t0.EMP_ID))
>>     [TopLink Fine]:
>>     ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DELETE
>>
>>     FROM session.TL_CMP3_EMPLOYEE
>>     [TopLink Fine]:
>>     ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DELETE
>>     FROM session.TL_CMP3_SALARY
>>     [TopLink Warning]:
>>     UnitOfWork(31852201)--Thread(Thread[AWT-EventQueue-0,6,main])--Local
>>     Exception Stack:
>>     Exception [TOPLINK-4002] (Oracle ${_EssentialsProductName} -
>>     ${_EssentialsProductVersion} (Build 070220Dev)):
>>     oracle.toplink.essentials.exceptions.DatabaseException
>>     Internal Exception: org.apache.derby.client.am.SqlException : Table
>>     'SESSION.TL_CMP3_EMPLOYEE' does not exist.Error Code: -1
>>     Call:INSERT INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION)
>>     SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0,
>>     CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND
>>     (t1.EMP_ID = t0.EMP_ID))
>>     Query:UpdateAllQuery()
>>
>>
> ------------------------------------------------------------------------
>
> 2007-02-23 21:18:01.266 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_EMP_PROJ WHERE EXISTS(SELECT t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) AND t0.EMP_ID = CMP3_EMP_PROJ.EMPLOYEES_EMP_ID) :End prepared statement
> 2007-02-23 21:18:01.266 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_SALARY WHERE EXISTS(SELECT t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) AND t1.EMP_ID = CMP3_SALARY.EMP_ID) :End prepared statement
> 2007-02-23 21:18:01.266 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_EMPLOYEE WHERE NOT EXISTS(SELECT t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE (t1.EMP_ID = t0.EMP_ID) AND t0.EMP_ID = CMP3_EMPLOYEE.EMP_ID) :End prepared statement
> 2007-02-23 21:18:01.282 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_ADDRESS WHERE (COUNTRY = 'testUpdateUsingTempStorage') :End prepared statement
> 2007-02-23 21:18:01.297 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: UPDATE CMP3_EMPLOYEE_SEQ SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ? :End prepared statement with 2 parameters begin parameter #1: 50 :end parameter begin parameter #2: EMPLOYEE_SEQ :end parameter 
> 2007-02-23 21:18:01.297 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: SELECT SEQ_COUNT FROM CMP3_EMPLOYEE_SEQ WHERE SEQ_NAME = ? :End prepared statement with 1 parameters begin parameter #1: EMPLOYEE_SEQ :end parameter 
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE, STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with 5 parameters begin parameter #1: 2 :end parameter begin parameter #2:  :end parameter begin parameter #3:  :end parameter begin parameter #4:  :end parameter begin parameter #5: testUpdateUsingTempStorage :end parameter 
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared statement
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE, STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with 5 parameters begin parameter #1: 1 :end parameter begin parameter #2:  :end parameter begin parameter #3:  :end parameter begin parameter #4:  :end parameter begin parameter #5: testUpdateUsingTempStorage :end parameter 
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared statement
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE, STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with 5 parameters begin parameter #1: 3 :end parameter begin parameter #2:  :end parameter begin parameter #3:  :end parameter begin parameter #4:  :end parameter begin parameter #5: testUpdateUsingTempStorage :end parameter 
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared statement
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement
> 2007-02-23 21:18:01.329 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), End compiling prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement
> 2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement with 12 parameters begin parameter #1: 401 :end parameter begin parameter #2: null :end parameter begin parameter #3: 1 :end parameter begin parameter #4: testUpdateUsingTempStorage :end parameter begin parameter #5: null :end parameter begin parameter #6: 1 :end parameter begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter begin parameter #9: 17 :end parameter begin parameter #10: null :end parameter begin parameter #11: null :end parameter begin parameter #12: null :end parameter 
> 2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID) VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1: 100 :end parameter begin parameter #2: 401 :end parameter 
> 2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement with 12 parameters begin parameter #1: 403 :end parameter begin parameter #2: null :end parameter begin parameter #3: 3 :end parameter begin parameter #4: testUpdateUsingTempStorage :end parameter begin parameter #5: null :end parameter begin parameter #6: 3 :end parameter begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter begin parameter #9: 18 :end parameter begin parameter #10: null :end parameter begin parameter #11: null :end parameter begin parameter #12: null :end parameter 
> 2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID) VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1: 300 :end parameter begin parameter #2: 403 :end parameter 
> 2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement with 12 parameters begin parameter #1: 402 :end parameter begin parameter #2: null :end parameter begin parameter #3: 2 :end parameter begin parameter #4: testUpdateUsingTempStorage :end parameter begin parameter #5: null :end parameter begin parameter #6: 2 :end parameter begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter begin parameter #9: 16 :end parameter begin parameter #10: null :end parameter begin parameter #11: null :end parameter begin parameter #12: null :end parameter 
> 2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID) VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1: 200 :end parameter begin parameter #2: 402 :end parameter 
> 2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
> 2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360), (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
> 2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2357), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
> 2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY (EMP_ID INTEGER NOT NULL, SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY (EMP_ID INTEGER NOT NULL, SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: INSERT INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION) SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: INSERT INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION) SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DELETE FROM session.TL_CMP3_EMPLOYEE :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DELETE FROM session.TL_CMP3_EMPLOYEE :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DELETE FROM session.TL_CMP3_SALARY :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DELETE FROM session.TL_CMP3_SALARY :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Rolling back
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365), (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
> 2007-02-23 21:19:36.516 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2358), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
> 2007-02-23 21:19:36.516 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_EMP_PROJ WHERE EXISTS(SELECT t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) AND t0.EMP_ID = CMP3_EMP_PROJ.EMPLOYEES_EMP_ID) :End prepared statement
> 2007-02-23 21:19:36.516 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_SALARY WHERE EXISTS(SELECT t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) AND t1.EMP_ID = CMP3_SALARY.EMP_ID) :End prepared statement
> 2007-02-23 21:19:36.532 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_EMPLOYEE WHERE NOT EXISTS(SELECT t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE (t1.EMP_ID = t0.EMP_ID) AND t0.EMP_ID = CMP3_EMPLOYEE.EMP_ID) :End prepared statement
> 2007-02-23 21:19:36.547 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: DELETE FROM CMP3_ADDRESS WHERE (COUNTRY = 'testUpdateUsingTempStorage') :End prepared statement
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE, STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with 5 parameters begin parameter #1: 3 :end parameter begin parameter #2:  :end parameter begin parameter #3:  :end parameter begin parameter #4:  :end parameter begin parameter #5: testUpdateUsingTempStorage :end parameter 
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared statement
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE, STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with 5 parameters begin parameter #1: 2 :end parameter begin parameter #2:  :end parameter begin parameter #3:  :end parameter begin parameter #4:  :end parameter begin parameter #5: testUpdateUsingTempStorage :end parameter 
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared statement
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE, STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with 5 parameters begin parameter #1: 1 :end parameter begin parameter #2:  :end parameter begin parameter #3:  :end parameter begin parameter #4:  :end parameter begin parameter #5: testUpdateUsingTempStorage :end parameter 
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared statement
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement with 12 parameters begin parameter #1: 406 :end parameter begin parameter #2: null :end parameter begin parameter #3: 3 :end parameter begin parameter #4: testUpdateUsingTempStorage :end parameter begin parameter #5: null :end parameter begin parameter #6: 3 :end parameter begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter begin parameter #9: 19 :end parameter begin parameter #10: null :end parameter begin parameter #11: null :end parameter begin parameter #12: null :end parameter 
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID) VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1: 300 :end parameter begin parameter #2: 406 :end parameter 
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement with 12 parameters begin parameter #1: 405 :end parameter begin parameter #2: null :end parameter begin parameter #3: 2 :end parameter begin parameter #4: testUpdateUsingTempStorage :end parameter begin parameter #5: null :end parameter begin parameter #6: 2 :end parameter begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter begin parameter #9: 20 :end parameter begin parameter #10: null :end parameter begin parameter #11: null :end parameter begin parameter #12: null :end parameter 
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID) VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1: 200 :end parameter begin parameter #2: 405 :end parameter 
> 2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) :End prepared statement with 12 parameters begin parameter #1: 404 :end parameter begin parameter #2: null :end parameter begin parameter #3: 1 :end parameter begin parameter #4: testUpdateUsingTempStorage :end parameter begin parameter #5: null :end parameter begin parameter #6: 1 :end parameter begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter begin parameter #9: 21 :end parameter begin parameter #10: null :end parameter begin parameter #11: null :end parameter begin parameter #12: null :end parameter 
> 2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID) VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1: 100 :end parameter begin parameter #2: 404 :end parameter 
> 2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
> 2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371), (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
> 2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2359), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY (EMP_ID INTEGER NOT NULL, SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY (EMP_ID INTEGER NOT NULL, SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: INSERT INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION) SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: INSERT INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION) SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DELETE FROM session.TL_CMP3_EMPLOYEE :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DELETE FROM session.TL_CMP3_EMPLOYEE :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Begin compiling prepared statement: DELETE FROM session.TL_CMP3_SALARY :End prepared statement
> 2007-02-23 21:19:36.610 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Error compiling prepared statement: DELETE FROM session.TL_CMP3_SALARY :End prepared statement
> 2007-02-23 21:19:36.610 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Rolling back
> 2007-02-23 21:19:36.610 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375), (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null), Committing
>

Re: Global Temp tables

Posted by Mamta Satoor <ms...@gmail.com>.
Lance, thanks for having the developer try it in embedded mode. It looks
like this is running as a global transaction. I will look at the log to see
if I can figure something out. But, is there any chance there is a small
reporducible test case that could be used by the community to see the
behavior?

Mamta


On 2/23/07, Lance J. Andersen <La...@sun.com> wrote:
>
> Mamta,
>
> Thanks for taking the time to respond.
>
>
> I had the developer run this using the embedded driver and attached the
> log.  It looks like the prepare is failing on the DECLARE.
>
>
> I have attached the log for your reference.
>
> Regards
> Lance
>
> Mamta Satoor wrote:
>
> Lance, I am sure you have already checked following but wanted to throw
> them out anyways
> 1)Is the temporary table getting referenced by the same connection that
> created it?
> 2)Does your particular scenario work under embedded Derby?
>
>  Also, once the connection that created the global table closes, the
> global table cease to exist.
>
> I think it will be worth checking the script under embedded Derby to rule
> out Network Server as the culprit.
>
> Mamta
>
>
> On 2/23/07, Lance J. Andersen <La...@sun.com> wrote:
> >
> > Does anyone have an idea as to why the gobal table cannot be found.
> >
> > Here is the trace output.
> >
> > Regards
> > lance
> >
> > [TopLink Fine]:
> >
> > ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DECLARE
> > GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT
> > NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255),
> > STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, ADDR_ID INTEGER,
> > MANAGER_EMP_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER,
> > PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED
> > [TopLink Fine]:
> >
> > ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DECLARE
> > GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY (EMP_ID INTEGER NOT NULL,
> > SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED
> > [TopLink Fine]:
> >
> > ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--INSERT
> > INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION) SELECT
> > t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0,
> > CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND
> > (t1.EMP_ID = t0.EMP_ID))
> > [TopLink Fine]:
> > ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DELETE
> >
> > FROM session.TL_CMP3_EMPLOYEE
> > [TopLink Fine]:
> >
> > ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DELETE
> > FROM session.TL_CMP3_SALARY
> > [TopLink Warning]:
> > UnitOfWork(31852201)--Thread(Thread[AWT-EventQueue-0,6,main])--Local
> > Exception Stack:
> > Exception [TOPLINK-4002] (Oracle ${_EssentialsProductName} -
> > ${_EssentialsProductVersion} (Build 070220Dev)):
> > oracle.toplink.essentials.exceptions.DatabaseException
> > Internal Exception: org.apache.derby.client.am.SqlException : Table
> > 'SESSION.TL_CMP3_EMPLOYEE' does not exist.Error Code: -1
> > Call:INSERT INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION)
> > SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0,
> > CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND
> > (t1.EMP_ID = t0.EMP_ID))
> > Query:UpdateAllQuery()
> >
>
>
> 2007-02-23 21:18:01.266 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: DELETE FROM CMP3_EMP_PROJ WHERE EXISTS(SELECT
> t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME =
> 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) AND t0.EMP_ID =
> CMP3_EMP_PROJ.EMPLOYEES_EMP_ID) :End prepared statement
> 2007-02-23 21:18:01.266 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: DELETE FROM CMP3_SALARY WHERE EXISTS(SELECT
> t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME =
> 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) AND t1.EMP_ID =
> CMP3_SALARY.EMP_ID) :End prepared statement
> 2007-02-23 21:18:01.266 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: DELETE FROM CMP3_EMPLOYEE WHERE NOT
> EXISTS(SELECT t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE (
> t1.EMP_ID = t0.EMP_ID) AND t0.EMP_ID = CMP3_EMPLOYEE.EMP_ID) :End prepared
> statement
> 2007-02-23 21:18:01.282 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: DELETE FROM CMP3_ADDRESS WHERE (COUNTRY =
> 'testUpdateUsingTempStorage') :End prepared statement
> 2007-02-23 21:18:01.297 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: UPDATE CMP3_EMPLOYEE_SEQ SET SEQ_COUNT =
> SEQ_COUNT + ? WHERE SEQ_NAME = ? :End prepared statement with 2 parameters
> begin parameter #1: 50 :end parameter begin parameter #2: EMPLOYEE_SEQ :end
> parameter
> 2007-02-23 21:18:01.297 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: SELECT SEQ_COUNT FROM CMP3_EMPLOYEE_SEQ WHERE
> SEQ_NAME = ? :End prepared statement with 1 parameters begin parameter #1:
> EMPLOYEE_SEQ :end parameter
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE,
> STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with
> 5 parameters begin parameter #1: 2 :end parameter begin parameter #2:  :end
> parameter begin parameter #3:  :end parameter begin parameter #4:  :end
> parameter begin parameter #5: testUpdateUsingTempStorage :end parameter
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared
> statement
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE,
> STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with
> 5 parameters begin parameter #1: 1 :end parameter begin parameter #2:  :end
> parameter begin parameter #3:  :end parameter begin parameter #4:  :end
> parameter begin parameter #5: testUpdateUsingTempStorage :end parameter
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared
> statement
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE,
> STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with
> 5 parameters begin parameter #1: 3 :end parameter begin parameter #2:  :end
> parameter begin parameter #3:  :end parameter begin parameter #4:  :end
> parameter begin parameter #5: testUpdateUsingTempStorage :end parameter
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared
> statement
> 2007-02-23 21:18:01.313 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Begin compiling prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID,
> PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID,
> ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?, ?) :End prepared statement
> 2007-02-23 21:18:01.329 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> End compiling prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID,
> PAY_SCALE, ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID,
> ADDR_ID, START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?, ?) :End prepared statement
> 2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE,
> ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID,
> START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
> :End prepared statement with 12 parameters begin parameter #1: 401 :end
> parameter begin parameter #2: null :end parameter begin parameter #3: 1 :end
> parameter begin parameter #4: testUpdateUsingTempStorage :end parameter
> begin parameter #5: null :end parameter begin parameter #6: 1 :end parameter
> begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter
> begin parameter #9: 17 :end parameter begin parameter #10: null :end
> parameter begin parameter #11: null :end parameter begin parameter #12: null
> :end parameter
> 2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID)
> VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1:
> 100 :end parameter begin parameter #2: 401 :end parameter
> 2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE,
> ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID,
> START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
> :End prepared statement with 12 parameters begin parameter #1: 403 :end
> parameter begin parameter #2: null :end parameter begin parameter #3: 3 :end
> parameter begin parameter #4: testUpdateUsingTempStorage :end parameter
> begin parameter #5: null :end parameter begin parameter #6: 3 :end parameter
> begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter
> begin parameter #9: 18 :end parameter begin parameter #10: null :end
> parameter begin parameter #11: null :end parameter begin parameter #12: null
> :end parameter
> 2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID)
> VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1:
> 300 :end parameter begin parameter #2: 403 :end parameter
> 2007-02-23 21:18:01.344 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE,
> ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID,
> START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
> :End prepared statement with 12 parameters begin parameter #1: 402 :end
> parameter begin parameter #2: null :end parameter begin parameter #3: 2 :end
> parameter begin parameter #4: testUpdateUsingTempStorage :end parameter
> begin parameter #5: null :end parameter begin parameter #6: 2 :end parameter
> begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter
> begin parameter #9: 16 :end parameter begin parameter #10: null :end
> parameter begin parameter #11: null :end parameter begin parameter #12: null
> :end parameter
> 2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID)
> VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1:
> 200 :end parameter begin parameter #2: 402 :end parameter
> 2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Committing
> 2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2360),
> (SESSIONID = 41), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Committing
> 2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2357),
> (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Committing
> 2007-02-23 21:18:01.360 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365),
> (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Begin compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE
> session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255),
> ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255),
> VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE,
> END_DATE DATE, DEPT_ID INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS
> NOT LOGGED :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365),
> (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Error compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE
> session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255),
> ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255),
> VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE,
> END_DATE DATE, DEPT_ID INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS
> NOT LOGGED :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365),
> (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Begin compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE
> session.TL_CMP3_SALARY (EMP_ID INTEGER NOT NULL, SALARY INTEGER, PRIMARY
> KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365),
> (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Error compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE
> session.TL_CMP3_SALARY (EMP_ID INTEGER NOT NULL, SALARY INTEGER, PRIMARY
> KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365),
> (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Begin compiling prepared statement: INSERT INTO session.TL_CMP3_EMPLOYEE(EMP_ID, ROOM_NUM, VERSION) SELECT
> t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0, CMP3_SALARY
> t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID =
> t0.EMP_ID)) :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365),
> (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Error compiling prepared statement: INSERT INTO session.TL_CMP3_EMPLOYEE(EMP_ID, ROOM_NUM, VERSION) SELECT
> t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0, CMP3_SALARY
> t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID =
> t0.EMP_ID)) :End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365),
> (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Begin compiling prepared statement: DELETE FROM session.TL_CMP3_EMPLOYEE:End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365),
> (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Error compiling prepared statement: DELETE FROM session.TL_CMP3_EMPLOYEE:End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365),
> (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Begin compiling prepared statement: DELETE FROM session.TL_CMP3_SALARY:End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365),
> (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Error compiling prepared statement: DELETE FROM session.TL_CMP3_SALARY:End prepared statement
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365),
> (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DATABASE =
> C:/Dev_ri/properties/Derby1), (DRDAID = null), Rolling back
> 2007-02-23 21:18:01.376 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2365),
> (SESSIONID = 42), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Committing
> 2007-02-23 21:19:36.516 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2358),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Committing
> 2007-02-23 21:19:36.516 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: DELETE FROM CMP3_EMP_PROJ WHERE EXISTS(SELECT
> t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME =
> 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) AND t0.EMP_ID =
> CMP3_EMP_PROJ.EMPLOYEES_EMP_ID) :End prepared statement
> 2007-02-23 21:19:36.516 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: DELETE FROM CMP3_SALARY WHERE EXISTS(SELECT
> t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME =
> 'testUpdateUsingTempStorage') AND (t1.EMP_ID = t0.EMP_ID)) AND t1.EMP_ID =
> CMP3_SALARY.EMP_ID) :End prepared statement
> 2007-02-23 21:19:36.532 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: DELETE FROM CMP3_EMPLOYEE WHERE NOT
> EXISTS(SELECT t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE (
> t1.EMP_ID = t0.EMP_ID) AND t0.EMP_ID = CMP3_EMPLOYEE.EMP_ID) :End prepared
> statement
> 2007-02-23 21:19:36.547 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: DELETE FROM CMP3_ADDRESS WHERE (COUNTRY =
> 'testUpdateUsingTempStorage') :End prepared statement
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE,
> STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with
> 5 parameters begin parameter #1: 3 :end parameter begin parameter #2:  :end
> parameter begin parameter #3:  :end parameter begin parameter #4:  :end
> parameter begin parameter #5: testUpdateUsingTempStorage :end parameter
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared
> statement
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE,
> STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with
> 5 parameters begin parameter #1: 2 :end parameter begin parameter #2:  :end
> parameter begin parameter #3:  :end parameter begin parameter #4:  :end
> parameter begin parameter #5: testUpdateUsingTempStorage :end parameter
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared
> statement
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: INSERT INTO CMP3_ADDRESS (CITY, PROVINCE,
> STREET, P_CODE, COUNTRY) VALUES (?, ?, ?, ?, ?) :End prepared statement with
> 5 parameters begin parameter #1: 1 :end parameter begin parameter #2:  :end
> parameter begin parameter #3:  :end parameter begin parameter #4:  :end
> parameter begin parameter #5: testUpdateUsingTempStorage :end parameter
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: values IDENTITY_VAL_LOCAL() :End prepared
> statement
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE,
> ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID,
> START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
> :End prepared statement with 12 parameters begin parameter #1: 406 :end
> parameter begin parameter #2: null :end parameter begin parameter #3: 3 :end
> parameter begin parameter #4: testUpdateUsingTempStorage :end parameter
> begin parameter #5: null :end parameter begin parameter #6: 3 :end parameter
> begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter
> begin parameter #9: 19 :end parameter begin parameter #10: null :end
> parameter begin parameter #11: null :end parameter begin parameter #12: null
> :end parameter
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID)
> VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1:
> 300 :end parameter begin parameter #2: 406 :end parameter
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE,
> ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID,
> START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
> :End prepared statement with 12 parameters begin parameter #1: 405 :end
> parameter begin parameter #2: null :end parameter begin parameter #3: 2 :end
> parameter begin parameter #4: testUpdateUsingTempStorage :end parameter
> begin parameter #5: null :end parameter begin parameter #6: 2 :end parameter
> begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter
> begin parameter #9: 20 :end parameter begin parameter #10: null :end
> parameter begin parameter #11: null :end parameter begin parameter #12: null
> :end parameter
> 2007-02-23 21:19:36.563 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID)
> VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1:
> 200 :end parameter begin parameter #2: 405 :end parameter
> 2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: INSERT INTO CMP3_EMPLOYEE (EMP_ID, PAY_SCALE,
> ROOM_NUM, F_NAME, STATUS, L_NAME, VERSION, MANAGER_EMP_ID, ADDR_ID,
> START_DATE, END_DATE, DEPT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
> :End prepared statement with 12 parameters begin parameter #1: 404 :end
> parameter begin parameter #2: null :end parameter begin parameter #3: 1 :end
> parameter begin parameter #4: testUpdateUsingTempStorage :end parameter
> begin parameter #5: null :end parameter begin parameter #6: 1 :end parameter
> begin parameter #7: 1 :end parameter begin parameter #8: null :end parameter
> begin parameter #9: 21 :end parameter begin parameter #10: null :end
> parameter begin parameter #11: null :end parameter begin parameter #12: null
> :end parameter
> 2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Executing prepared statement: INSERT INTO CMP3_SALARY (SALARY, EMP_ID)
> VALUES (?, ?) :End prepared statement with 2 parameters begin parameter #1:
> 100 :end parameter begin parameter #2: 404 :end parameter
> 2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Committing
> 2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2371),
> (SESSIONID = 43), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Committing
> 2007-02-23 21:19:36.579 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2359),
> (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Committing
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375),
> (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Begin compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE
> session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255),
> ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255),
> VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE,
> END_DATE DATE, DEPT_ID INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS
> NOT LOGGED :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375),
> (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Error compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE
> session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT NULL, PAY_SCALE VARCHAR(255),
> ROOM_NUM INTEGER, F_NAME VARCHAR(255), STATUS INTEGER, L_NAME VARCHAR(255),
> VERSION INTEGER, MANAGER_EMP_ID INTEGER, ADDR_ID INTEGER, START_DATE DATE,
> END_DATE DATE, DEPT_ID INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS
> NOT LOGGED :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375),
> (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Begin compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE
> session.TL_CMP3_SALARY (EMP_ID INTEGER NOT NULL, SALARY INTEGER, PRIMARY
> KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375),
> (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Error compiling prepared statement: DECLARE GLOBAL TEMPORARY TABLE
> session.TL_CMP3_SALARY (EMP_ID INTEGER NOT NULL, SALARY INTEGER, PRIMARY
> KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375),
> (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Begin compiling prepared statement: INSERT INTO session.TL_CMP3_EMPLOYEE(EMP_ID, ROOM_NUM, VERSION) SELECT
> t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0, CMP3_SALARY
> t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID =
> t0.EMP_ID)) :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375),
> (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Error compiling prepared statement: INSERT INTO session.TL_CMP3_EMPLOYEE(EMP_ID, ROOM_NUM, VERSION) SELECT
> t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0, CMP3_SALARY
> t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND (t1.EMP_ID =
> t0.EMP_ID)) :End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375),
> (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Begin compiling prepared statement: DELETE FROM session.TL_CMP3_EMPLOYEE:End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375),
> (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Error compiling prepared statement: DELETE FROM session.TL_CMP3_EMPLOYEE:End prepared statement
> 2007-02-23 21:19:36.594 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375),
> (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Begin compiling prepared statement: DELETE FROM session.TL_CMP3_SALARY:End prepared statement
> 2007-02-23 21:19:36.610 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375),
> (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Error compiling prepared statement: DELETE FROM session.TL_CMP3_SALARY:End prepared statement
> 2007-02-23 21:19:36.610 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375),
> (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DATABASE =
> C:/Dev_ri/properties/Derby1), (DRDAID = null), Rolling back
> 2007-02-23 21:19:36.610 GMT Thread[AWT-EventQueue-0,6,main] (XID = 2375),
> (SESSIONID = 44), (DATABASE = C:/Dev_ri/properties/Derby1), (DRDAID = null),
> Committing
>
>

Re: Global Temp tables

Posted by "Lance J. Andersen" <La...@Sun.COM>.
Mamta,

Thanks for taking the time to respond.


I had the developer run this using the embedded driver and attached the 
log.  It looks like the prepare is failing on the DECLARE.


I have attached the log for your reference.

Regards
Lance

Mamta Satoor wrote:
> Lance, I am sure you have already checked following but wanted to 
> throw them out anyways
> 1)Is the temporary table getting referenced by the same connection 
> that created it?
> 2)Does your particular scenario work under embedded Derby?
>  
> Also, once the connection that created the global table closes, the 
> global table cease to exist.
>  
> I think it will be worth checking the script under embedded Derby to 
> rule out Network Server as the culprit.
>  
> Mamta
>
>  
> On 2/23/07, *Lance J. Andersen* <Lance.Andersen@sun.com 
> <ma...@sun.com>> wrote:
>
>     Does anyone have an idea as to why the gobal table cannot be found.
>
>     Here is the trace output.
>
>     Regards
>     lance
>
>     [TopLink Fine]:
>     ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DECLARE
>     GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT
>     NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255),
>     STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, ADDR_ID INTEGER,
>     MANAGER_EMP_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID
>     INTEGER,
>     PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED
>     [TopLink Fine]:
>     ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DECLARE
>     GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY (EMP_ID INTEGER NOT
>     NULL,
>     SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT
>     LOGGED
>     [TopLink Fine]:
>     ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--INSERT
>     INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION) SELECT
>     t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0,
>     CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND
>     (t1.EMP_ID = t0.EMP_ID))
>     [TopLink Fine]:
>     ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DELETE
>
>     FROM session.TL_CMP3_EMPLOYEE
>     [TopLink Fine]:
>     ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DELETE
>     FROM session.TL_CMP3_SALARY
>     [TopLink Warning]:
>     UnitOfWork(31852201)--Thread(Thread[AWT-EventQueue-0,6,main])--Local
>     Exception Stack:
>     Exception [TOPLINK-4002] (Oracle ${_EssentialsProductName} -
>     ${_EssentialsProductVersion} (Build 070220Dev)):
>     oracle.toplink.essentials.exceptions.DatabaseException
>     Internal Exception: org.apache.derby.client.am.SqlException : Table
>     'SESSION.TL_CMP3_EMPLOYEE' does not exist.Error Code: -1
>     Call:INSERT INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION)
>     SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0,
>     CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND
>     (t1.EMP_ID = t0.EMP_ID))
>     Query:UpdateAllQuery()
>
>

Re: Global Temp tables

Posted by Mamta Satoor <ms...@gmail.com>.
Lance, I am sure you have already checked following but wanted to throw them
out anyways
1)Is the temporary table getting referenced by the same connection that
created it?
2)Does your particular scenario work under embedded Derby?

 Also, once the connection that created the global table closes, the global
table cease to exist.

I think it will be worth checking the script under embedded Derby to rule
out Network Server as the culprit.

Mamta


On 2/23/07, Lance J. Andersen <La...@sun.com> wrote:
>
> Does anyone have an idea as to why the gobal table cannot be found.
>
> Here is the trace output.
>
> Regards
> lance
>
> [TopLink Fine]:
>
> ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DECLARE
> GLOBAL TEMPORARY TABLE session.TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT
> NULL, PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, F_NAME VARCHAR(255),
> STATUS INTEGER, L_NAME VARCHAR(255), VERSION INTEGER, ADDR_ID INTEGER,
> MANAGER_EMP_ID INTEGER, START_DATE DATE, END_DATE DATE, DEPT_ID INTEGER,
> PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED
> [TopLink Fine]:
>
> ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DECLARE
> GLOBAL TEMPORARY TABLE session.TL_CMP3_SALARY (EMP_ID INTEGER NOT NULL,
> SALARY INTEGER, PRIMARY KEY (EMP_ID)) ON COMMIT DELETE ROWS NOT LOGGED
> [TopLink Fine]:
>
> ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--INSERT
> INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION) SELECT
> t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0,
> CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND
> (t1.EMP_ID = t0.EMP_ID))
> [TopLink Fine]:
>
> ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DELETE
> FROM session.TL_CMP3_EMPLOYEE
> [TopLink Fine]:
>
> ClientSession(12549034)--Connection(16309502)--Thread(Thread[AWT-EventQueue-0,6,main])--DELETE
> FROM session.TL_CMP3_SALARY
> [TopLink Warning]:
> UnitOfWork(31852201)--Thread(Thread[AWT-EventQueue-0,6,main])--Local
> Exception Stack:
> Exception [TOPLINK-4002] (Oracle ${_EssentialsProductName} -
> ${_EssentialsProductVersion} (Build 070220Dev)):
> oracle.toplink.essentials.exceptions.DatabaseException
> Internal Exception: org.apache.derby.client.am.SqlException: Table
> 'SESSION.TL_CMP3_EMPLOYEE' does not exist.Error Code: -1
> Call:INSERT INTO session.TL_CMP3_EMPLOYEE (EMP_ID, ROOM_NUM, VERSION)
> SELECT t0.EMP_ID, t1.SALARY, (t0.VERSION + 1) FROM CMP3_EMPLOYEE t0,
> CMP3_SALARY t1 WHERE ((t0.F_NAME = 'testUpdateUsingTempStorage') AND
> (t1.EMP_ID = t0.EMP_ID))
> Query:UpdateAllQuery()
>