Thread: What changed?
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
This is addressed in the FAQ, look at the section titled, "I upgraded from 7.x to 8.x. Why did my application break?" http://jdbc.postgresql.org/documentation/faq.html#upgradeTo80 There's also plenty of discussion in the archives that you'll find. -- Mark Lewis On Fri, 2007-04-13 at 14:47 -0400, 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? >
Quoting Mark Lewis (mark.lewis@mir3.com): > This is addressed in the FAQ, look at the section titled, "I upgraded > from 7.x to 8.x. Why did my application break?" > > http://jdbc.postgresql.org/documentation/faq.html#upgradeTo80 Except neither of the two categories documented there apply to me. The problem I'm having is that I'm calling either stmt.setNull(p, java.sql.Types.TIMESTAMP); or stmt.setTimestamp(p, startDBDate); and it's staying that it can't tell the type, in spite of the fact that I do specify it in both cases. -- Paul Tomblin <ptomblin@xcski.com> http://blog.xcski.com/ "We sealed our federal pact without bloodshed and without exploitation of the weak by the strong. All it took was fairness, justice and some compromises on both sides." - George-Etienne Cartier.
On Fri, 2007-04-13 at 15:33 -0400, Paul Tomblin wrote: > Quoting Mark Lewis (mark.lewis@mir3.com): > > This is addressed in the FAQ, look at the section titled, "I upgraded > > from 7.x to 8.x. Why did my application break?" > > > > http://jdbc.postgresql.org/documentation/faq.html#upgradeTo80 > > Except neither of the two categories documented there apply to me. The > problem I'm having is that I'm calling either > stmt.setNull(p, java.sql.Types.TIMESTAMP); > or > stmt.setTimestamp(p, startDBDate); > and it's staying that it can't tell the type, in spite of the fact that I > do specify it in both cases. Sorry, guess I was reading a little too quickly the first time around :) More recent versions of the driver send UNSPECIFIED for timestamps (except in the setNull case where they always send TIMESTAMPTZ, but I don't think that matters), which allows the server to "do the right thing" most of the time. I don't have time to really dig into it right now but I'm guessing that you're hitting a case where it fails to do the right thing. -- Mark
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
Quoting Dave Cramer (pg@fastcrypt.com): > Paul, > > Have you tried a recent driver ? > > As Mark pointed out we have some issues with timestamps because java > does not recognize two types. I'm constrained to keep the version that's in CentOS 5.0 unless I can make a business case for upgrading. But in this case I can work around it by casting all timestamp and timestamptz parameters. I'm just worried about what other pitfalls I might fall into. -- Paul Tomblin <ptomblin@xcski.com> http://blog.xcski.com/ Reliability went through the floor, tunnelled its way to the centre of the Earth, and perished in the magma. -- Saundo