So, I think that PreparedStatement should have a way at least case a
String to an Array or a way to create a Array, because of
conn.prepareStatement("SELECT myfunction('{1,2,3}')") is NOT very
useful.Comment? Jie Liang -----Original Message----- From: Kris Jurka [mailto:[EMAIL PROTECTED] Sent: Thursday, June 17, 2004 10:47 PM To: Jie Liang Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [JDBC] Prepare Statement 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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
