From: acheung02 at hotmail.com (Alvin C)
Date: Mon, 11 Apr 2011 22:03:23 +0000
Subject: [Pljava-dev] retaining prepared statements across stored
 procedure calls
In-Reply-To: <4DA36735.4080402@tada.se>
References: <DUB103-w4058D109F7165ACBB748BCBEA80@phx.gbl>,
	<4DA36735.4080402@tada.se>
Message-ID: <DUB103-w619D7BE5CD6732004A3A11BEA80@phx.gbl>


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>