Skip to main content
A
Anonymous
February 24, 2010
Answered

Oracle Global Temporary Tables and Connection Pooling

  • February 24, 2010
  • 1 reply
  • 1800 views

When connection pooling, (“Maintain Connections” in cfadmin) is enabled.  Oracle Global Temporary tables get messed up.  Data bleeds from one page request to the next because CF is reusing the same Oracle session over and over again.

Is there a JDBC driver that will allow the use of Global Temp Tables while having connection pooling enabled? 

Other application servers, like Oracle Application Server, somehow allow the pooling and GTT work together harmoniously.

    This topic has been closed for replies.
    Correct answer Dan_Bracuk

    If your temporary tables were created with the default setting of on commit delete rows, the cftransaction tag will help you.  This code:

    <!---
    <cfquery name="x" datasource="burns">
    create global temporary table t_dan_test (id integer)
    </cfquery>

    --->

    <cftransaction>
    <cfquery name="i" datasource="burns">
    insert into t_dan_test values(1)
    </cfquery>
    <cfquery  name="x1" datasource="burns">
    select * from t_dan_test
    </cfquery>
    </cftransaction>
    <cfquery  name="x2" datasource="burns">
    select * from t_dan_test
    </cfquery>
    <cfdump var="#x1#">
    <cfdump var="#x2#">

    resulted in x1 dumping 1 record and x2 dumping 0.

    1 reply

    D
    Dan_BracukCorrect answer
    Inspiring
    February 25, 2010

    If your temporary tables were created with the default setting of on commit delete rows, the cftransaction tag will help you.  This code:

    <!---
    <cfquery name="x" datasource="burns">
    create global temporary table t_dan_test (id integer)
    </cfquery>

    --->

    <cftransaction>
    <cfquery name="i" datasource="burns">
    insert into t_dan_test values(1)
    </cfquery>
    <cfquery  name="x1" datasource="burns">
    select * from t_dan_test
    </cfquery>
    </cftransaction>
    <cfquery  name="x2" datasource="burns">
    select * from t_dan_test
    </cfquery>
    <cfdump var="#x1#">
    <cfdump var="#x2#">

    resulted in x1 dumping 1 record and x2 dumping 0.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices