agora inbox for pljava-dev@postgresql.org
help / color / mirror / Atom feed[Pljava-dev] retaining prepared statements across stored procedure calls
4+ messages / 0 participants
[nested] [flat]
* [Pljava-dev] retaining prepared statements across stored procedure calls
@ 2011-04-11 20:01
2011-04-11 22:03 ` [Pljava-dev] retaining prepared statements across stored procedure calls
0 siblings, 1 reply; 4+ messages in thread
From: @ 2011-04-11 20:01 UTC (permalink / raw)
Hi,
I have a stored procedure class that looks like this:
class TestSP{ public static TestSP testSP = null; // singleton instance public PreparedStatement stmt; // member variable
TestSP() throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection"); this.stmt = conn.prepareStatement("select 1"); conn.close(); }
// stored procedure public static int callProc () throws SQLException { if (testSP == null) testSP = new TestSP();
ResultSet rs = testSP.testStmt.executeQuery();
rs.next(); return(rs.getInt(1)); }}
When I call callProc in psql, I get a warning about closing 1 forgotten statement. Does that mean I cannot have prepared statements lingering around after each store procedure invocation?
Thanks,Alvin
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20110411/bc9548d7/attachment.html;
^ permalink raw reply [nested|flat] 4+ messages in thread
* [Pljava-dev] retaining prepared statements across stored procedure calls
2011-04-11 20:01 [Pljava-dev] retaining prepared statements across stored procedure calls
@ 2011-04-11 22:03 `
2011-04-15 23:25 ` [Pljava-dev] retaining prepared statements across stored procedure calls
0 siblings, 1 reply; 4+ messages in thread
From: @ 2011-04-11 22:03 UTC (permalink / raw)
Thanks. In my case is there still any advantage of using prepared statements (as opposed to creating new statements every time when callProc is called)?
Date: Mon, 11 Apr 2011 22:40:21 +0200
From: thomas at tada.se
To: acheung02 at hotmail.com
Subject: Re: [Pljava-dev] retaining prepared statements across stored procedure calls
On 2011-04-11 22:01, Alvin C wrote:
Hi,
I have a stored procedure class that
looks like this:
class TestSP
{
public static TestSP testSP = null; //
singleton instance
public PreparedStatement stmt; //
member variable
TestSP() throws SQLException
{
Connection conn =
DriverManager.getConnection("jdbc:default:connection");
this.stmt = conn.prepareStatement("select 1");
conn.close();
}
// stored procedure
public static int callProc () throws SQLException
{
if (testSP == null)
testSP = new TestSP();
ResultSet rs = testSP.testStmt.executeQuery();
rs.next();
return(rs.getInt(1));
}
}
When
I call callProc in psql, I get a warning about closing 1
forgotten statement. Does that mean I cannot have prepared
statements lingering around after each store procedure
invocation?
Yes it does.
- thomas
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20110411/e58c3a45/attachment.html;
^ permalink raw reply [nested|flat] 4+ messages in thread
* [Pljava-dev] retaining prepared statements across stored procedure calls
2011-04-11 20:01 [Pljava-dev] retaining prepared statements across stored procedure calls
2011-04-11 22:03 ` [Pljava-dev] retaining prepared statements across stored procedure calls
@ 2011-04-15 23:25 `
2011-04-16 05:27 ` [Pljava-dev] retaining prepared statements across stored procedure calls
0 siblings, 1 reply; 4+ messages in thread
From: @ 2011-04-15 23:25 UTC (permalink / raw)
>> When
>> I call callProc in psql, I get a warning about closing 1
>> forgotten statement. Does that mean I cannot have prepared
>> statements lingering around after each store procedure
>> invocation?
>>
> Yes it does.
> - thomas
Is there a good reason for this?
Is there something wrong with initializing prepared statements within a
static initializer?
PreparedStatment prep;
static {
//...
prep = connection.prepareStatement( "select 1;" );
//...
}
public static useStatement() {
//...
prep.executeQuery();
//...
}
--
Johann Oskarsson http://www.2ndquadrant.com/ |[]
PostgreSQL Development, 24x7 Support, Training and Services --+--
|
Blog: http://my.opera.com/myrkraverk/blog/
^ permalink raw reply [nested|flat] 4+ messages in thread
* [Pljava-dev] retaining prepared statements across stored procedure calls
2011-04-11 20:01 [Pljava-dev] retaining prepared statements across stored procedure calls
2011-04-11 22:03 ` [Pljava-dev] retaining prepared statements across stored procedure calls
2011-04-15 23:25 ` [Pljava-dev] retaining prepared statements across stored procedure calls
@ 2011-04-16 05:27 `
0 siblings, 0 replies; 4+ messages in thread
From: @ 2011-04-16 05:27 UTC (permalink / raw)
Yes, there are reasons. One reason is that calls can come from functions with different SecurityManagers. Keeping a
state between those calls could introduce security leaks and IIRC, the statement is attached to an SPI context. Another
reason is memory preservation. There is no natural way of doing a proper clean up unless you do it when the call ends.
There might be other reasons as well. It's been a while since I looked at the actual code.
- thomas
On 2011-04-16 01:25, Johann 'Myrkraverk' Oskarsson wrote:
>
>>> When
>>> I call callProc in psql, I get a warning about closing 1
>>> forgotten statement. Does that mean I cannot have prepared
>>> statements lingering around after each store procedure
>>> invocation?
>>>
>> Yes it does.
>> - thomas
>
> Is there a good reason for this?
>
> Is there something wrong with initializing prepared statements within a static initializer?
>
> PreparedStatment prep;
>
> static {
> //...
> prep = connection.prepareStatement( "select 1;" );
> //...
> }
>
> public static useStatement() {
> //...
> prep.executeQuery();
> //...
> }
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-04-16 05:27 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-11 20:01 [Pljava-dev] retaining prepared statements across stored procedure calls
2011-04-11 22:03 `
2011-04-15 23:25 `
2011-04-16 05:27 `
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox