Re: CallableStatement: java.sql.Types=12 however type

On Tue, 21 Dec 2004, Jerome Colombie wrote:

> org.postgresql.util.PSQLException: A CallableStatement function was
> executed and the return was of type java.sql.Types=12 however type
> java.sql.Types=1111 was registered.
>
> when running the following code:
>
>                conn.setAutoCommit(false);
>                 CallableStatement stmt = conn.prepareCall("{ ? = call
> myfunction1() }");
>                 stmt.registerOutParameter(1, Types.OTHER);
>                 stmt.execute();
>                 ResultSet rs = (ResultSet) stmt.getObject(1);
>                 while (rs.next()) {
>                     result = result + rs.getString(1);
>                     result = result + rs.getDouble(2);
>                 }
>
> create type b_line as (account_text varchar(255), amount numeric);
>
> CREATE OR REPLACE FUNCTION myfunction1()
>   RETURNS setof b_line AS

The JDBC driver is expecting a single scalar value returned from your
function.  It retrieves the first column in the first row and detects that
it is a varchar, not the Types.OTHER that you had registered.  You really
don't want to use the CallableStatement interface for this operation.  Try
instead:

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM myfunction()");

Kris Jurka