Re: Behaviour of Global Temporary Table In HTTPSession(Java Servlet or JSP)

My Responses are below

--

~~~~~~~~~~~~~~~~
Chris Weiss
www.hpdbe.com
High Performance Database Engineering
~~~~~~~~~~~~~~~~


"Arun Solleti" <solleti_at_hotmail.com> wrote in message
news:3CA7D4B6.1A340F13_at_hotmail.com...


> Hi

>

> I have a web application which is deployed on Tomcat. For Oracle

> Database transactions, I am using Connection Pooling to get a Connection

>

> to my Database.

>

> First i open fetch a connection from the pool inside a JSP/Servlet, then

>

> i call a Oracle Stored Procedure(using the Java CallableStatement) which

>

> performs the processing and then populate a global temporary table.

> After

> the execution of the CallableStatement, i run a simple SELECT statement

> to display the results.

> Afterwards i release the connection to the pool.

>

> 1) Will the data in the Global Temporary Table still persists untill my

> session expires or will it be lost after i close my ResultSet Object and

>

> then release the connection back to the pool.


If the connect string on the connection is not reset, the temporary table
will persist.  However, once the connect string is reset, the session is
lost and all the temporary data is truncated.

If another user gets the same connection but does not reset the connection,
the temporary data may be invalid.

Connection pooling will not guarantee the same connection for the same user.
It simply saves the overhead of building a new connection.  You should be
able to reconnect with destroying the existing connection.  This will reset
the session specific data.




> 2) Will i get the same information again if i come back to the same page

>

> after some time or reload the page because the pool may give me a

> different

> connection when i do HTTP request again on the same JSP/Servlet. So

> even though i am in the same session i have different database

> connection so

> will this cause any problems for me to access the data populated by

> different

> database connection which actually populated it.


See above.  Yes, it will be a problem.



> I am currently under the impression that the data will persist untill my

> HTTPSession of JSP gets expired, but when i reload the JSP that i may

> get a different Connection object(Because of the

> Connection Pooling) and when i do run the same SELECT statement on the

> temporary table i may get different or no results.


Yes, that is correct.  Connection pooling and temporary data does not work
well.  A better solution is to create a cached nologging table that contains
a session id.  As long as the users's web session id persists, you can use
this as a key to query permanent tables intended to hold temporary data.  By
making the table no logging and cached, you will accomplish much of what you
wanted with global tempoary tables.  However, you may need a DBMS_JOB or a
cron process to do periodic clean up.


 > Any inputs would of help.


> Thanks

> Arun

>