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