Re: Problems with Timestamp and Function Calls in JDBC/Prepared

[Kris Jurka <books(at)ejurka(dot)com>]

On Fri, 21 Apr 2006, tarabas(at)tarabas(dot)de wrote:

> I am having a bit trouble with function-calls containing timestamps.
>
> CREATE OR REPLACE FUNCTION do_something(int8, int8, int8, int8,
> timestamptz, timestamptz, timestamptz, timestamptz, int8, timestamptz,
> "varchar", int8, int8, "numeric", int8, int8)
>  RETURNS int8 AS
>
> When I now try to call the function through the JDBC 8.1-405 JDBC 3
> with a prepared Statement I do this:
>
> 			ps.setDouble(14, param14);

I belive this line is actually your problem, not the timestamps.  Your 
function is declared to take numeric, so you should ps.setBigDecimal() to 
get that type.  setDouble implies a float8 type which does not work...

CREATE FUNCTION f(numeric) RETURNS int AS 'SELECT 1;' LANGUAGE sql;
SELECT f(1::float8);
ERROR:  function f(double precision) does not exist


> The odd thing is that when I do a setTimestamp() and the param is null,
> the type timestamp seems to be used correctly (it is set to 1184). But
> when the Timestamp is filled, the type is set to 0 in the prepared
> statement.

When passing timestamp values to the server the JDBC driver must leave 
them as an unresolved type because it does not know if it is a timestamp 
or timestamptz.  This is not an issue with a null value because in that 
case the conversion between timestamp and timestamptz cannot alter the 
value, so we can give the server a hint about the type involved.

Kris Jurka