Re: [JDBC] Prepared statement performance...

Dmitry Tkach wrote:
> Barry Lind wrote:
> 
>>
>>
>> Dmitry Tkach wrote:
>>
>>
>>> True... But how does PreparedStatement.setTimestamp () help here?
>>> All it does (at least in 7.2) is Timestamp.toString () :-)
>>>
>>
>> Huh?  In 7.3 setTimestamp() is much more than Timestamp.toString() 
>> (and in 7.2 it was as well, I think you need to go back to 7.1 for it 
>> to be as simple as you describe).
> 
> 
> Not really... It *seems* to be doing  more in 7.2, but then here is what 
> it says right after it's done:
> 
>                        // The above works, but so does the following. 
> I'm leaving the above in, but this seems
>                        // to be identical. Pays to read the docs ;-)
>                        //set(parameterIndex,"'"+x.toString()+"'");
> 
> And this is exactly right :-)

Those were old comments that were incorrect, thus they are removed in 7.3.

> 
> I have indeed seen that timezones did not use to work correctly with 7.2 
> JDBC, but it seems that all that needed to be done to fix that was to 
> remove the df.setTimezone() call (it shifts the time to GMT, and sends 
> it to the server that way, but the backend assumes it is in the local 
> timezone, so the timestamp ends up having wrong time), or, even better, 
> just get rid of the whole formatting stuff alltogether and stick to the 
> toString () call as that comment suggets :-)

That doesn't work when the client and server are running in different 
timezones.

> Everything would work, except for that 7.1 thing of course, that 
> converts 59.999 to 60.00 :-)
> 
> Still all of the above would only work with your local timezone, and, if 
> the timestamp has some other offset, it would still be wrong. The 
> complexity of the 7.3 code
> seems to be inteded to fix that, which is a good thing, although,  the 
> way it is done seems to be way overcomplicated to me - it seems that if 
> you just did x.toGMTString () and sent it to the backend as text, it 
> would do the same conversion automatically... 

This is wrong if the datatype is timestamp with out timezone, but would 
do the correct thing (I think) for timestamp with timezone.

> BTW, the same approach could be used if you wanted to use Statement to set
> a timestamptz properly, without calling setTimestamp ():
> 
> c.createStatement ().executeQuery ("select * from foo where 
> timestamptzfield = '" + mytimestamp.toGMTString () +"');
> 
> It's true that not every database would understand GMT format (for 
> example, informix doesn't), but,
> on the other hand, not every database has the notion of timezones either 
> (informix doesn't either) - i.e., if you are using anything other than 
> the locale's default timezone in your application (which would just work 
> if you did Timestamp.toString () and sent it in as text), then you are 
> already aware that you are working with postgres, and, thus the 
> 'database abstraction' argument for using 'set*()' stuff doesn't shoot :-)
> 
> and actually, I don't even see any reason why something like:
> 
> c.createStatement ().executeQuery ("select * from foo where 
> timestamptzfiled = '" + mytimestamp + "'")
> 
> ... would not do the same thing as the example above...
> 
> To cut it short, the bottomline is - if JDBC implementation just ignored 
> the timezone stuff alltogether, and left it all to the backend, 
> everything would just work :-)
>
> I may be wrong here, but if I am, I would be very curious to see an 
> example where this would not work.
> 

See responses above.  If you have suggested improvements try them out 
and if you can get them to pass the TimestampTest.java regression I 
would like to look at them.

thanks,
--Barry