Skip site navigation (1) Skip section navigation (2)

Re: Exception while executing function with CallableStatement

From: Kris Jurka <books(at)ejurka(dot)com>
To: "Philip A(dot) Chapman" <pchapman(at)pcsw(dot)us>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Exception while executing function with CallableStatement
Date: 2004-03-08 06:53:24
Message-ID: Pine.BSO.4.56.0403080100370.14560@leary.csoft.net (view raw or flat)
Thread:
Lists: pgsql-jdbc

On Sun, 7 Mar 2004, Philip A. Chapman wrote:

> org.postgresql.util.PSQLException: ERROR: function instimelog(integer,
> integer, integer, "unknown", integer, integer, "unknown", "unknown")
> does not exist
> 
> Since I'm setting the type using Types.INTEGER and Types.VARCHAR, I do
> not know why the exception is listing the types for the eight and nineth
> fields as "unknown".  I would appreciate any help.
> 

This is indeed a bug, but in your case the error is because the function 
does not exist.  There are two possible problems that I saw in my testing:

I created three functions

CREATE FUNCTION nullfunc(a timestamptz) RETURNS timestamptz AS 
	'BEGIN RETURN $1; END;' LANGUAGE plpgsql
CREATE FUNCTION nullfunc(a int) RETURNS int AS
	'BEGIN RETURN $1; END;' LANGUAGE plpgsql
CREATE FUNCTION nullfunc(a text) RETURNS text AS
	'BEGIN RETURN $1; END;' LANGUAGE plpgsql

and java code that tries to call the int version:

CallableStatement cs = conn.prepareCall("{? = call nullfunc(?)}");
cs.registerOutParameter(1, Types.INTEGER);
cs.setNull(2, Types.INTEGER);
cs.execute();
int result = cs.getInt(1);

This resulted in the text version being called even though you would 
expect the int version to be called, resulting in:

	A CallableStatement Function was executed and the return was of
	type (java.sql.Types=12) however type=java.sql.Types=4 was
	registered.

After deleting the text function the backend couldn't determine which 
function to call and I get:

	ERROR: function nullfunc("unknown") is not unique

At the moment no casting is done for any function arguments.  We could do 
something like the server side prepared statement's code in 
AbstractJdbc1Statement.transformToServerPrepare for CallableStatements as 
well.  Would we want that for all arguments or just for null ones?  Adding 
the casts for non-null parameters would allow the selection between two 
functions like f(int4) and f(int2), but could likely cause other 
problems/unexpected behavior.

Kris Jurka


Attachment: cs.java
Description: text/plain (975 bytes)

In response to

pgsql-jdbc by date

Next:From: Harry MantheakisDate: 2004-03-08 11:28:56
Subject: Using auto-commit OFF for transactions - instead of BEGIN
Previous:From: Andrew LazarusDate: 2004-03-08 05:21:39
Subject: Re: Exception while executing function with CallableStatement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group