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
