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

Hi

I get the following error:

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
$BODY$DECLARE
tmp1 numeric;
account RECORD;
r b_line%rowtype;
BEGIN
tmp1 = 0.00;
FOR i IN 30..39 LOOP
FOR account IN SELECT id, account_id, name, type, amount_cred FROM
bo.obj_ledger WHERE account_id like (i || '__') ORDER BY id LOOP
IF account.type = 'P' THEN
tmp1 = tmp1 + account.amount_cred;
ELSE
tmp1 = tmp1 - account.amount_cred;
END IF;
END LOOP;
END LOOP;
r.account_text = 'Line1:';
r.amount = tmp1;
return next r;
RETURN;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;

I'm using the following versions:
pg80b1.308.jdbc3.jar
PostgreSQL 8.0.0rc1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.3.1 (mingw special 20030804-1)

Thanks for your help.

Regards,
Jerome

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

Hi Kris,

Thanks you very much for your help. It works perfectly now. I just
wonder why it didn't work with the callable statement. In my opinion it
should also work with the procedure call, since it is a stored
procedure. Of course it is not needed, since the statement

"SELECT * FROM myfunction()"

works perfectly, but according to an O'Reilly Article
(http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html?page=last)
the callable statement should also work, but maybe this is obsolete now.

Thanks again!
Jerome

On Wed, 22 Dec 2004, Jerome Colombie wrote:

> Thanks you very much for your help. It works perfectly now. I just
> wonder why it didn't work with the callable statement. In my opinion it
> should also work with the procedure call, since it is a stored
> procedure.

Postgresql doesn't really support stored procedures, only functions. In
time this function support was hacked to return sets, but it is not really
true stored procedure support. Notably from the caller's perspective how
can you tell what the difference is between a function that returns an int
and a function that returns a setof int, but returns only one row. In
both cases you get a one row, one column result. In the first case
CallableStatement.getObject should return Integer, but in the second case
you're suggesting it should return ResultSet. I'm unclear on how to make
this determination in the client.

> works perfectly, but according to an O'Reilly Article
> (http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html?page=last)
> the callable statement should also work, but maybe this is obsolete now.
>

This examples shows returning a refcursor, not a setof <type>. This does
work, and is different because a refcursor is a single scalar value (a
cursor name) that may then be transformed into a true ResultSet.

Kris Jurka

Until version 8 (now using build 308), I used a prepared statement

INSERT INTO mytable(array_column) VALUES(?);

and

setString(1, my_formatter(java_array));

where my_formatter turns a Java double[] into a string in the form {1.0} .

This doesn't work any more, with the error message that character
varying can not be converted to real[] . I think it's with the driver,
because the braces form still works fine with psql. I changed the
formatter to produce a string in ARRAY[1.0] notation with the same error
message.

On Wed, 22 Dec 2004, Andrew Lazarus wrote:

> Until version 8 (now using build 308), I used a prepared statement
>
> INSERT INTO mytable(array_column) VALUES(?);
>
> setString(1, my_formatter(java_array));
>
> This doesn't work any more, with the error message that character
> varying can not be converted to real[] . I think it's with the driver,
> because the braces form still works fine with psql.

This is an expected error message. The 8.0 driver uses strongly typed
parameters. By using setString you are claiming that you have varchar
data. The psql equivalent is:

The old driver:

jurka=# select '{1}'::int[];
int4
------
{1}
(1 row)

The 8.0 driver:

jurka=# select '{1}'::varchar::int[];
ERROR: cannot cast type character varying to integer[]

The correct JDBC solution is to use setArray() or setObject(). The
postgresql JDBC driver doesn't support using setObject on Java arrays, so
that's out. The pg implementation of setArray is very fragile and
requires a specific java.sql.Array implementation. So that's less than
ideal (especially considering the extra code/work on the client side to
construct such an array).

The move to strong typing, especially without providing workarounds (like
this case), is definitely going to be a problem in the 8.0 release. This
is part of the growing pains the driver has to go through to use the V3
protocol to its fullest.

Unfortunately your options at this point are:
- Use the 7.4 driver.
- Use the 8.0 with the protocolVersion=2 URL parameter.
- Provide a java.sql.Array implementation
- Add support for java arrays in setObject
- or complain loudly enough that someone else will

Kris Jurka