Thread: Re: [JDBC] Prepare Statement

Kris,
I have another question, I saw some discussion regarding
PreparedStatement work with array argument, I get a error when I try to
play with it.
E.g.
I have myfunction(int[]),
So,
PrepareStatement st = conn.prepareStatment("SELECT myfunction(?)");
String arr="{1,2,3}";
St.setString(1,arr};
Result rs = st.executeQuery();

Then it will complaint when it run:
Myfuntion(text) does not exist!

Did I miss something??


Thanks.


Jie Liang

-----Original Message-----
From: Kris Jurka [mailto:books@ejurka.com]
Sent: Thursday, June 17, 2004 3:26 PM
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:

> 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

On Thu, 17 Jun 2004, Jie Liang wrote:

> Kris,
> I have another question, I saw some discussion regarding
> PreparedStatement work with array argument, I get a error when I try to
> play with it.
> E.g.
> I have myfunction(int[]),
> So,
> PrepareStatement st = conn.prepareStatment("SELECT myfunction(?)");
> String arr="{1,2,3}";
> St.setString(1,arr};
> Result rs = st.executeQuery();
>
> Then it will complaint when it run:
> Myfuntion(text) does not exist!
>


This is actually a case where prepared statements actually cause trouble.
With the directly executed SELECT myfunction('{1,2,3}'); The backend can
determine that there is only one version of myfunction so it can convert
the unkown argument type to it, but note that this won't work if
myfunction is overloaded.

With the prepared case, you must tell it what types to use when doing the
prepare.  The JDBC driver doesn't have a whole lot of information to work
with, so it takes what it knows (that you called setString) and says the
argument is of type text, issuing a prepare like this:  PREPARE
JDBC_STATEMENT_1(text) AS SELECT myfunction($1);  At this time (before it
actually calls EXECUTE) it tries to lookup myfunction that takes a text
argument and determines there isn't one.  In this case it doesn't have the
opportunity to apply any casts because we were quite clear in specifying
that it should take a text argument, not one of unknown type.

Ideally you should be using setArray, but there is no existing way to
create Array objects and I'm not sure that code would work even if there
was.

Kris Jurka