Thread: CallableStatement.setTimestamp bug
Hello!
It appears that CallableStatement.setTimestamp()
does not work correctly with the latest 8.1 jdbc driver -
I've listed a test case below -
sorry if this bug is already known.
Let me know if you have any questions.
Thanks for all the great work.
Reuben
--------------------
p-nut:/tmp pasquini$ javac Frick.java
p-nut:/tmp pasquini$ java -cp postgresql-8.1-407.jdbc3.jar:. Frick
Nov 14, 2006 9:59:54 PM Frick main
INFO: Caught: org.postgresql.util.PSQLException: ERROR: function
donothing("unknown") does not exist
p-nut:/tmp pasquini$ cat Frick.java
import java.util.*;
import java.util.logging.Logger;
import java.util.logging.Level;
import java.lang.reflect.*;
import java.sql.*;
/* .............
CREATE OR REPLACE FUNCTION doNothing ( TIMESTAMP )
RETURNS INTEGER AS $FUNC$
BEGIN
RETURN 0;
END; $FUNC$
LANGUAGE plpgsql;
*/
/**
* Test postgres JDBC setTimestamp functionality against doNothing()
*/
public class Frick {
public static void main ( String[] v_argv ) {
Logger log_generic = Logger.getLogger ( "littleware" );
try {
Class.forName ( "org.postgresql.Driver" );
Connection sql_conn = DriverManager.getConnection
( "jdbc:postgresql:littleware://localhost:5432", "littleware_user",
"" );
CallableStatement sql_call = sql_conn.prepareCall ( "{ ?
= call doNothing ( ? ) }" );
java.util.Date t_now = new java.util.Date ();
sql_call.registerOutParameter ( 1, Types.INTEGER );
sql_call.setTimestamp ( 2, new Timestamp ( t_now.getTime
() ) );
sql_call.execute ();
} catch ( Exception e ) {
log_generic.log ( Level.INFO, "Caught: " + e );
}
}
}
Reuben,
Didn't see this coming, problem is we have two timestamp types and
java only has one. We attempted to get around this by letting the
server do auto type casting. It appears this doesnt' work with
functions. I think it might be possible to work around this in
callable statements, but we still have to figure out which type to
send timestamptz, or timestamp
Dave
On 14-Nov-06, at 11:05 PM, Reuben Pasquini wrote:
> Hello!
>
> It appears that CallableStatement.setTimestamp()
> does not work correctly with the latest 8.1 jdbc driver -
> I've listed a test case below -
> sorry if this bug is already known.
> Let me know if you have any questions.
> Thanks for all the great work.
>
> Reuben
>
> --------------------
>
> p-nut:/tmp pasquini$ javac Frick.java
>
> p-nut:/tmp pasquini$ java -cp postgresql-8.1-407.jdbc3.jar:. Frick
> Nov 14, 2006 9:59:54 PM Frick main
> INFO: Caught: org.postgresql.util.PSQLException: ERROR: function
> donothing("unknown") does not exist
>
>
> p-nut:/tmp pasquini$ cat Frick.java
> import java.util.*;
> import java.util.logging.Logger;
> import java.util.logging.Level;
> import java.lang.reflect.*;
> import java.sql.*;
>
> /* .............
> CREATE OR REPLACE FUNCTION doNothing ( TIMESTAMP )
> RETURNS INTEGER AS $FUNC$
> BEGIN
> RETURN 0;
> END; $FUNC$
> LANGUAGE plpgsql;
> */
>
> /**
> * Test postgres JDBC setTimestamp functionality against doNothing()
> */
> public class Frick {
>
> public static void main ( String[] v_argv ) {
> Logger log_generic = Logger.getLogger ( "littleware" );
>
> try {
> Class.forName ( "org.postgresql.Driver" );
> Connection sql_conn = DriverManager.getConnection
> ( "jdbc:postgresql:littleware://localhost:5432", "littleware_user",
> "" );
> CallableStatement sql_call = sql_conn.prepareCall
> ( "{ ? = call doNothing ( ? ) }" );
> java.util.Date t_now = new java.util.Date ();
> sql_call.registerOutParameter ( 1, Types.INTEGER );
> sql_call.setTimestamp ( 2, new Timestamp
> ( t_now.getTime () ) );
> sql_call.execute ();
> } catch ( Exception e ) {
> log_generic.log ( Level.INFO, "Caught: " + e );
> }
> }
> }
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
You don't happen to have another function called donothing which takes a
single parameter of a different type, do you?
-- Mark
On Tue, 2006-11-14 at 22:05 -0600, Reuben Pasquini wrote:
> Hello!
>
> It appears that CallableStatement.setTimestamp()
> does not work correctly with the latest 8.1 jdbc driver -
> I've listed a test case below -
> sorry if this bug is already known.
> Let me know if you have any questions.
> Thanks for all the great work.
>
> Reuben
>
> --------------------
>
> p-nut:/tmp pasquini$ javac Frick.java
>
> p-nut:/tmp pasquini$ java -cp postgresql-8.1-407.jdbc3.jar:. Frick
> Nov 14, 2006 9:59:54 PM Frick main
> INFO: Caught: org.postgresql.util.PSQLException: ERROR: function
> donothing("unknown") does not exist
>
>
> p-nut:/tmp pasquini$ cat Frick.java
> import java.util.*;
> import java.util.logging.Logger;
> import java.util.logging.Level;
> import java.lang.reflect.*;
> import java.sql.*;
>
> /* .............
> CREATE OR REPLACE FUNCTION doNothing ( TIMESTAMP )
> RETURNS INTEGER AS $FUNC$
> BEGIN
> RETURN 0;
> END; $FUNC$
> LANGUAGE plpgsql;
> */
>
> /**
> * Test postgres JDBC setTimestamp functionality against doNothing()
> */
> public class Frick {
>
> public static void main ( String[] v_argv ) {
> Logger log_generic = Logger.getLogger ( "littleware" );
>
> try {
> Class.forName ( "org.postgresql.Driver" );
> Connection sql_conn = DriverManager.getConnection
> ( "jdbc:postgresql:littleware://localhost:5432", "littleware_user",
> "" );
> CallableStatement sql_call = sql_conn.prepareCall ( "{ ?
> = call doNothing ( ? ) }" );
> java.util.Date t_now = new java.util.Date ();
> sql_call.registerOutParameter ( 1, Types.INTEGER );
> sql_call.setTimestamp ( 2, new Timestamp ( t_now.getTime
> () ) );
> sql_call.execute ();
> } catch ( Exception e ) {
> log_generic.log ( Level.INFO, "Caught: " + e );
> }
> }
> }
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
Mark,
It is a known "feature/bug". The driver is doing this on purpose.
see my previous email
Dave
On 15-Nov-06, at 9:47 AM, Mark Lewis wrote:
> You don't happen to have another function called donothing which
> takes a
> single parameter of a different type, do you?
>
> -- Mark
>
> On Tue, 2006-11-14 at 22:05 -0600, Reuben Pasquini wrote:
>> Hello!
>>
>> It appears that CallableStatement.setTimestamp()
>> does not work correctly with the latest 8.1 jdbc driver -
>> I've listed a test case below -
>> sorry if this bug is already known.
>> Let me know if you have any questions.
>> Thanks for all the great work.
>>
>> Reuben
>>
>> --------------------
>>
>> p-nut:/tmp pasquini$ javac Frick.java
>>
>> p-nut:/tmp pasquini$ java -cp postgresql-8.1-407.jdbc3.jar:. Frick
>> Nov 14, 2006 9:59:54 PM Frick main
>> INFO: Caught: org.postgresql.util.PSQLException: ERROR: function
>> donothing("unknown") does not exist
>>
>>
>> p-nut:/tmp pasquini$ cat Frick.java
>> import java.util.*;
>> import java.util.logging.Logger;
>> import java.util.logging.Level;
>> import java.lang.reflect.*;
>> import java.sql.*;
>>
>> /* .............
>> CREATE OR REPLACE FUNCTION doNothing ( TIMESTAMP )
>> RETURNS INTEGER AS $FUNC$
>> BEGIN
>> RETURN 0;
>> END; $FUNC$
>> LANGUAGE plpgsql;
>> */
>>
>> /**
>> * Test postgres JDBC setTimestamp functionality against doNothing()
>> */
>> public class Frick {
>>
>> public static void main ( String[] v_argv ) {
>> Logger log_generic = Logger.getLogger ( "littleware" );
>>
>> try {
>> Class.forName ( "org.postgresql.Driver" );
>> Connection sql_conn = DriverManager.getConnection
>> ( "jdbc:postgresql:littleware://localhost:5432", "littleware_user",
>> "" );
>> CallableStatement sql_call = sql_conn.prepareCall ( "{ ?
>> = call doNothing ( ? ) }" );
>> java.util.Date t_now = new java.util.Date ();
>> sql_call.registerOutParameter ( 1, Types.INTEGER );
>> sql_call.setTimestamp ( 2, new Timestamp ( t_now.getTime
>> () ) );
>> sql_call.execute ();
>> } catch ( Exception e ) {
>> log_generic.log ( Level.INFO, "Caught: " + e );
>> }
>> }
>> }
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
I know that the driver is sending unknown as the timestamp type on
purpose, but I thought that the back-end was smart enough to associate
an unknown-type function parameter with the correct signature. I
haven't tested that, but it seems like it should work.
But it is known not to work if there is another function with the same
name and number of parameters, because the back-end has no means of
determining which of the functions should be called.
-- Mark
On Wed, 2006-11-15 at 11:08 -0500, Dave Cramer wrote:
> Mark,
>
> It is a known "feature/bug". The driver is doing this on purpose.
>
> see my previous email
>
> Dave
> On 15-Nov-06, at 9:47 AM, Mark Lewis wrote:
>
> > You don't happen to have another function called donothing which
> > takes a
> > single parameter of a different type, do you?
> >
> > -- Mark
> >
> > On Tue, 2006-11-14 at 22:05 -0600, Reuben Pasquini wrote:
> >> Hello!
> >>
> >> It appears that CallableStatement.setTimestamp()
> >> does not work correctly with the latest 8.1 jdbc driver -
> >> I've listed a test case below -
> >> sorry if this bug is already known.
> >> Let me know if you have any questions.
> >> Thanks for all the great work.
> >>
> >> Reuben
> >>
> >> --------------------
> >>
> >> p-nut:/tmp pasquini$ javac Frick.java
> >>
> >> p-nut:/tmp pasquini$ java -cp postgresql-8.1-407.jdbc3.jar:. Frick
> >> Nov 14, 2006 9:59:54 PM Frick main
> >> INFO: Caught: org.postgresql.util.PSQLException: ERROR: function
> >> donothing("unknown") does not exist
> >>
> >>
> >> p-nut:/tmp pasquini$ cat Frick.java
> >> import java.util.*;
> >> import java.util.logging.Logger;
> >> import java.util.logging.Level;
> >> import java.lang.reflect.*;
> >> import java.sql.*;
> >>
> >> /* .............
> >> CREATE OR REPLACE FUNCTION doNothing ( TIMESTAMP )
> >> RETURNS INTEGER AS $FUNC$
> >> BEGIN
> >> RETURN 0;
> >> END; $FUNC$
> >> LANGUAGE plpgsql;
> >> */
> >>
> >> /**
> >> * Test postgres JDBC setTimestamp functionality against doNothing()
> >> */
> >> public class Frick {
> >>
> >> public static void main ( String[] v_argv ) {
> >> Logger log_generic = Logger.getLogger ( "littleware" );
> >>
> >> try {
> >> Class.forName ( "org.postgresql.Driver" );
> >> Connection sql_conn = DriverManager.getConnection
> >> ( "jdbc:postgresql:littleware://localhost:5432", "littleware_user",
> >> "" );
> >> CallableStatement sql_call = sql_conn.prepareCall ( "{ ?
> >> = call doNothing ( ? ) }" );
> >> java.util.Date t_now = new java.util.Date ();
> >> sql_call.registerOutParameter ( 1, Types.INTEGER );
> >> sql_call.setTimestamp ( 2, new Timestamp ( t_now.getTime
> >> () ) );
> >> sql_call.execute ();
> >> } catch ( Exception e ) {
> >> log_generic.log ( Level.INFO, "Caught: " + e );
> >> }
> >> }
> >> }
> >>
> >>
> >> ---------------------------(end of
> >> broadcast)---------------------------
> >> TIP 6: explain analyze is your friend
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
>
Hi Mark and Dave,
I only have the one 'doNothing' function in my database - exactly one
'doNothing'
function that I created specifically to setup the test case.
Give it a try - it only takes a minute to test.
I originally ran into the problem with a different pgsql function
after migrating an application
from an old (v2 protocol) jdbc driver
when calling a function that takes several
parameters - including 2 TIMESTAMP type parameters.
I managed to work around the problem in my application by
changing the function to take VARCHAR instead of TIMESTAMP
arguments, and converting the VARCHAR parameters to TIMESTAMP
within the pgsqlisn't the best
way to do the conversion ?) :
CREATE OR REPLACE FUNCTION littleware.stringToTimestamp ( VARCHAR )
RETURNS TIMESTAMP AS $FUNC$
DECLARE
s_param_timestring ALIAS FOR $1;
BEGIN
RETURN s_param_timestring;
END;
$FUNC$
LANGUAGE plpgsql;
Anyway - hope that information doesn't confuse the issue.
I've managed to work around the problem in my app,
but you can verify whether this CallableStatement.setTimestamp()
thing is a real bug with the test case below.
Thanks for looking into it - I've had great experience with
postgres jdbc.
Reuben
On Nov 15, 2006, at 10:27 AM, Mark Lewis wrote:
> I know that the driver is sending unknown as the timestamp type on
> purpose, but I thought that the back-end was smart enough to associate
> an unknown-type function parameter with the correct signature. I
> haven't tested that, but it seems like it should work.
>
> But it is known not to work if there is another function with the same
> name and number of parameters, because the back-end has no means of
> determining which of the functions should be called.
>
> -- Mark
>
> On Wed, 2006-11-15 at 11:08 -0500, Dave Cramer wrote:
>> Mark,
>>
>> It is a known "feature/bug". The driver is doing this on purpose.
>>
>> see my previous email
>>
>> Dave
>> On 15-Nov-06, at 9:47 AM, Mark Lewis wrote:
>>
>>> You don't happen to have another function called donothing which
>>> takes a
>>> single parameter of a different type, do you?
>>>
>>> -- Mark
>>>
>>> On Tue, 2006-11-14 at 22:05 -0600, Reuben Pasquini wrote:
>>>> Hello!
>>>>
>>>> It appears that CallableStatement.setTimestamp()
>>>> does not work correctly with the latest 8.1 jdbc driver -
>>>> I've listed a test case below -
>>>> sorry if this bug is already known.
>>>> Let me know if you have any questions.
>>>> Thanks for all the great work.
>>>>
>>>> Reuben
>>>>
>>>> --------------------
>>>>
>>>> p-nut:/tmp pasquini$ javac Frick.java
>>>>
>>>> p-nut:/tmp pasquini$ java -cp postgresql-8.1-407.jdbc3.jar:. Frick
>>>> Nov 14, 2006 9:59:54 PM Frick main
>>>> INFO: Caught: org.postgresql.util.PSQLException: ERROR: function
>>>> donothing("unknown") does not exist
>>>>
>>>>
>>>> p-nut:/tmp pasquini$ cat Frick.java
>>>> import java.util.*;
>>>> import java.util.logging.Logger;
>>>> import java.util.logging.Level;
>>>> import java.lang.reflect.*;
>>>> import java.sql.*;
>>>>
>>>> /* .............
>>>> CREATE OR REPLACE FUNCTION doNothing ( TIMESTAMP )
>>>> RETURNS INTEGER AS $FUNC$
>>>> BEGIN
>>>> RETURN 0;
>>>> END; $FUNC$
>>>> LANGUAGE plpgsql;
>>>> */
>>>>
>>>> /**
>>>> * Test postgres JDBC setTimestamp functionality against doNothing()
>>>> */
>>>> public class Frick {
>>>>
>>>> public static void main ( String[] v_argv ) {
>>>> Logger log_generic = Logger.getLogger ( "littleware" );
>>>>
>>>> try {
>>>> Class.forName ( "org.postgresql.Driver" );
>>>> Connection sql_conn = DriverManager.getConnection
>>>> ( "jdbc:postgresql:littleware://localhost:5432", "littleware_user",
>>>> "" );
>>>> CallableStatement sql_call = sql_conn.prepareCall
>>>> ( "{ ?
>>>> = call doNothing ( ? ) }" );
>>>> java.util.Date t_now = new java.util.Date ();
>>>> sql_call.registerOutParameter ( 1, Types.INTEGER );
>>>> sql_call.setTimestamp ( 2, new Timestamp
>>>> ( t_now.getTime
>>>> () ) );
>>>> sql_call.execute ();
>>>> } catch ( Exception e ) {
>>>> log_generic.log ( Level.INFO, "Caught: " + e );
>>>> }
>>>> }
>>>> }
>>>>
>>>>
>>>> ---------------------------(end of
>>>> broadcast)---------------------------
>>>> TIP 6: explain analyze is your friend
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 5: don't forget to increase your free space map settings
>>>
>>