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