Thread: Problems with Timestamp and Function Calls in JDBC/Prepared Statement

Hi!

I am having a bit trouble with function-calls containing timestamps.

the function looks like this:

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.setLong(1, param1);
            ps.setLong(2, param2);
            ps.setLong(3, param3);
            ps.setLong(4, param4);
            ps.setTimestamp(5, param5);
            ps.setTimestamp(6, param6);
            ps.setTimestamp(7, param7);
            ps.setTimestamp(8, param8);
            ps.setLong(9, param9);
            ps.setTimestamp(10, param10);
            ps.setString(11, param11);
            ps.setLong(12, param12);
            ps.setLong(13, param13);
            ps.setDouble(14, param14);
            ps.setLong(15, param15);
            ps.setLong(16, param16);

So the set-statements are correctly typed. Now I get this Errormessage
(although the function exists and the types were correctly set):

function do_something(bigint, bigint, bigint, bigint, "unknown",
"unknown", timestamp with time zone, "unknown", bigint, timestamp with
time zone, character varying, bigint, bigint, double precision, bigint,
bigint) 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 I look at the prepared statement in the debugger I get the
following paramTypes from the preparedParameters: [20, 20, 20, 20, 0,
0, 1184, 0, 20, 1184, 1043, 20, 20, 701, 20, 20]

and the following paramValues:

[0, 18, 1, 1, 2006-02-20 22:56:10.000000 +0100, 2006-02-20
22:56:10.000000 +0100, java.lang.Object@16f25a7, 2020-01-01
01:00:00.281000 +0100, 3, java.lang.Object@16f25a7, xxx, 14, 1, -1.0,
552, 1]

Anyone have any idea how to fix this? I also tried to set Timestamps
with setObject(index, value, Types.TIMESTAMP); without any luck.

I would appreciate any hint, workaround or bugfix for this behaviour

Best regards
Manuel

On Fri, 21 Apr 2006, tarabas@tarabas.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

Hi Kris,

you were right, it seems i was assuming that the Timestamps were the
problem because of the, from my side unexpected, behaviour.

Changing the setDouble to setBigDecimal worked fine for me.

Thanks a lot for the hint.

Manuel