Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (herse.apache.org: domain of msatoor@gmail.com designates
 66.249.92.170 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references;
        b=E1A7bXzMLu59HipoMt2tDBu343+OkmRoBD75uCt20fjjuB93iGMr/rmjcM7TU6H/UZP6sfNlZdyzTQbZbVsE61FK6Qla374IlhcpzD4yzM0AXxKLN4xLdaQaB41uDzXzX+2pwqgkMcF3+BUMYezHdLA6SHiCwbLkWGgDWrno4Cw=
Message-ID: <d9619e4a0702230951r673f0e1fhca97b8bd8ec11ae4@mail.gmail.com>
Date: Fri, 23 Feb 2007 09:51:06 -0800
From: "Mamta Satoor" <msatoor@gmail.com>
To: derby-dev@db.apache.org
Subject: Re: Global Temp tables
In-Reply-To: <45DF0B36.8080103@sun.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_21283_20080942.1172253066725"
References: <45DF0B36.8080103@sun.com>

------=_Part_21283_20080942.1172253066725
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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> 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()
>

------=_Part_21283_20080942.1172253066725
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_21283_20080942.1172253066725--