Re: CallableStatement.setTimestamp bug

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
>>>
>>