Skip site navigation (1) Skip section navigation (2)

Re: How do I ensure same session over multiple statements??

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Sean Shanny <shannyconsulting(at)earthlink(dot)net>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: How do I ensure same session over multiple statements??
Date: 2004-02-21 00:04:40
Message-ID: 4036A098.7080608@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Sean Shanny wrote:
> To all,
> 
> I have a situation where I need to issue the following:
> 
> set enable_nestloop = off
> 
> before executing a prepared statement.  We are using connection pooling. 
> m_conn is a connection object that is retrieved earlier in the code.
> 
> Code fragment....
> 
> PreparedStatement ps = null;
>        ResultSet rs = null;
> 
>        try
>        {
>            NEED TO DO THE SET HERE
>            ps = m_conn.prepareStatement(
>                "SELECT t1.id, t2.md5, t2.url from referral_temp t2 LEFT 
> OUTER JOIN d_referral t1 ON t2.md5 = t1.referral_md5" );
>            ps.setFetchSize( T_FETCH_SIZE );
> 
>            rs = ps.executeQuery();
> 
>            WHEN I AM DONE LIKE TO REVERSE THE SET
> 
>       .......
> 
> I can do this just fine through psql since I am always in the same 
> session.  How would I do that given the above code fragment?

'Statement.executeUpdate("set enable_nestloop = off")' should do the 
trick, unless your connection pooling implementation is very strange. 
All the pools I've seen give you exclusive access to the connection 
you're given until you return it to the pool (via close() etc) for 
exactly this reason -- there is per-connection state that is important. 
'set' is one case of this, but transaction state is the more obvious one!

You'll want to either put the un-"set" code into a finally block before 
returning the connection to the pool or teach your connection pooling 
implementation about per-connection state and how to reset it, though, 
or you might leak the setting to other users of the connection.

-O

In response to

pgsql-jdbc by date

Next:From: Chris SmithDate: 2004-02-21 15:17:11
Subject: Re: PATCH: SSL documentation and flexibility
Previous:From: Oliver JowettDate: 2004-02-20 23:57:48
Subject: Re: JDBC3 and 7.4.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group