Re: prepared statement call fails

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