Re: What changed? - Mailing list pgsql-jdbc

Paul,

Have you tried a recent driver ?

As Mark pointed out we have some issues with timestamps because java
does not recognize two types.

Dave

On 13-Apr-07, at 2:47 PM, Paul Tomblin wrote:

> This code worked in various versions of Postgres up to and
> including 7.4
> (as bundled with CentOS 4.3), but fails on 8.1 (as bundled with CentOS
> 5.0) with an exception about being unable to determine the type of
> parameter $2:
>
>     java.sql.Timestamp startDBDate = null;
>     if (startDate != null)
>     {
>       startDBDate = new java.sql.Timestamp(startDate.getTime());
>     }
>
>     try
>     {
>       PreparedStatement stmt = con.prepareStatement(
>                 "SELECT    " +  SHOWLIST_COLUMNS +
>                 "FROM           showlists s, showlistsplaylists sc " +
>                 "WHERE          s.id = sc.showlistid AND " +
>                 "               sc.playlistid = ? AND " +
>                 "               (? IS NULL OR s.showtime >= ?) " +
>                 "ORDER BY       screen, showtime");
>       int               p = 1;
>       stmt.setLong(p, playlistID);
>       p++;
>       if (startDate == null)
>       {
>         stmt.setNull(p, java.sql.Types.TIMESTAMP);
>         p++;
>         stmt.setNull(p, java.sql.Types.TIMESTAMP);
>         p++;
>       }
>       else
>       {
>         stmt.setTimestamp(p, startDBDate);
>         p++;
>         stmt.setTimestamp(p, startDBDate);
>         p++;
>       }
>
>       ResultSet rs      = stmt.executeQuery();
>       SortedSet retList = parseShowListResultSet(con, rs,
> venueInfoList);
>
>       stmt.close();
>
> Based on something I saw in the list archives, I got it to work by
> casting
> the timestamp parameters using "?::timestamptz".  But I'm wondering
> why
> that changed, and are there any other gotchas lurking?  Should I
> cast any
> timestamp parameter to either ::timestamp or ::timestamptz
> depending on
> what it is in the db?
>
> --
> Paul Tomblin <ptomblin@xcski.com> http://blog.xcski.com/
> I think I'd like to see a Simpsons episode starting up with Bart
> Simpson
> writing 'I will not attempt to undermine the Usenet Cabal'.
>               -- J. D. Falk
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match