Thread: Function not found with JDBC 8.0 driver and later

I'm running PostgreSQL 8.0.3 for Windows. In the database I have a function defined like this:

CREATE OR REPLACE FUNCTION get_property_list2(text, date, int4, int4)
...
 
The parameters are passed from JDBC like this:

PreparedStatement ps
ps.setInt(3, ((Integer)queryParameters[2]).intValue());
ps.setLong(4, ((Long)queryParameters[3]).longValue());

With pg73jdbc3.jar the function call works fine. With pg80b1.308.jdbc3.jar or postgresql-8.0-311.jdbc3.jar, the driver can't seem to recognize the database function. I get stack trace as shown below. Seems like there's a mismatch with long integer type mapping between 8.0+ JDBC drivers and PostgreSQL? Can this be looked at for future release of the driver? Thanks.

java.sql.SQLException: ERROR: function get_property_list2(character varying, date, integer, bigint) does not exist
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:389)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:240)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)

This is because the 8.0 drivers expect you to pass an int when the function signature is int. Parameter 4 is an int, and you are passing a long.

Donny Tjandra wrote:
> I'm running PostgreSQL 8.0.3 for Windows. In the database I have a
> function defined like this:
>
> CREATE OR REPLACE FUNCTION get_property_list2(text, date, int4, int4)
> ...
>
> The parameters are passed from JDBC like this:
>
> PreparedStatement ps
> ps.setInt(3, ((Integer)queryParameters[2]).intValue());
> ps.setLong(4, ((Long)queryParameters[3]).longValue());
>
> With pg73jdbc3.jar the function call works fine. With
> pg80b1.308.jdbc3.jar or postgresql-8.0-311.jdbc3.jar, the driver can't
> seem to recognize the database function. I get stack trace as shown
> below. Seems like there's a mismatch with long integer type mapping
> between 8.0+ JDBC drivers and PostgreSQL? Can this be looked at for
> future release of the driver? Thanks.

This is an application bug -- either explicitly cast the parameter to
the right type in the query itself, or use an appropriate parameter
setting method (setInt or setObject(..., Types.INTEGER) in your case).

The change was that the 8.0 driver now uses server-side preparation of
queries which means that parameters are more strongly typed -- you don't
get the previous implicit conversion from a textual representation of
the parameter to whatever type seems correct.

-O