Обсуждение: Re: [JDBC] Prepare Statement

Kris,
You are right, I modified that piece of code a little bit,
CallableStatement stmt = conn.prepareCall("{?=call chr(?)}");
Then my log file were:
Select * from chr(65) as result;
Select * from chr(66) as result;
......
However, if I use:
PrepareStatement stmt = conn.prepareStatement("SELECT chr(?)");
Then my log file are same as yours.i.e. it use PREPARE and EXECUTE.

So, I am getting confusion.
I think CallableStatement is extended from PrepareStatement, it should
have same behaviou.

Any comment?


Thanks.


Jie Liang


-----Original Message-----
From: Kris Jurka [mailto:books@ejurka.com]
Sent: Thursday, June 17, 2004 11:59 AM
To: Jie Liang
Cc: Tom Lane; pgsql-sql@postgresql.org; pgsql-jdbc@postgresql.org
Subject: RE: [JDBC] Prepare Statement




On Thu, 17 Jun 2004, Jie Liang wrote:

> Kirs,
>
> I re-compile with setUseServerPrepare(true), it works fine, thanks.
> However, reading from my log file, what I saw is that five same
> SELECTs with different argument, so I am wondering that the
> PrepareStatement really save time than individualy execute five
> SELECTs ???
>

This is what I see in the log file:

2004-06-17 11:55:35 [23254] LOG:  statement: PREPARE
JDBC_STATEMENT_1(integer) AS SELECT  $1 ; EXECUTE JDBC_STATEMENT_1(1)
2004-06-17 11:55:35 [23254] LOG:  statement: EXECUTE JDBC_STATEMENT_1(2)
2004-06-17 11:55:35 [23254] LOG:  statement: EXECUTE JDBC_STATEMENT_1(3)
2004-06-17 11:55:35 [23254] LOG:  statement: EXECUTE JDBC_STATEMENT_1(4)
2004-06-17 11:55:35 [23254] LOG:  statement: EXECUTE JDBC_STATEMENT_1(5)
2004-06-17 11:55:35 [23254] LOG:  statement: DEALLOCATE JDBC_STATEMENT_1

I don't know why this would be different for you.  What exact version of

the server and driver are you using?

Kris Jurka

On Thu, 17 Jun 2004, Jie Liang wrote:

> Kris,
> You are right, I modified that piece of code a little bit,
> CallableStatement stmt = conn.prepareCall("{?=call chr(?)}");
> Then my log file were:
> Select * from chr(65) as result;
> Select * from chr(66) as result;
> ......
> However, if I use:
> PrepareStatement stmt = conn.prepareStatement("SELECT chr(?)");
> Then my log file are same as yours.i.e. it use PREPARE and EXECUTE.
>
> So, I am getting confusion.
> I think CallableStatement is extended from PrepareStatement, it should
> have same behaviou.
>

What's happening here is that you can only use prepared statements for
certain operations.  You can't for example prepare a CREATE TABLE
statement.  The driver examines the query to see if it is valid for
preparing and I believe the problem here is that with a callable statement
it is examinging the query with "call" before it is transformed to a
SELECT, so it doesn't recognize it as a preparable.  This looks like a bug
to me.

Kris Jurka