Thread: Trouble with JDBC2 ResultSet.getDate()
System is Debian "woody"
java is IBM SDK1.3
Source is CVS from March 20, 2001.
Trouble is the following, that
org.postgresql.jdbc2.ResultSet.getDate(int)
Started to generate errors
java.lang.NumberFormatException: 15 14:25:17+02
at java.lang.Integer.parseInt(Integer.java:415)
at java.lang.Integer.parseInt(Integer.java:455)
at java.sql.Date.valueOf(Date.java:97)
at org.postgresql.jdbc2.ResultSet.getDate(ResultSet.java:427)
at org.postgresql.jdbc2.ResultSet.getDate(ResultSet.java:665)
...
when fetching dates from fields of timestamp
type. It seems that the fixes provided in CVS version 1.18 from Jan 24
23:41:04 2001 of ResultSet.java regarding getDate() method broke it for
our application. Now I went back to 1.17 and copied the
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
try {
return new java.sql.Date(df.parse(s).getTime());
} catch (ParseException e) {
throw new PSQLException("postgresql.res.baddate",new
Integer(e.getErrorOffset()),s);
}
part to replace the new code:
return java.sql.Date.valueOf(s);
and it works fine but I have not had time to debug this any further. May
be it would be nice to have that part of the old code included in the 7.1
release driver?
Regards,
Juhan Ernits
Hi!
I just got bitten by this too. I use type timestamp in the
database, and often need the latest timestamp, but want to
format it as a date. With 7.0.x, I just
select ts from foo order by ts desc limit 1
and in java: d = res.getDate(1);
but this fails everywhere in my code now :(
The problem is this optimization in
src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java,
introduced in 1.21:
@@ -418,12 +418,8 @@
String s = getString(columnIndex);
if(s==null)
return null;
- SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
- try {
- return new java.sql.Date(df.parse(s).getTime());
- } catch (ParseException e) {
- throw new PSQLException("postgresql.res.baddate",new
Integer(e.getErrorOffset()),s);
- }
+
+ return java.sql.Date.valueOf(s);
}
/**
Log string is:
- Removed need for SimpleDateFormat in ResultSet.getDate()
improving performance.
I cannot find any reference to whether it is bad or not to let
getDate return a date when the string from postgres is a
timestamp or datetime. It appears to me as a good feature, and
since it has been working for quite som time, and the postgres
documenation recommends timestamp as the best time & date type,
is it really necessary to break functionality for many users
this way? The performance fix is good, but I think code shall
be added to make it backward compatible.
My query will need to be rewritten like this:
select ts::date as d, ts from foo order by ts desc limit 1
and I have hundreds of them :(
Maybe we can introduce a try-catch clause to handle the case
where the string is really a timestamp and not a pure date, but
this would give users the false impression that everything is
OK, and exceptions are really a performance hog... Maybe just
check if the string size is > 10, and then use the old code?
Agree, this would make it complicated.
/Palle
Juhan-Peep Ernits wrote:
>
> System is Debian "woody"
> java is IBM SDK1.3
> Source is CVS from March 20, 2001.
>
> Trouble is the following, that
>
> org.postgresql.jdbc2.ResultSet.getDate(int)
>
> Started to generate errors
>
> java.lang.NumberFormatException: 15 14:25:17+02
> at java.lang.Integer.parseInt(Integer.java:415)
> at java.lang.Integer.parseInt(Integer.java:455)
> at java.sql.Date.valueOf(Date.java:97)
> at org.postgresql.jdbc2.ResultSet.getDate(ResultSet.java:427)
> at org.postgresql.jdbc2.ResultSet.getDate(ResultSet.java:665)
> ...
>
> when fetching dates from fields of timestamp
> type. It seems that the fixes provided in CVS version 1.18 from Jan 24
> 23:41:04 2001 of ResultSet.java regarding getDate() method broke it for
> our application. Now I went back to 1.17 and copied the
>
> SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
>
> try {
> return new java.sql.Date(df.parse(s).getTime());
> } catch (ParseException e) {
> throw new PSQLException("postgresql.res.baddate",new
> Integer(e.getErrorOffset()),s);
> }
>
> part to replace the new code:
>
> return java.sql.Date.valueOf(s);
>
> and it works fine but I have not had time to debug this any further. May
> be it would be nice to have that part of the old code included in the 7.1
> release driver?
>
> Regards,
>
> Juhan Ernits
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
--
Partitur Informationsteknik AB
Wenner-Gren Center +46 8 566 280 02
113 46 Stockholm +46 70 785 86 02
Sweden girgen@partitur.se
Hello! We solved the problem by patching the code of the jdbc driver (since the new code has ResultSet.getStatement() method and other useful enhancements) for some time, while we changed the code to use getTimestamp() method, since it is not a good idea to stick to patching the code of the jdbc driver. Of course it would be more comfortable if one could use getDate on a timestamp field, but it is not by default always available. In our case the new approach lead to more robust code (more typesafe), and in addition there were also a few other things that resolved when we started to use the new driver, so eventually we benefitted a lot of it. It depends how much code you have to rewrite but I'd suggest doing that. Juhan Ernits On Tue, 8 May 2001, Palle Girgensohn wrote: > I just got bitten by this too. I use type timestamp in the > database, and often need the latest timestamp, but want to > format it as a date. With 7.0.x, I just > > select ts from foo order by ts desc limit 1 > > and in java: d = res.getDate(1); > > but this fails everywhere in my code now :(
So, I am concluding that the timestamp patch should not be applied the jdbc tree. > > Hello! > > We solved the problem by patching the code of the jdbc driver (since the > new code has ResultSet.getStatement() method and other useful > enhancements) for some time, while we changed the code to use > getTimestamp() method, since it is not a good idea to stick to patching > the code of the jdbc driver. > > Of course it would be more comfortable if one could use getDate on a > timestamp field, but it is not by default always available. > > In our case the new approach lead to more robust code (more typesafe), > and in addition there were also a few other things that resolved when we > started to use the new driver, so eventually we benefitted a lot of it. It > depends how much code you have to rewrite but I'd suggest doing that. > > Juhan Ernits > > > > > On Tue, 8 May 2001, Palle Girgensohn wrote: > > > I just got bitten by this too. I use type timestamp in the > > database, and often need the latest timestamp, but want to > > format it as a date. With 7.0.x, I just > > > > select ts from foo order by ts desc limit 1 > > > > and in java: d = res.getDate(1); > > > > but this fails everywhere in my code now :( > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Well, http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec/jdbc-spec.frame7.html says The ResultSet.getXXX methods will attempt to convert whatever SQL type was returned by the database to whatever Java type is returned by the getXXX method. and there is a table that clearly shows that getDate shall eat timestamp and return a proper date. Hence, I consider this to be a bug in the new code. /Palle Palle Girgensohn wrote: > > Hi! > > I just got bitten by this too. I use type timestamp in the > database, and often need the latest timestamp, but want to > format it as a date. With 7.0.x, I just > > select ts from foo order by ts desc limit 1 > > and in java: d = res.getDate(1); > > but this fails everywhere in my code now :( > > The problem is this optimization in > src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java, > introduced in 1.21: > > @@ -418,12 +418,8 @@ > String s = getString(columnIndex); > if(s==null) > return null; > - SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd"); > - try { > - return new java.sql.Date(df.parse(s).getTime()); > - } catch (ParseException e) { > - throw new PSQLException("postgresql.res.baddate",new > Integer(e.getErrorOffset()),s); > - } > + > + return java.sql.Date.valueOf(s); > } > > /** > > Log string is: > > - Removed need for SimpleDateFormat in ResultSet.getDate() > improving performance. > > I cannot find any reference to whether it is bad or not to let > getDate return a date when the string from postgres is a > timestamp or datetime. It appears to me as a good feature, and > since it has been working for quite som time, and the postgres > documenation recommends timestamp as the best time & date type, > is it really necessary to break functionality for many users > this way? The performance fix is good, but I think code shall > be added to make it backward compatible. > > My query will need to be rewritten like this: > > select ts::date as d, ts from foo order by ts desc limit 1 > > and I have hundreds of them :( > > Maybe we can introduce a try-catch clause to handle the case > where the string is really a timestamp and not a pure date, but > this would give users the false impression that everything is > OK, and exceptions are really a performance hog... Maybe just > check if the string size is > 10, and then use the old code? > Agree, this would make it complicated. > > /Palle > > Juhan-Peep Ernits wrote: > > > > System is Debian "woody" > > java is IBM SDK1.3 > > Source is CVS from March 20, 2001. > > > > Trouble is the following, that > > > > org.postgresql.jdbc2.ResultSet.getDate(int) > > > > Started to generate errors > > > > java.lang.NumberFormatException: 15 14:25:17+02 > > at java.lang.Integer.parseInt(Integer.java:415) > > at java.lang.Integer.parseInt(Integer.java:455) > > at java.sql.Date.valueOf(Date.java:97) > > at org.postgresql.jdbc2.ResultSet.getDate(ResultSet.java:427) > > at org.postgresql.jdbc2.ResultSet.getDate(ResultSet.java:665) > > ... > > > > when fetching dates from fields of timestamp > > type. It seems that the fixes provided in CVS version 1.18 from Jan 24 > > 23:41:04 2001 of ResultSet.java regarding getDate() method broke it for > > our application. Now I went back to 1.17 and copied the > > > > SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd"); > > > > try { > > return new java.sql.Date(df.parse(s).getTime()); > > } catch (ParseException e) { > > throw new PSQLException("postgresql.res.baddate",new > > Integer(e.getErrorOffset()),s); > > } > > > > part to replace the new code: > > > > return java.sql.Date.valueOf(s); > > > > and it works fine but I have not had time to debug this any further. May > > be it would be nice to have that part of the old code included in the 7.1 > > release driver? > > > > Regards, > > > > Juhan Ernits > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://www.postgresql.org/search.mpl > > -- > Partitur Informationsteknik AB > Wenner-Gren Center +46 8 566 280 02 > 113 46 Stockholm +46 70 785 86 02 > Sweden girgen@partitur.se > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Partitur Informationsteknik AB Wenner-Gren Center +46 8 566 280 02 113 46 Stockholm +46 70 785 86 02 Sweden girgen@partitur.se
Well, I didn't see the patch, but my message (probably you just
received) points to the fact that getDate shall work on a
timestamp column, and some kind of patch shall be applied. I
something like this:
if s.length() == 10
return java.sql.Date.valueOf(s)
else do the old 7.0.3 SimpleDateFormatter stuff.
*or*
try {
return java.sql.Date.valueOf(s)
} catch (SQLException e) {
do the old SimpleDateFormatter stuff...
}
sort of... gotta go, or I'd have sent you a full patch.
/Palle
Bruce Momjian wrote:
>
> So, I am concluding that the timestamp patch should not be applied the
> jdbc tree.
>
> >
> > Hello!
> >
> > We solved the problem by patching the code of the jdbc driver (since the
> > new code has ResultSet.getStatement() method and other useful
> > enhancements) for some time, while we changed the code to use
> > getTimestamp() method, since it is not a good idea to stick to patching
> > the code of the jdbc driver.
> >
> > Of course it would be more comfortable if one could use getDate on a
> > timestamp field, but it is not by default always available.
> >
> > In our case the new approach lead to more robust code (more typesafe),
> > and in addition there were also a few other things that resolved when we
> > started to use the new driver, so eventually we benefitted a lot of it. It
> > depends how much code you have to rewrite but I'd suggest doing that.
> >
> > Juhan Ernits
> >
> >
> >
> >
> > On Tue, 8 May 2001, Palle Girgensohn wrote:
> >
> > > I just got bitten by this too. I use type timestamp in the
> > > database, and often need the latest timestamp, but want to
> > > format it as a date. With 7.0.x, I just
> > >
> > > select ts from foo order by ts desc limit 1
> > >
> > > and in java: d = res.getDate(1);
> > >
> > > but this fails everywhere in my code now :(
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
--
Partitur Informationsteknik AB
Wenner-Gren Center +46 8 566 280 02
113 46 Stockholm +46 70 785 86 02
Sweden girgen@partitur.se
Is this the patch you are suggesting be applied?
> Hi!
>
> I just got bitten by this too. I use type timestamp in the
> database, and often need the latest timestamp, but want to
> format it as a date. With 7.0.x, I just
>
> select ts from foo order by ts desc limit 1
>
> and in java: d = res.getDate(1);
>
> but this fails everywhere in my code now :(
>
> The problem is this optimization in
> src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java,
> introduced in 1.21:
>
> @@ -418,12 +418,8 @@
> String s = getString(columnIndex);
> if(s==null)
> return null;
> - SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
> - try {
> - return new java.sql.Date(df.parse(s).getTime());
> - } catch (ParseException e) {
> - throw new PSQLException("postgresql.res.baddate",new
> Integer(e.getErrorOffset()),s);
> - }
> +
> + return java.sql.Date.valueOf(s);
> }
>
> /**
>
>
> Log string is:
>
> - Removed need for SimpleDateFormat in ResultSet.getDate()
> improving performance.
>
> I cannot find any reference to whether it is bad or not to let
> getDate return a date when the string from postgres is a
> timestamp or datetime. It appears to me as a good feature, and
> since it has been working for quite som time, and the postgres
> documenation recommends timestamp as the best time & date type,
> is it really necessary to break functionality for many users
> this way? The performance fix is good, but I think code shall
> be added to make it backward compatible.
>
> My query will need to be rewritten like this:
>
> select ts::date as d, ts from foo order by ts desc limit 1
>
> and I have hundreds of them :(
>
> Maybe we can introduce a try-catch clause to handle the case
> where the string is really a timestamp and not a pure date, but
> this would give users the false impression that everything is
> OK, and exceptions are really a performance hog... Maybe just
> check if the string size is > 10, and then use the old code?
> Agree, this would make it complicated.
>
> /Palle
>
>
> Juhan-Peep Ernits wrote:
> >
> > System is Debian "woody"
> > java is IBM SDK1.3
> > Source is CVS from March 20, 2001.
> >
> > Trouble is the following, that
> >
> > org.postgresql.jdbc2.ResultSet.getDate(int)
> >
> > Started to generate errors
> >
> > java.lang.NumberFormatException: 15 14:25:17+02
> > at java.lang.Integer.parseInt(Integer.java:415)
> > at java.lang.Integer.parseInt(Integer.java:455)
> > at java.sql.Date.valueOf(Date.java:97)
> > at org.postgresql.jdbc2.ResultSet.getDate(ResultSet.java:427)
> > at org.postgresql.jdbc2.ResultSet.getDate(ResultSet.java:665)
> > ...
> >
> > when fetching dates from fields of timestamp
> > type. It seems that the fixes provided in CVS version 1.18 from Jan 24
> > 23:41:04 2001 of ResultSet.java regarding getDate() method broke it for
> > our application. Now I went back to 1.17 and copied the
> >
> > SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
> >
> > try {
> > return new java.sql.Date(df.parse(s).getTime());
> > } catch (ParseException e) {
> > throw new PSQLException("postgresql.res.baddate",new
> > Integer(e.getErrorOffset()),s);
> > }
> >
> > part to replace the new code:
> >
> > return java.sql.Date.valueOf(s);
> >
> > and it works fine but I have not had time to debug this any further. May
> > be it would be nice to have that part of the old code included in the 7.1
> > release driver?
> >
> > Regards,
> >
> > Juhan Ernits
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://www.postgresql.org/search.mpl
>
> --
> Partitur Informationsteknik AB
> Wenner-Gren Center +46 8 566 280 02
> 113 46 Stockholm +46 70 785 86 02
> Sweden girgen@partitur.se
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Patch applied. Standards require it. If Peter adds casting later, this
can be removed. We are clearly seeing problem reports about this.
Thanks.
> Hi!
>
> I just got bitten by this too. I use type timestamp in the
> database, and often need the latest timestamp, but want to
> format it as a date. With 7.0.x, I just
>
> select ts from foo order by ts desc limit 1
>
> and in java: d = res.getDate(1);
>
> but this fails everywhere in my code now :(
>
> The problem is this optimization in
> src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java,
> introduced in 1.21:
>
> @@ -418,12 +418,8 @@
> String s = getString(columnIndex);
> if(s==null)
> return null;
> - SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
> - try {
> - return new java.sql.Date(df.parse(s).getTime());
> - } catch (ParseException e) {
> - throw new PSQLException("postgresql.res.baddate",new
> Integer(e.getErrorOffset()),s);
> - }
> +
> + return java.sql.Date.valueOf(s);
> }
>
> /**
>
>
> Log string is:
>
> - Removed need for SimpleDateFormat in ResultSet.getDate()
> improving performance.
>
> I cannot find any reference to whether it is bad or not to let
> getDate return a date when the string from postgres is a
> timestamp or datetime. It appears to me as a good feature, and
> since it has been working for quite som time, and the postgres
> documenation recommends timestamp as the best time & date type,
> is it really necessary to break functionality for many users
> this way? The performance fix is good, but I think code shall
> be added to make it backward compatible.
>
> My query will need to be rewritten like this:
>
> select ts::date as d, ts from foo order by ts desc limit 1
>
> and I have hundreds of them :(
>
> Maybe we can introduce a try-catch clause to handle the case
> where the string is really a timestamp and not a pure date, but
> this would give users the false impression that everything is
> OK, and exceptions are really a performance hog... Maybe just
> check if the string size is > 10, and then use the old code?
> Agree, this would make it complicated.
>
> /Palle
>
>
> Juhan-Peep Ernits wrote:
> >
> > System is Debian "woody"
> > java is IBM SDK1.3
> > Source is CVS from March 20, 2001.
> >
> > Trouble is the following, that
> >
> > org.postgresql.jdbc2.ResultSet.getDate(int)
> >
> > Started to generate errors
> >
> > java.lang.NumberFormatException: 15 14:25:17+02
> > at java.lang.Integer.parseInt(Integer.java:415)
> > at java.lang.Integer.parseInt(Integer.java:455)
> > at java.sql.Date.valueOf(Date.java:97)
> > at org.postgresql.jdbc2.ResultSet.getDate(ResultSet.java:427)
> > at org.postgresql.jdbc2.ResultSet.getDate(ResultSet.java:665)
> > ...
> >
> > when fetching dates from fields of timestamp
> > type. It seems that the fixes provided in CVS version 1.18 from Jan 24
> > 23:41:04 2001 of ResultSet.java regarding getDate() method broke it for
> > our application. Now I went back to 1.17 and copied the
> >
> > SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
> >
> > try {
> > return new java.sql.Date(df.parse(s).getTime());
> > } catch (ParseException e) {
> > throw new PSQLException("postgresql.res.baddate",new
> > Integer(e.getErrorOffset()),s);
> > }
> >
> > part to replace the new code:
> >
> > return java.sql.Date.valueOf(s);
> >
> > and it works fine but I have not had time to debug this any further. May
> > be it would be nice to have that part of the old code included in the 7.1
> > release driver?
> >
> > Regards,
> >
> > Juhan Ernits
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://www.postgresql.org/search.mpl
>
> --
> Partitur Informationsteknik AB
> Wenner-Gren Center +46 8 566 280 02
> 113 46 Stockholm +46 70 785 86 02
> Sweden girgen@partitur.se
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
--- src/interfaces/jdbc/org/postgresql/jdbc1/ResultSet.java~ Fri Feb 23 19:12:23 2001
+++ src/interfaces/jdbc/org/postgresql/jdbc1/ResultSet.java Wed May 9 04:31:11 2001
@@ -423,8 +423,13 @@
String s = getString(columnIndex);
if(s==null)
return null;
-
- return java.sql.Date.valueOf(s);
+ // length == 10: SQL Date
+ // length > 10: SQL Timestamp, assumes PGDATESTYLE=ISO
+ try {
+ return java.sql.Date.valueOf((s.length() == 10) ? s : s.substring(0,10));
+ } catch (NumberFormatException e) {
+ throw new PSQLException("postgresql.res.baddate", s);
+ }
}
/**
@@ -441,8 +446,13 @@
if(s==null)
return null; // SQL NULL
-
- return java.sql.Time.valueOf(s);
+ // length == 8: SQL Time
+ // length > 8: SQL Timestamp
+ try {
+ return java.sql.Time.valueOf((s.length() == 8) ? s : s.substring(11,19));
+ } catch (NumberFormatException e) {
+ throw new PSQLException("postgresql.res.badtime",s);
+ }
}
/**