Lists: | pgsql-jdbc |
---|
From: | tarabas(at)tarabas(dot)de |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Problems with Timestamp and Function Calls in JDBC/Prepared Statement |
Date: | 2006-04-21 16:52:00 |
Message-ID: | 1145638320.890274.191530@z34g2000cwc.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
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(dot)lang(dot)Object(at)16f25a7, 2020-01-01
01:00:00.281000 +0100, 3, java(dot)lang(dot)Object(at)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
From: | Kris Jurka <books(at)ejurka(dot)com> |
---|---|
To: | tarabas(at)tarabas(dot)de |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Problems with Timestamp and Function Calls in JDBC/Prepared |
Date: | 2006-04-21 19:58:46 |
Message-ID: | Pine.BSO.4.63.0604211454001.1040@leary2.csoft.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
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
From: | tarabas(at)tarabas(dot)de |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Problems with Timestamp and Function Calls in JDBC/Prepared |
Date: | 2006-04-21 20:16:46 |
Message-ID: | 1145650606.250770.165600@i39g2000cwa.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
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