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

Re: Prepare Statement

From: "Jie Liang" <jie(at)stbernard(dot)com>
To: "Kris Jurka" <books(at)ejurka(dot)com>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, <pgsql-sql(at)postgresql(dot)org>,<pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Prepare Statement
Date: 2004-06-17 00:53:07
Message-ID: E7E213858379814A9AE48CA6754F5ECB034518C2@mail01.stbernard.com (view raw or flat)
Thread:
Lists: pgsql-jdbcpgsql-sql
Kris,
Thank you for your valuable response, I used the code you list
following:
import java.sql.*;

public class ServerSidePreparedStatement
{

	public static void main(String args[]) throws Exception
	{
		Class.forName("org.postgresql.Driver");
		String url = "jdbc:postgresql://localhost:5432/test";
		Connection conn =
DriverManager.getConnection(url,"test","");

		PreparedStatement pstmt = conn.prepareStatement("SELECT
?");

		// cast to the pg extension interface
		org.postgresql.PGStatement pgstmt =
(org.postgresql.PGStatement)pstmt;

		// on the third execution start using server side
statements
		pgstmt.setPrepareThreshold(3);

		for (int i=1; i<=5; i++)
		{
			pstmt.setInt(1,i);
			boolean usingServerPrepare =
pgstmt.isUseServerPrepare();
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			System.out.println("Execution: "+i+", Used
server side: " + usingServerPrepare + ", Result: "+rs.getInt(1));
			rs.close();
		}

		pstmt.close();
		conn.close();
	}
}
Then, the compiler complaint:
ServerSidePreparedStatement.java:20: cannot resolve symbol symbol  :
method setPrepareThreshold  (int)
location: interface org.postgresql.PGStatement
                pgstmt.setPrepareThreshold(3);
I downloaded pg74.213.jdbc2.jar and pg74.213.jdbc2ee.jar at
http://jdbc.postgresql.org/download.html
And had a try, I got same error msg.

I use java 1.3.1, postgresql -7.4.2, FreeBSD 4.7

What I need to do to make it work??

Thanks.



Jie Liang

-----Original Message-----
From: Kris Jurka [mailto:books(at)ejurka(dot)com] 
Sent: Tuesday, June 15, 2004 11:00 AM
To: Jie Liang
Cc: Tom Lane; pgsql-sql(at)postgresql(dot)org; pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] Prepare Statement




On Mon, 14 Jun 2004, Jie Liang wrote:

> I have a question about performance, in SQL commands: there is a 
> prepare/execute command, document says it will improve the performance

> while repeatly execute a statement. In java.sql: there is a 
> PreparedStatement object, which can store precompiled SQL statement, 
> document says it can improve the performance also. If I use java jdbc 
> to connect postgresql database, which one I should use? Can I use 
> both?
> 

When using JDBC it is best to use the standard
Statement/PreparedStatement 
interfaces.  It is possible to directly use PREPARE/EXECUTE, but this
can 
be handled by the driver.  Let me give you a run down of the different 
driver versions and their capabilities:

Current released version: can enable using PREPARE/EXECUTE behind the 
scenes on PreparedStatement by casting the prepared statement to 
PGStatement and issuing setUseServerPrepare.

Current cvs version: can enable using PREPARE/EXECUTE by setting an 
execution threshold that will turn it on when reached.  This threshold
can 
be set at a number of levels, see the following for more information

http://www.ejurka.com/pgsql/docs/cvs/ch09s05.html

Soon to be committed cvs version: can directly use server prepared
statements without using the SQL level PREPARE/EXECUTE.

Kris Jurka

Responses

pgsql-sql by date

Next:From: Greg Sabino MullaneDate: 2004-06-17 03:03:31
Subject: Re: Is there a faster way to do this?
Previous:From: ctrlDate: 2004-06-16 21:49:43
Subject: help with Postgres function

pgsql-jdbc by date

Next:From: Barry LindDate: 2004-06-17 00:59:43
Subject: Re: Nested transactions
Previous:From: Simon RiggsDate: 2004-06-16 22:45:36
Subject: Re: Nested transactions

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