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:

     
           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();


On Sun, 05 Dec 2004 20:08:54 +0100, Thomas Hallgren
 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@postgresql.org)
>