Thread: CallableStatement: java.sql.Types=12 however type java.sql.Types=1111 was registered
CallableStatement: java.sql.Types=12 however type java.sql.Types=1111 was registered
From
Jerome Colombie
Date:
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
Re: CallableStatement: java.sql.Types=12 however type java.sql.Types=1111
From
Jerome Colombie
Date:
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