Re: prepared statement call fails

Thanks Thomas. I'll try it your way to see what happens, but
according to the Postgresql documentation, it should support callable
statements. I posted the relevent text from the JDBC section of the
online docs below:

<quote>
PostgreSQL's JDBC driver fully supports calling PostgreSQL
stored functions.

Example 31-4. Calling a built in stored function

This example shows how to call a PostgreSQL built in
function, upper, which simply converts the supplied
string argument to uppercase.

// Turn transactions off.
con.setAutoCommit(false);
// Procedure call.
CallableStatement upperProc = con.prepareCall("{ ? = call
upper( ? ) }");
upperProc.registerOutParameter(1, Types.VARCHAR);
upperProc.setString(2, "lowercase to uppercase");
upperProc.execute();
String upperCased = upperProc.getString(1);
upperProc.close();
<end quote>

On Sun, 05 Dec 2004 20:08:54 +0100, Thomas Hallgren
<thhal(at)mailblocks(dot)com> wrote:
> Larry White wrote:
>
>
> > I'm hoping someone with more experience can help me find a problem in
> > calling a function from Java. This is the first time I'm trying this
> > so I'm guessing it will be straightforward.
> >
> > The function's signature is:
> >
> > add_country(bigint, char(2), char(3), varchar(60))
> > RETURNS INTEGER '
> >
> > It works fine if I call it from the command line, like so...
> >
> > select add_country(124,'US', 'USA', 'United States of America');
> >
> > In java, I call it using the following:
> >
> > CallableStatement proc = null;
> > proc = connection.prepareCall("{ ? = call add_country( ? ? ? ? ) }");
> > proc.registerOutParameter(1, java.sql.Types.INTEGER);
> > proc.setInt(2, 124);
> > proc.setString(3, code2); // a two character java String
> > proc.setString(4, code3); // a three character java String
> > proc.setString(5, name); // a Java String
> > proc.execute();
> >
> AFAIK, CallableStatement is for stored procedure calls. Stored
> procedures are not yet implemented in PostgreSQL. It only has functions.
> In order to call a function you need a select statement and a normal
> PreparedStatement. Try this:
>
> PreparedStatement stmt = connection.prepareStatement("select
> add_country(?,?,?,?)");
> stmt.setInt(1, 124);
> stmt.setString(2, code2);
> stmt.setString(3, code3);
> stmt.setString(4, code4);
> ResultSet rs = stmt.executeQuery();
> if(rs.next())
> result = rs.getInt(1);
>
> Kind regards,
> Thomas Hallgren
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>