[Pljava-dev] retaining prepared statements across stored procedure calls

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>