Failure in timestamptz of JDBC of 7.2b4
I found a failure in a JDBC driver of 7.2b4.
(1) It does not support timestamptz type
(2) Exception occurs by timestamp without time zone type
I attach a patch correcting the first failure.
You can confirm it in example.psql as follows:
---------------------------------------------------------------------
$ java -cp postgresql-examples.jar:postgresql.jar example.psql jdbc:postgresql:r-matuda r-matuda pass
PostgreSQL psql example v6.3 rev 1
Connecting to Database URL = jdbc:postgresql:r-matuda
Connected to PostgreSQL 7.2b4
[1]: [1] select 'now'::timestamp without time zone; timestamp Exception caught. java.lang.StringIndexOutOfBoundsException: String index out of range: 26 java.lang.StringIndexOutOfBoundsException: String index out of range: 26 at java.lang.String.charAt(String.java:516) at org.postgresql.jdbc2.ResultSet.toTimestamp(ResultSet.java:1653) at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:398) at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:768) at example.psql.displayResult(psql.java:137) at example.psql.processLine(psql.java:96) at example.psql.<init>(psql.java:62) at example.psql.main(psql.java:227)
timestamptz
No class found for timestamptz.
[1]: [1] select 'now'::timestamp without time zone; timestamp Exception caught. java.lang.StringIndexOutOfBoundsException: String index out of range: 26 java.lang.StringIndexOutOfBoundsException: String index out of range: 26 at java.lang.String.charAt(String.java:516) at org.postgresql.jdbc2.ResultSet.toTimestamp(ResultSet.java:1653) at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:398) at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:768) at example.psql.displayResult(psql.java:137) at example.psql.processLine(psql.java:96) at example.psql.<init>(psql.java:62) at example.psql.main(psql.java:227)
timestamptz
No class found for timestamptz.
[1]: [1] select 'now'::timestamp without time zone; timestamp Exception caught. java.lang.StringIndexOutOfBoundsException: String index out of range: 26 java.lang.StringIndexOutOfBoundsException: String index out of range: 26 at java.lang.String.charAt(String.java:516) at org.postgresql.jdbc2.ResultSet.toTimestamp(ResultSet.java:1653) at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:398) at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:768) at example.psql.displayResult(psql.java:137) at example.psql.processLine(psql.java:96) at example.psql.<init>(psql.java:62) at example.psql.main(psql.java:227)
timestamp
Exception caught.
java.lang.StringIndexOutOfBoundsException: String index out of range: 26
java.lang.StringIndexOutOfBoundsException: String index out of range: 26
at java.lang.String.charAt(String.java:516)
at org.postgresql.jdbc2.ResultSet.toTimestamp(ResultSet.java:1653)
at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:398)
at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:768)
at example.psql.displayResult(psql.java:137)
at example.psql.processLine(psql.java:96)
at example.psql.<init>(psql.java:62)
at example.psql.main(psql.java:227)
Attachments:
Connection.java.diffapplication/octet-stream; name=Connection.java.diffDownload
*** Connection.java Tue Dec 25 16:29:55 2001
--- Connection.java_7.2b4 Tue Dec 25 16:22:08 2001
***************
*** 303,309 ****
"bool",
"date",
"time",
! "abstime", "timestamp", "timestamptz",
"_bool", "_char", "_int2", "_int4", "_text",
"_oid", "_varchar", "_int8", "_float4", "_float8",
"_abstime", "_date", "_time", "_timestamp", "_numeric",
--- 303,309 ----
"bool",
"date",
"time",
! "abstime", "timestamp",
"_bool", "_char", "_int2", "_int4", "_text",
"_oid", "_varchar", "_int8", "_float4", "_float8",
"_abstime", "_date", "_time", "_timestamp", "_numeric",
***************
*** 331,337 ****
Types.BIT,
Types.DATE,
Types.TIME,
! Types.TIMESTAMP, Types.TIMESTAMP, Types.TIMESTAMP,
Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY,
Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY,
Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY,
--- 331,337 ----
Types.BIT,
Types.DATE,
Types.TIME,
! Types.TIMESTAMP, Types.TIMESTAMP,
Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY,
Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY,
Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY,
Ryouichi,
I did not follow your explaination of the problem and therefore I don't
see how your suggested patch fixes the problem.
You claim that the jdbc driver does not support the timestamptz type.
However when I try using this type, I don't have any problems. I just
executed the sql statements you had included in your email:
select 'now'::timestamp;
select 'now'::timestamptz;
select 'now'::timestamp with time zone;
These all seem to work correctly for me.
Then however I did try your last query:
select 'now'::timestamp without time zone;
and this does fail for me with the string index out of bounds exception.
However the patch you sent does not seem to fix this error. And I
really don't know what to do with this datatype, since jdbc does not
have a corresponding datatype that doesn't contain timezone information.
So to summarize, after looking at the sql statements you mention in your
email, they all seem to work correctly for me without your patch being
applied, except for the last one, which still fails even with your patch
applied. So I am unsure what your patch is supposed to fix, since I do
not see any evidence that it fixes anything that is broken.
thanks,
--Barry
Ryouichi Matsuda wrote:
Show quoted text
I found a failure in a JDBC driver of 7.2b4.
(1) It does not support timestamptz type
(2) Exception occurs by timestamp without time zone type
I attach a patch correcting the first failure.
You can confirm it in example.psql as follows:---------------------------------------------------------------------
$ java -cp postgresql-examples.jar:postgresql.jar example.psql jdbc:postgresql:r-matuda r-matuda pass
PostgreSQL psql example v6.3 rev 1Connecting to Database URL = jdbc:postgresql:r-matuda
Connected to PostgreSQL 7.2b4[1] select 'now'::timestamp;
timestamptz
No class found for timestamptz.
[1] [1] select 'now'::timestamp with time zone;
timestamptz
No class found for timestamptz.
[1] [1] select 'now'::timestamp without time zone;
timestamp
Exception caught.
java.lang.StringIndexOutOfBoundsException: String index out of range: 26
java.lang.StringIndexOutOfBoundsException: String index out of range: 26
at java.lang.String.charAt(String.java:516)
at org.postgresql.jdbc2.ResultSet.toTimestamp(ResultSet.java:1653)
at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:398)
at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:768)
at example.psql.displayResult(psql.java:137)
at example.psql.processLine(psql.java:96)
at example.psql.<init>(psql.java:62)
at example.psql.main(psql.java:227)------------------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Barry Lind wrote:
select 'now'::timestamp;
select 'now'::timestamptz;
select 'now'::timestamp with time zone;These all seem to work correctly for me.
When I executed SQL in <<example.psql>>, exception occurred.
In an approach by a getTimestamp() method, exception does not occur.
Statement st = db.createStatement();
ResultSet rs = st.executeQuery("SELECT 'now'::timestamp");
rs.next();
Timestamp ts = rs.getTimestamp(1);
System.out.println(ts);
But, in an approach by a getObject() method, exception occurs.
Statement st = db.createStatement();
ResultSet rs = st.executeQuery("SELECT 'now'::timestamp");
rs.next();
Timestamp ts = (Timestamp)rs.getObject(1);
System.out.println(ts);
Because a displayResult() method of 'example/psql.java' uses
getObject(), exception of 'No class found for timestamptz' occurs.
The patch which I attached to a former mail corrects this error.
Then however I did try your last query:
select 'now'::timestamp without time zone;
and this does fail for me with the string index out of bounds exception.
However the patch you sent does not seem to fix this error. And I
really don't know what to do with this datatype, since jdbc does not
have a corresponding datatype that doesn't contain timezone information.
My patch does not correct this error. It is difficult for me to
correct this error justly, but I try to think.
In addition, I found an error of time type.
Statement st = db.createStatement();
ResultSet rs = st.executeQuery("SELECT 'now'::time");
rs.next();
Time t = rs.getTime(1);
System.out.println(t);
This becomes string index out of bounds exception.
Ryouichi,
Thanks for the additional information. I will look at this when I get
some free time.
thanks,
--Barry
Ryouichi Matsuda wrote:
Show quoted text
Barry Lind wrote:
select 'now'::timestamp;
select 'now'::timestamptz;
select 'now'::timestamp with time zone;These all seem to work correctly for me.
When I executed SQL in <<example.psql>>, exception occurred.
In an approach by a getTimestamp() method, exception does not occur.Statement st = db.createStatement();
ResultSet rs = st.executeQuery("SELECT 'now'::timestamp");
rs.next();
Timestamp ts = rs.getTimestamp(1);
System.out.println(ts);But, in an approach by a getObject() method, exception occurs.
Statement st = db.createStatement();
ResultSet rs = st.executeQuery("SELECT 'now'::timestamp");
rs.next();
Timestamp ts = (Timestamp)rs.getObject(1);
System.out.println(ts);Because a displayResult() method of 'example/psql.java' uses
getObject(), exception of 'No class found for timestamptz' occurs.
The patch which I attached to a former mail corrects this error.Then however I did try your last query:
select 'now'::timestamp without time zone;
and this does fail for me with the string index out of bounds exception.
However the patch you sent does not seem to fix this error. And I
really don't know what to do with this datatype, since jdbc does not
have a corresponding datatype that doesn't contain timezone information.My patch does not correct this error. It is difficult for me to
correct this error justly, but I try to think.In addition, I found an error of time type.
Statement st = db.createStatement();
ResultSet rs = st.executeQuery("SELECT 'now'::time");
rs.next();
Time t = rs.getTime(1);
System.out.println(t);This becomes string index out of bounds exception.
Ryouichi,
I have applied the patch for this bug. The fix won't be in 7.2b5, but
will be in 7.2RC1. It will also be in the latest builds on the
jdbc.postgresql.org website.
thanks,
--Barry
Ryouichi Matsuda wrote:
Show quoted text
Barry Lind wrote:
select 'now'::timestamp;
select 'now'::timestamptz;
select 'now'::timestamp with time zone;These all seem to work correctly for me.
When I executed SQL in <<example.psql>>, exception occurred.
In an approach by a getTimestamp() method, exception does not occur.Statement st = db.createStatement();
ResultSet rs = st.executeQuery("SELECT 'now'::timestamp");
rs.next();
Timestamp ts = rs.getTimestamp(1);
System.out.println(ts);But, in an approach by a getObject() method, exception occurs.
Statement st = db.createStatement();
ResultSet rs = st.executeQuery("SELECT 'now'::timestamp");
rs.next();
Timestamp ts = (Timestamp)rs.getObject(1);
System.out.println(ts);Because a displayResult() method of 'example/psql.java' uses
getObject(), exception of 'No class found for timestamptz' occurs.
The patch which I attached to a former mail corrects this error.Then however I did try your last query:
select 'now'::timestamp without time zone;
and this does fail for me with the string index out of bounds exception.
However the patch you sent does not seem to fix this error. And I
really don't know what to do with this datatype, since jdbc does not
have a corresponding datatype that doesn't contain timezone information.My patch does not correct this error. It is difficult for me to
correct this error justly, but I try to think.In addition, I found an error of time type.
Statement st = db.createStatement();
ResultSet rs = st.executeQuery("SELECT 'now'::time");
rs.next();
Time t = rs.getTime(1);
System.out.println(t);This becomes string index out of bounds exception.
I wrote:
In addition, I found an error of time type.
Statement st = db.createStatement();
ResultSet rs = st.executeQuery("SELECT 'now'::time");
rs.next();
Time t = rs.getTime(1);
System.out.println(t);This becomes string index out of bounds exception.
An attached patch corrects this error.
But time is always local time zone.
In this patch, time zone does not look.
Attachments:
jdbc1.ResultSet.java.diffapplication/octet-stream; name=jdbc1.ResultSet.java.diffDownload
*** org/postgresql/jdbc1/ResultSet.java_7.2b4 Tue Dec 11 13:48:05 2001
--- org/postgresql/jdbc1/ResultSet.java Tue Jan 15 18:50:42 2002
***************
*** 469,491 ****
{
String s = getString(columnIndex);
! if (s != null)
{
! try
! {
! if (s.length() != 5 && s.length() != 8)
! throw new NumberFormatException("Wrong Length!");
! int hr = Integer.parseInt(s.substring(0, 2));
! int min = Integer.parseInt(s.substring(3, 5));
! int sec = (s.length() == 5) ? 0 : Integer.parseInt(s.substring(6));
! return new Time(hr, min, sec);
! }
! catch (NumberFormatException e)
! {
! throw new PSQLException ("postgresql.res.badtime", s);
! }
}
- return null; // SQL NULL
}
/*
--- 469,499 ----
{
String s = getString(columnIndex);
! if (s == null)
! return null; // SQL NULL
!
! // 2002-01-15 17:22:41.863506+09 timestamp with time zone
! // 2002-01-15 17:22:41+09 timestamp(0) with time zone
! // 2002-01-15 17:25:11.451415 timestamp without time zone
! // 2002-01-15 17:25:11 timestamp(0) without time zone
! // 17:25:39.731669 time without time zone
! // 17:25:39 time(0) without time zone
! // 17:26:27.537975+09 time with time zone
! // 17:26:27+09 time(0) with time zone
!
! String hhmmss;
! if (19 <= s.length() && s.charAt(10) == ' ')
! hhmmss = s.substring(11, 19);
! else
! hhmmss = s.substring(0, 8);
! try
! {
! return Time.valueOf(hhmmss);
! }
! catch (NumberFormatException e)
{
! throw new PSQLException("postgresql.res.badtime", s);
}
}
/*
jdbc2.ResultSet.java.diffapplication/octet-stream; name=jdbc2.ResultSet.java.diffDownload
*** org/postgresql/jdbc2/ResultSet.java_7.2b4 Tue Dec 11 13:48:05 2001
--- org/postgresql/jdbc2/ResultSet.java Tue Jan 15 19:15:05 2002
***************
*** 1579,1589 ****
{
if (s == null)
return null; // SQL NULL
! // 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)
{
--- 1579,1602 ----
{
if (s == null)
return null; // SQL NULL
!
! // 2002-01-15 17:22:41.863506+09 timestamp with time zone
! // 2002-01-15 17:22:41+09 timestamp(0) with time zone
! // 2002-01-15 17:25:11.451415 timestamp without time zone
! // 2002-01-15 17:25:11 timestamp(0) without time zone
! // 17:25:39.731669 time without time zone
! // 17:25:39 time(0) without time zone
! // 17:26:27.537975+09 time with time zone
! // 17:26:27+09 time(0) with time zone
!
! String hhmmss;
! if (19 <= s.length() && s.charAt(10) == ' ')
! hhmmss = s.substring(11, 19);
! else
! hhmmss = s.substring(0, 8);
try
{
! return Time.valueOf(hhmmss);
}
catch (NumberFormatException e)
{
Barry Lind wrote:
Then however I did try your last query:
select 'now'::timestamp without time zone;
and this does fail for me with the string index out of bounds exception.
An attached patch corrects problem of this bug and fractional second.
The handling of time zone was as follows:
(a) with time zone
using SimpleDateFormat("yyyy-MM-dd HH:mm:ss z")
(b) without time zone
using SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
About problem of fractional second,
Fractional second was changed from milli-second to nano-second.
Attachments:
jdbc1.ResultSet.java.diffapplication/octet-stream; name=jdbc1.ResultSet.java.diffDownload
*** org/postgresql/jdbc1/ResultSet.java_7.2b4 Tue Dec 11 13:48:05 2001
--- org/postgresql/jdbc1/ResultSet.java Thu Jan 17 19:41:39 2002
***************
*** 493,504 ****
* java.sql.Timestamp object
*
* The driver is set to return ISO date formated strings. We modify this
! * string from the ISO format to a format that Java can understand. Java
! * expects timezone info as 'GMT+09:00' where as ISO gives '+09'.
! * Java also expects fractional seconds to 3 places where postgres
! * will give, none, 2 or 6 depending on the time and postgres version.
! * From version 7.2 postgres returns fractional seconds to 6 places.
! * If available, we drop the last 3 digits.
*
* @param columnIndex the first column is 1, the second is 2...
* @return the column value; null if SQL NULL
--- 493,504 ----
* java.sql.Timestamp object
*
* The driver is set to return ISO date formated strings. We modify this
! * The driver is set to return ISO date formated strings. We modify this
! * string from the ISO format to a format that Java can understand. Java
! * expects timezone info as 'GMT+09:00' where as ISO gives '+09'.
! * Java also expects fractional seconds to 9 places where postgres
! * will give, none, 2 or 6 depending on the time and postgres version.
! * From version 7.2 postgres returns fractional seconds to 6 places.
*
* @param columnIndex the first column is 1, the second is 2...
* @return the column value; null if SQL NULL
***************
*** 509,586 ****
String s = getString(columnIndex);
if (s == null)
! return null;
! StringBuffer sbuf = new StringBuffer(s);
SimpleDateFormat df = null;
! if (s.length() > 19)
! {
! // The len of the ISO string to the second value is 19 chars. If
! // greater then 19, there should be tz info and perhaps fractional
! // second info which we need to change to java to read it.
!
! // cut the copy to second value "2001-12-07 16:29:22"
! int i = 19;
! sbuf.setLength(i);
!
! char c = s.charAt(i++);
! if (c == '.')
! {
! // Found a fractional value. Append up to 3 digits including
! // the leading '.'
! do
! {
! if (i < 24)
! sbuf.append(c);
! c = s.charAt(i++);
! } while (Character.isDigit(c));
!
! // If there wasn't at least 3 digits we should add some zeros
! // to make up the 3 digits we tell java to expect.
! for (int j = i; j < 24; j++)
! sbuf.append('0');
! }
! else
! {
! // No fractional seconds, lets add some.
! sbuf.append(".000");
}
! // prepend the GMT part and then add the remaining bit of
! // the string.
! sbuf.append(" GMT");
! sbuf.append(c);
! sbuf.append(s.substring(i, s.length()));
!
! // Lastly, if the tz part doesn't specify the :MM part then
! // we add ":00" for java.
! if (s.length() - i < 5)
! sbuf.append(":00");
! // we'll use this dateformat string to parse the result.
! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS z");
! }
! else if (s.length() == 19)
! {
! // No tz or fractional second info.
! // I'm not sure if it is
! // possible to have a string in this format, as pg
! // should give us tz qualified timestamps back, but it was
! // in the old code, so I'm handling it for now.
! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
! }
! else
! {
! // We must just have a date. This case is
! // needed if this method is called on a date column
! df = new SimpleDateFormat("yyyy-MM-dd");
}
try
{
// All that's left is to parse the string and return the ts.
! return new Timestamp(df.parse(sbuf.toString()).getTime());
}
catch (ParseException e)
{
--- 509,587 ----
String s = getString(columnIndex);
if (s == null)
! return null; // SQL NULL
! // 2002-01-15 17:22:41.863506+09 timestamp with time zone
! // 2002-01-15 17:25:11.451415 timestamp without time zone
! // 2002-01-15 17:22:41+09 timestamp(0) with time zone
! // 2002-01-15 17:25:11 timestamp(0) without time zone
! // 2002-01-15 date
!
! final int TIMESTAMP_LENGTH = 19; // "2002-01-15 17:22:41".length()
! final int MAX_NANOS_LENGTH = 9; // 0 <= Timastamp.nanos <= 999999999
SimpleDateFormat df = null;
+ int nanos = 0;
! // If first time, create the buffer
! StringBuffer sbuf = new StringBuffer();
!
! if (s.length() < TIMESTAMP_LENGTH) {
! // In case of SQL Date.
! sbuf.setLength(0);
! sbuf.append(s);
! df = new SimpleDateFormat("yyyy-MM-dd");
! } else {
! // In case of SQL Timestamp.
!
! // Search position of decimal point and time zone.
! int decimalPointIndex = s.length();
! int timezoneIndex = s.length();
! for (int i = TIMESTAMP_LENGTH; i < s.length(); i++) {
! switch (s.charAt(i)) {
! case '.':
! decimalPointIndex = i;
! break;
! case '+':
! case '-':
! timezoneIndex = i;
! break;
! default:
! break; // digit
! }
}
! if (decimalPointIndex < s.length()) {
! // Parse fractional seconds
! sbuf.setLength(0);
! sbuf.append(s.substring(decimalPointIndex + 1, timezoneIndex));
! for (int i = sbuf.length(); i < MAX_NANOS_LENGTH; i++) {
! sbuf.append('0');
! }
! sbuf.setLength(MAX_NANOS_LENGTH);
! nanos = Integer.parseInt(sbuf.toString());
! }
! sbuf.setLength(0);
! sbuf.append(s.substring(0, TIMESTAMP_LENGTH));
! if (timezoneIndex < s.length()) {
! // Parse time zone.
! String tz = s.substring(timezoneIndex, s.length());
! sbuf.append(" GMT");
! sbuf.append(tz);
! if (tz.length() <= 3) // "+09".length()
! sbuf.append(":00");
! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss z");
! } else {
! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
! }
}
try
{
// All that's left is to parse the string and return the ts.
! Timestamp ts = new Timestamp(df.parse(sbuf.toString()).getTime());
! ts.setNanos(nanos);
! return ts;
}
catch (ParseException e)
{
jdbc2.ResultSet.java.diffapplication/octet-stream; name=jdbc2.ResultSet.java.diffDownload
*** org/postgresql/jdbc2/ResultSet.java_7.2b4 Tue Dec 11 13:48:05 2001
--- org/postgresql/jdbc2/ResultSet.java Thu Jan 17 19:42:19 2002
***************
*** 1597,1606 ****
* The driver is set to return ISO date formated strings. We modify this
* string from the ISO format to a format that Java can understand. Java
* expects timezone info as 'GMT+09:00' where as ISO gives '+09'.
! * Java also expects fractional seconds to 3 places where postgres
* will give, none, 2 or 6 depending on the time and postgres version.
* From version 7.2 postgres returns fractional seconds to 6 places.
- * If available, we drop the last 3 digits.
*
* @param s The ISO formated date string to parse.
* @param resultSet The ResultSet this date is part of.
--- 1597,1605 ----
* The driver is set to return ISO date formated strings. We modify this
* string from the ISO format to a format that Java can understand. Java
* expects timezone info as 'GMT+09:00' where as ISO gives '+09'.
! * Java also expects fractional seconds to 9 places where postgres
* will give, none, 2 or 6 depending on the time and postgres version.
* From version 7.2 postgres returns fractional seconds to 6 places.
*
* @param s The ISO formated date string to parse.
* @param resultSet The ResultSet this date is part of.
***************
*** 1613,1704 ****
throws SQLException
{
if (s == null)
! return null;
// We must be synchronized here incase more theads access the ResultSet
! // bad practice but possible. Anyhow this is to protect sbuf and
! // SimpleDateFormat objects
synchronized (resultSet)
{
SimpleDateFormat df = null;
! // If first time, create the buffer, otherwise clear it.
if (resultSet.sbuf == null)
resultSet.sbuf = new StringBuffer();
! else
resultSet.sbuf.setLength(0);
! // Copy s into sbuf for parsing.
! resultSet.sbuf.append(s);
! if (s.length() > 19)
! {
! // The len of the ISO string to the second value is 19 chars. If
! // greater then 19, there should be tz info and perhaps fractional
! // second info which we need to change to java to read it.
!
! // cut the copy to second value "2001-12-07 16:29:22"
! int i = 19;
! resultSet.sbuf.setLength(i);
!
! char c = s.charAt(i++);
! if (c == '.')
! {
! // Found a fractional value. Append up to 3 digits including
! // the leading '.'
! do
! {
! if (i < 24)
! resultSet.sbuf.append(c);
! c = s.charAt(i++);
! } while (Character.isDigit(c));
!
! // If there wasn't at least 3 digits we should add some zeros
! // to make up the 3 digits we tell java to expect.
! for (int j = i; j < 24; j++)
resultSet.sbuf.append('0');
}
- else
- {
- // No fractional seconds, lets add some.
- resultSet.sbuf.append(".000");
- }
-
- // prepend the GMT part and then add the remaining bit of
- // the string.
- resultSet.sbuf.append(" GMT");
- resultSet.sbuf.append(c);
- resultSet.sbuf.append(s.substring(i, s.length()));
-
- // Lastly, if the tz part doesn't specify the :MM part then
- // we add ":00" for java.
- if (s.length() - i < 5)
- resultSet.sbuf.append(":00");
! // we'll use this dateformat string to parse the result.
! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS z");
! }
! else if (s.length() == 19)
! {
! // No tz or fractional second info.
! // I'm not sure if it is
! // possible to have a string in this format, as pg
! // should give us tz qualified timestamps back, but it was
! // in the old code, so I'm handling it for now.
! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
! }
! else
! {
! // We must just have a date. This case is
! // needed if this method is called on a date
! // column
! df = new SimpleDateFormat("yyyy-MM-dd");
}
try
{
// All that's left is to parse the string and return the ts.
! return new Timestamp(df.parse(resultSet.sbuf.toString()).getTime());
}
catch (ParseException e)
{
--- 1612,1695 ----
throws SQLException
{
if (s == null)
! return null; // SQL NULL
!
! // 2002-01-15 17:22:41.863506+09 timestamp with time zone
! // 2002-01-15 17:25:11.451415 timestamp without time zone
! // 2002-01-15 17:22:41+09 timestamp(0) with time zone
! // 2002-01-15 17:25:11 timestamp(0) without time zone
! // 2002-01-15 date
// We must be synchronized here incase more theads access the ResultSet
! // bad practice but possible. Anyhow this is to protect sbuf.
synchronized (resultSet)
{
+ final int TIMESTAMP_LENGTH = 19; // "2002-01-15 17:22:41".length()
+ final int MAX_NANOS_LENGTH = 9; // 0 <= Timastamp.nanos <= 999999999
SimpleDateFormat df = null;
+ int nanos = 0;
! // If first time, create the buffer
if (resultSet.sbuf == null)
resultSet.sbuf = new StringBuffer();
!
! if (s.length() < TIMESTAMP_LENGTH) {
! // In case of SQL Date.
resultSet.sbuf.setLength(0);
+ resultSet.sbuf.append(s);
+ df = new SimpleDateFormat("yyyy-MM-dd");
+ } else {
+ // In case of SQL Timestamp.
! // Search position of decimal point and time zone.
! int decimalPointIndex = s.length();
! int timezoneIndex = s.length();
! for (int i = TIMESTAMP_LENGTH; i < s.length(); i++) {
! switch (s.charAt(i)) {
! case '.':
! decimalPointIndex = i;
! break;
! case '+':
! case '-':
! timezoneIndex = i;
! break;
! default:
! break; // digit
! }
! }
! if (decimalPointIndex < s.length()) {
! // Parse fractional seconds
! resultSet.sbuf.setLength(0);
! resultSet.sbuf.append(s.substring(decimalPointIndex + 1, timezoneIndex));
! for (int i = resultSet.sbuf.length(); i < MAX_NANOS_LENGTH; i++) {
resultSet.sbuf.append('0');
+ }
+ resultSet.sbuf.setLength(MAX_NANOS_LENGTH);
+ nanos = Integer.parseInt(resultSet.sbuf.toString());
}
! resultSet.sbuf.setLength(0);
! resultSet.sbuf.append(s.substring(0, TIMESTAMP_LENGTH));
! if (timezoneIndex < s.length()) {
! // Parse time zone.
! String tz = s.substring(timezoneIndex, s.length());
! resultSet.sbuf.append(" GMT");
! resultSet.sbuf.append(tz);
! if (tz.length() <= 3) // "+09".length()
! resultSet.sbuf.append(":00");
! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss z");
! } else {
! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
! }
}
try
{
// All that's left is to parse the string and return the ts.
! Timestamp ts = new Timestamp(df.parse(resultSet.sbuf.toString()).getTime());
! ts.setNanos(nanos);
! return ts;
}
catch (ParseException e)
{
Hi Matsuda-san,
You beat me too it :) The following is a similar patch to the same code.
I've tested it with your GetTimestampTest.java code and it looks good
from what I can see. I'm attaching both jdbc1 and jdbc2 patches. This
patch changes a bit less in the code and basically adds a check to the
fraction loop for the end of string, as well as a check for a tz before
adding the GMT bit.
Tom.
On Thu, Jan 17, 2002 at 08:00:01PM +0900, Ryouichi Matsuda wrote:
Barry Lind wrote:
Then however I did try your last query:
select 'now'::timestamp without time zone;
and this does fail for me with the string index out of bounds exception.
An attached patch corrects problem of this bug and fractional second.
The handling of time zone was as follows:
(a) with time zone
using SimpleDateFormat("yyyy-MM-dd HH:mm:ss z")
(b) without time zone
using SimpleDateFormat("yyyy-MM-dd HH:mm:ss")About problem of fractional second,
Fractional second was changed from milli-second to nano-second.
--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs
Attachments:
j1.difftext/plain; charset=us-asciiDownload
Index: org/postgresql/jdbc1/ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/ResultSet.java,v
retrieving revision 1.29
diff -c -r1.29 ResultSet.java
*** org/postgresql/jdbc1/ResultSet.java 2002/01/05 22:26:22 1.29
--- org/postgresql/jdbc1/ResultSet.java 2002/01/17 13:53:16
***************
*** 514,520 ****
StringBuffer sbuf = new StringBuffer(s);
SimpleDateFormat df = null;
! if (s.length() > 19)
{
// The len of the ISO string to the second value is 19 chars. If
// greater then 19, there should be tz info and perhaps fractional
--- 514,522 ----
StringBuffer sbuf = new StringBuffer(s);
SimpleDateFormat df = null;
! int slen = s.length();
!
! if (slen > 19)
{
// The len of the ISO string to the second value is 19 chars. If
// greater then 19, there should be tz info and perhaps fractional
***************
*** 534,540 ****
if (i < 24)
sbuf.append(c);
c = s.charAt(i++);
! } while (Character.isDigit(c));
// If there wasn't at least 3 digits we should add some zeros
// to make up the 3 digits we tell java to expect.
--- 536,542 ----
if (i < 24)
sbuf.append(c);
c = s.charAt(i++);
! } while (i < slen && Character.isDigit(c));
// If there wasn't at least 3 digits we should add some zeros
// to make up the 3 digits we tell java to expect.
***************
*** 547,567 ****
sbuf.append(".000");
}
! // prepend the GMT part and then add the remaining bit of
! // the string.
! sbuf.append(" GMT");
! sbuf.append(c);
! sbuf.append(s.substring(i, s.length()));
!
! // Lastly, if the tz part doesn't specify the :MM part then
! // we add ":00" for java.
! if (s.length() - i < 5)
! sbuf.append(":00");
! // we'll use this dateformat string to parse the result.
! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS z");
}
! else if (s.length() == 19)
{
// No tz or fractional second info.
// I'm not sure if it is
--- 549,576 ----
sbuf.append(".000");
}
! if (i < slen)
! {
! // prepend the GMT part and then add the remaining bit of
! // the string.
! sbuf.append(" GMT");
! sbuf.append(c);
! sbuf.append(s.substring(i, slen));
!
! // Lastly, if the tz part doesn't specify the :MM part then
! // we add ":00" for java.
! if (slen - i < 5)
! sbuf.append(":00");
! // we'll use this dateformat string to parse the result.
! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS z");
! }
! else
! {
! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
! }
}
! else if (slen == 19)
{
// No tz or fractional second info.
// I'm not sure if it is
j2.difftext/plain; charset=us-asciiDownload
Index: org/postgresql/jdbc2/ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java,v
retrieving revision 1.47
diff -c -r1.47 ResultSet.java
*** org/postgresql/jdbc2/ResultSet.java 2002/01/05 22:26:22 1.47
--- org/postgresql/jdbc2/ResultSet.java 2002/01/17 13:42:14
***************
*** 1630,1640 ****
// Copy s into sbuf for parsing.
resultSet.sbuf.append(s);
! if (s.length() > 19)
{
// The len of the ISO string to the second value is 19 chars. If
! // greater then 19, there should be tz info and perhaps fractional
// second info which we need to change to java to read it.
// cut the copy to second value "2001-12-07 16:29:22"
--- 1630,1641 ----
// Copy s into sbuf for parsing.
resultSet.sbuf.append(s);
+ int slen = s.length();
! if (slen > 19)
{
// The len of the ISO string to the second value is 19 chars. If
! // greater then 19, there may be tz info and perhaps fractional
// second info which we need to change to java to read it.
// cut the copy to second value "2001-12-07 16:29:22"
***************
*** 1651,1657 ****
if (i < 24)
resultSet.sbuf.append(c);
c = s.charAt(i++);
! } while (Character.isDigit(c));
// If there wasn't at least 3 digits we should add some zeros
// to make up the 3 digits we tell java to expect.
--- 1652,1658 ----
if (i < 24)
resultSet.sbuf.append(c);
c = s.charAt(i++);
! } while (i < slen && Character.isDigit(c));
// If there wasn't at least 3 digits we should add some zeros
// to make up the 3 digits we tell java to expect.
***************
*** 1664,1684 ****
resultSet.sbuf.append(".000");
}
! // prepend the GMT part and then add the remaining bit of
! // the string.
! resultSet.sbuf.append(" GMT");
! resultSet.sbuf.append(c);
! resultSet.sbuf.append(s.substring(i, s.length()));
!
! // Lastly, if the tz part doesn't specify the :MM part then
! // we add ":00" for java.
! if (s.length() - i < 5)
! resultSet.sbuf.append(":00");
! // we'll use this dateformat string to parse the result.
! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS z");
}
! else if (s.length() == 19)
{
// No tz or fractional second info.
// I'm not sure if it is
--- 1665,1693 ----
resultSet.sbuf.append(".000");
}
! if (i < slen)
! {
! // prepend the GMT part and then add the remaining bit of
! // the string.
! resultSet.sbuf.append(" GMT");
! resultSet.sbuf.append(c);
! resultSet.sbuf.append(s.substring(i, slen));
!
! // Lastly, if the tz part doesn't specify the :MM part then
! // we add ":00" for java.
! if (slen - i < 5)
! resultSet.sbuf.append(":00");
! // we'll use this dateformat string to parse the result.
! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS z");
! }
! else
! {
! // Just found fractional seconds but no timezone.
! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
! }
}
! else if (slen == 19)
{
// No tz or fractional second info.
// I'm not sure if it is
This has been saved for the 7.3 release:
http://candle.pha.pa.us/cgi-bin/pgpatches2
---------------------------------------------------------------------------
Ryouichi Matsuda wrote:
Barry Lind wrote:
Then however I did try your last query:
select 'now'::timestamp without time zone;
and this does fail for me with the string index out of bounds exception.
An attached patch corrects problem of this bug and fractional second.
The handling of time zone was as follows:
(a) with time zone
using SimpleDateFormat("yyyy-MM-dd HH:mm:ss z")
(b) without time zone
using SimpleDateFormat("yyyy-MM-dd HH:mm:ss")About problem of fractional second,
Fractional second was changed from milli-second to nano-second.
[ Attachment, skipping... ]
[ Attachment, skipping... ]
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
--
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 19026This has been saved for the 7.3 release:
http://candle.pha.pa.us/cgi-bin/pgpatches2
---------------------------------------------------------------------------
Thomas O'Dowd wrote:
Hi Matsuda-san,
You beat me too it :) The following is a similar patch to the same code.
I've tested it with your GetTimestampTest.java code and it looks good
from what I can see. I'm attaching both jdbc1 and jdbc2 patches. This
patch changes a bit less in the code and basically adds a check to the
fraction loop for the end of string, as well as a check for a tz before
adding the GMT bit.Tom.
On Thu, Jan 17, 2002 at 08:00:01PM +0900, Ryouichi Matsuda wrote:
Barry Lind wrote:
Then however I did try your last query:
select 'now'::timestamp without time zone;
and this does fail for me with the string index out of bounds exception.
An attached patch corrects problem of this bug and fractional second.
The handling of time zone was as follows:
(a) with time zone
using SimpleDateFormat("yyyy-MM-dd HH:mm:ss z")
(b) without time zone
using SimpleDateFormat("yyyy-MM-dd HH:mm:ss")About problem of fractional second,
Fractional second was changed from milli-second to nano-second.--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs
[ Attachment, skipping... ]
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--
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
Hi
I am trying to insert non english characters ( chars which lie in the
128-255 range in the ASCII character set ). When I try to get this data from
the table, I am getting garbled data. Is there any way to insert and
retrieve non english characters in pgsql
regards
Sulakshana
I think, you must adjust the Characterset of your database. By default it is
ASCII, but you can create databases with unicode or one of the europien
character sets (LATIN-1 or something else)
That should solve your problem.
Oliver Friedrich
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Sulakshana
Awsarikar
Sent: Friday, January 25, 2002 2:26 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Inserting Non English characters in a varchar type field
Hi
I am trying to insert non english characters ( chars which lie in the
128-255 range in the ASCII character set ). When I try to get this data from
the table, I am getting garbled data. Is there any way to insert and
retrieve non english characters in pgsql
regards
Sulakshana
---------------------------(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
hi,
I get the following error while retrieving a datetime column using
getTimestamp.
Bad Timestamp Format at 12 in 1970-1-1 0:0;
Bad Timestamp Format at 12 in 1970-1-1 0:0
at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
at
org.jboss.pool.jdbc.ResultSetInPool.getTimestamp(ResultSetInPool.java:734)
regards,
Nagarajan.
Hi Nagarajan,
What driver version are you using? The latest drivers at least
should switch the database to return ISO datestyle timestamps
when it first gets a connection. The current driver code expects
to parse the ISO format which is why you are getting this exception
below. Are you changing the datestyle in your program? I'm unfamilar
with how to get postgres to return a timestamp in the short format without
the seconds which you have below. The ISO format usually returns...
1970-01-01 00:00:00 if without time zone is specified. Can you perhaps
provide sample code that reproduces the problem using the latest driver?
You can download the latest drivers here...
http://jdbc.postgresql.org/download.html
Cheers,
Tom.
On Sat, Jan 26, 2002 at 02:33:15PM +0100, Gunaseelan Nagarajan wrote:
hi,
I get the following error while retrieving a datetime column using
getTimestamp.Bad Timestamp Format at 12 in 1970-1-1 0:0;
Bad Timestamp Format at 12 in 1970-1-1 0:0
at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
at
org.jboss.pool.jdbc.ResultSetInPool.getTimestamp(ResultSetInPool.java:734)regards,
Nagarajan.---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs
Hi Tom,
I went to ResultSet.java and made all the formats into
df = new SimpleDateFormat("yyyy-MM-dd HH:mm");
so now it works. As I don't require the seconds and milliseconds part,
it is ok now.
my program inserts the date values in the "yyyy-MM-dd HH:mm" format.
Perhaps that is causing the problem. However I am not changing the
default format either in the driver or in the database.
Thanks,
Nagarajan.
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Thomas O'Dowd
Sent: Saturday, January 26, 2002 4:47 PM
To: Gunaseelan Nagarajan
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Bad Timestamp Format
Hi Nagarajan,
What driver version are you using? The latest drivers at least
should switch the database to return ISO datestyle timestamps
when it first gets a connection. The current driver code expects
to parse the ISO format which is why you are getting this exception
below. Are you changing the datestyle in your program? I'm unfamilar
with how to get postgres to return a timestamp in the short format without
the seconds which you have below. The ISO format usually returns...
1970-01-01 00:00:00 if without time zone is specified. Can you perhaps
provide sample code that reproduces the problem using the latest driver?
You can download the latest drivers here...
http://jdbc.postgresql.org/download.html
Cheers,
Tom.
On Sat, Jan 26, 2002 at 02:33:15PM +0100, Gunaseelan Nagarajan wrote:
hi,
I get the following error while retrieving a datetime column using
getTimestamp.Bad Timestamp Format at 12 in 1970-1-1 0:0;
Bad Timestamp Format at 12 in 1970-1-1 0:0
at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
at
org.jboss.pool.jdbc.ResultSetInPool.getTimestamp(ResultSetInPool.java:734)regards,
Nagarajan.---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Hi Nagarajan,
On Sat, Jan 26, 2002 at 05:14:31PM +0100, G.Nagarajan wrote:
Hi Tom,
I went to ResultSet.java and made all the formats into
df = new SimpleDateFormat("yyyy-MM-dd HH:mm");so now it works. As I don't require the seconds and milliseconds part,
it is ok now.
Hmmmmmm. Not sure that this is the correct solution for your particular
problem (ie. altering the driver) although it may work for you now. It
would be better to find out the core of the problem and fix that.
my program inserts the date values in the "yyyy-MM-dd HH:mm" format.
Perhaps that is causing the problem. However I am not changing the
default format either in the driver or in the database.
What database version are you using? What driver version are you using?
I can't figure out how to get 7.2 beta to return a shorter timestamp
then 1970-01-01 00:00:00. even if I insert a short timestamp. I presume
you are using older code? As I said before, the driver should force
the datestyle to ISO when it first gets a connection.
select '1970-1-1 0:0'::timestamp without time zone, '1970-1-1 0:0'::timestamp;
timestamp | timestamptz
---------------------+------------------------
1970-01-01 00:00:00 | 1970-01-01 00:00:00+09
(1 row)
If I can figure out how you are doing it and if the its a problem in the
latest code, then we can fix it.
Tom.
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Thomas O'Dowd
Sent: Saturday, January 26, 2002 4:47 PM
To: Gunaseelan Nagarajan
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Bad Timestamp FormatHi Nagarajan,
What driver version are you using? The latest drivers at least
should switch the database to return ISO datestyle timestamps
when it first gets a connection. The current driver code expects
to parse the ISO format which is why you are getting this exception
below. Are you changing the datestyle in your program? I'm unfamilar
with how to get postgres to return a timestamp in the short format without
the seconds which you have below. The ISO format usually returns...
1970-01-01 00:00:00 if without time zone is specified. Can you perhaps
provide sample code that reproduces the problem using the latest driver?You can download the latest drivers here...
http://jdbc.postgresql.org/download.html
Cheers,
Tom.
On Sat, Jan 26, 2002 at 02:33:15PM +0100, Gunaseelan Nagarajan wrote:
hi,
I get the following error while retrieving a datetime column using
getTimestamp.Bad Timestamp Format at 12 in 1970-1-1 0:0;
Bad Timestamp Format at 12 in 1970-1-1 0:0
at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
at
org.jboss.pool.jdbc.ResultSetInPool.getTimestamp(ResultSetInPool.java:734)regards,
Nagarajan.---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs
Hi Tom,
I am using PostgreSQL 7.1.3. It was built from the source file
by using ./configure --with-java etc. I used the jdbc source that comes
along with the source and did not download it seperately.
Here is my java environment.
JDK 1.3.10
JBoss 2.4
I use the following code for inserting the date value
public String giveInsert(Calendar cal)
throws Exception{
String colValue = "";
colValue = "'" + cal.get(Calendar.YEAR) + "-"
+ ( cal.get( Calendar.MONTH ) + 1 ) + "-"
+ cal.get(Calendar.DAY_OF_MONTH) + " "
+ cal.get( Calendar.HOUR_OF_DAY ) + ":"
+ cal.get( Calendar.MINUTE ) + "'"; return colValue;
}
For retreiving from the database, I use the following code
public Calendar takeFromResultSet(ResultSet rs, String columnName)
throws SQLException, Exception{
Timestamp ts = rs.getTimestamp(columnName);
Calendar c = Calendar.getInstance();
if(ts != null)
c.set(ts.getYear()+1900, ts.getMonth(), ts.getDate(),
ts.getHours(),ts.getMinutes());
else
c = null;
return c;
}
The database connection comes through the JBoss connection pool handler.
Maybe that is not setting the required database property?
here is the configuration settings in jboss.jcml
<mbean code="org.jboss.jdbc.XADataSourceLoader"
name="DefaultDomain:service=XADataSource,name=postgresqlDS">
<attribute
name="DataSourceClass">org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl</attribute>
<attribute name="PoolName">postgresqlDS</attribute>
<attribute name="URL">jdbc:postgresql:db</attribute>
<attribute name="JDBCUser">root</attribute>
<attribute name="Password">japan</attribute>
</mbean>
To get the database connection, I use the following code
cat.debug("Using jboss pool to get Connection");
Context ctx = new InitialContext();
javax.sql.DataSource ds =(javax.sql.DataSource)ctx.lookup( jndiName );
con = ds.getConnection();
Thanks and Regards,
Nagarajan.
Show quoted text
On Saturday 26 January 2002 14:33, you wrote:
hi,
I get the following error while retrieving a datetime column using
getTimestamp.Bad Timestamp Format at 12 in 1970-1-1 0:0;
Bad Timestamp Format at 12 in 1970-1-1 0:0
at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
at
org.jboss.pool.jdbc.ResultSetInPool.getTimestamp(ResultSetInPool.java:734)regards,
Nagarajan.---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Hi Nagarajan,
I'm not familiar with jboss, but I just tried messing with an old
7.1.2 database that I had lying around and can't get it to return
a short format of a timestamp like "1970-1-1 0:0". The driver
code in 7.1.3 will cause an exception for this short form as you
are getting and the 7.2 driver will just parse the datepart but
not the hour/minute part.
Before changing the new driver code to handle this format, I'd
like to know how you are generating it, so that a) I know it makes
sense to support it and b) that I can test it. Can anyone on the
list let me know how to get the backend to return this short format?
The only way I can think about doing it is using the to_char()
functions which you would have to be explicitly doing??? ie, something
like:
select now(), to_char(now(), 'YYYY-FMMM-FMDD FMHH24:FMMI');
now | to_char
------------------------+-----------------
2002-01-28 10:26:09+09 | 2002-1-28 10:26
(1 row)
If your select code or jboss is somehow using to_char() to alter the
default timestamp string, its doing something unusual which the driver
doesn't support.
Can anyone add anymore reasons why the backend would be returning
a short timestamp string to the driver, such as specific datestyle
options, a specific locale or other?
Tom.
On Sun, Jan 27, 2002 at 03:22:58PM +0100, Gunaseelan Nagarajan wrote:
Hi Tom,
I am using PostgreSQL 7.1.3. It was built from the source file
by using ./configure --with-java etc. I used the jdbc source that comes
along with the source and did not download it seperately.Here is my java environment.
JDK 1.3.10
JBoss 2.4I use the following code for inserting the date value
public String giveInsert(Calendar cal) throws Exception{ String colValue = ""; colValue = "'" + cal.get(Calendar.YEAR) + "-" + ( cal.get( Calendar.MONTH ) + 1 ) + "-" + cal.get(Calendar.DAY_OF_MONTH) + " " + cal.get( Calendar.HOUR_OF_DAY ) + ":" + cal.get( Calendar.MINUTE ) + "'";return colValue;
}For retreiving from the database, I use the following code
public Calendar takeFromResultSet(ResultSet rs, String columnName)
throws SQLException, Exception{
Timestamp ts = rs.getTimestamp(columnName);
Calendar c = Calendar.getInstance();
if(ts != null)
c.set(ts.getYear()+1900, ts.getMonth(), ts.getDate(),
ts.getHours(),ts.getMinutes());
else
c = null;
return c;
}The database connection comes through the JBoss connection pool handler.
Maybe that is not setting the required database property?here is the configuration settings in jboss.jcml
<mbean code="org.jboss.jdbc.XADataSourceLoader"
name="DefaultDomain:service=XADataSource,name=postgresqlDS">
<attribute
name="DataSourceClass">org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl</attribute>
<attribute name="PoolName">postgresqlDS</attribute>
<attribute name="URL">jdbc:postgresql:db</attribute>
<attribute name="JDBCUser">root</attribute>
<attribute name="Password">japan</attribute>
</mbean>To get the database connection, I use the following code
cat.debug("Using jboss pool to get Connection");
Context ctx = new InitialContext();
javax.sql.DataSource ds =(javax.sql.DataSource)ctx.lookup( jndiName );
con = ds.getConnection();Thanks and Regards,
Nagarajan.On Saturday 26 January 2002 14:33, you wrote:
hi,
I get the following error while retrieving a datetime column using
getTimestamp.Bad Timestamp Format at 12 in 1970-1-1 0:0;
Bad Timestamp Format at 12 in 1970-1-1 0:0
at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
at
org.jboss.pool.jdbc.ResultSetInPool.getTimestamp(ResultSetInPool.java:734)regards,
Nagarajan.---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs
Hi Tom,
I use a "Select * from table" and no to_char() functions. I will post
the same question to the jboss-mailing list and see if anyone there
has had the same problem. It could also be a feature of the JBoss
resultset implementation.
Regards,
Nagarajan.
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Thomas O'Dowd
Sent: Monday, January 28, 2002 2:30 AM
To: Gunaseelan Nagarajan
Cc:
Subject: Re: [JDBC] Bad Timestamp Format
Hi Nagarajan,
I'm not familiar with jboss, but I just tried messing with an old
7.1.2 database that I had lying around and can't get it to return
a short format of a timestamp like "1970-1-1 0:0". The driver
code in 7.1.3 will cause an exception for this short form as you
are getting and the 7.2 driver will just parse the datepart but
not the hour/minute part.
Before changing the new driver code to handle this format, I'd
like to know how you are generating it, so that a) I know it makes
sense to support it and b) that I can test it. Can anyone on the
list let me know how to get the backend to return this short format?
The only way I can think about doing it is using the to_char()
functions which you would have to be explicitly doing??? ie, something
like:
select now(), to_char(now(), 'YYYY-FMMM-FMDD FMHH24:FMMI');
now | to_char
------------------------+-----------------
2002-01-28 10:26:09+09 | 2002-1-28 10:26
(1 row)
If your select code or jboss is somehow using to_char() to alter the
default timestamp string, its doing something unusual which the driver
doesn't support.
Can anyone add anymore reasons why the backend would be returning
a short timestamp string to the driver, such as specific datestyle
options, a specific locale or other?
Tom.
On Sun, Jan 27, 2002 at 03:22:58PM +0100, Gunaseelan Nagarajan wrote:
Hi Tom,
I am using PostgreSQL 7.1.3. It was built from the source file
by using ./configure --with-java etc. I used the jdbc source that comes
along with the source and did not download it seperately.Here is my java environment.
JDK 1.3.10
JBoss 2.4I use the following code for inserting the date value
public String giveInsert(Calendar cal) throws Exception{ String colValue = ""; colValue = "'" + cal.get(Calendar.YEAR) + "-" + ( cal.get( Calendar.MONTH ) + 1 ) + "-" + cal.get(Calendar.DAY_OF_MONTH) + " " + cal.get( Calendar.HOUR_OF_DAY ) + ":" + cal.get( Calendar.MINUTE ) + "'";return colValue;
}For retreiving from the database, I use the following code
public Calendar takeFromResultSet(ResultSet rs, String columnName)
throws SQLException, Exception{
Timestamp ts = rs.getTimestamp(columnName);
Calendar c = Calendar.getInstance();
if(ts != null)
c.set(ts.getYear()+1900, ts.getMonth(), ts.getDate(),
ts.getHours(),ts.getMinutes());
else
c = null;
return c;
}The database connection comes through the JBoss connection pool handler.
Maybe that is not setting the required database property?here is the configuration settings in jboss.jcml
<mbean code="org.jboss.jdbc.XADataSourceLoader"
name="DefaultDomain:service=XADataSource,name=postgresqlDS">
<attribute
name="DataSourceClass">org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl</attr
ibute>
<attribute name="PoolName">postgresqlDS</attribute>
<attribute name="URL">jdbc:postgresql:db</attribute>
<attribute name="JDBCUser">root</attribute>
<attribute name="Password">japan</attribute>
</mbean>To get the database connection, I use the following code
cat.debug("Using jboss pool to get Connection");
Context ctx = new InitialContext();
javax.sql.DataSource ds =(javax.sql.DataSource)ctx.lookup( jndiName );
con = ds.getConnection();Thanks and Regards,
Nagarajan.On Saturday 26 January 2002 14:33, you wrote:
hi,
I get the following error while retrieving a datetime column using
getTimestamp.Bad Timestamp Format at 12 in 1970-1-1 0:0;
Bad Timestamp Format at 12 in 1970-1-1 0:0
at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
at org.postgresql.jdbc2.ResultSet.getTimestamp(Unknown Source)
at
org.jboss.pool.jdbc.ResultSetInPool.getTimestamp(ResultSetInPool.java:734)
regards,
Nagarajan.---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs
---------------------------(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
Your patch has been added to the PostgreSQL unapplied patches list at:
http://candle.pha.pa.us/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---------------------------------------------------------------------------
Thomas O'Dowd wrote:
Hi Matsuda-san,
You beat me too it :) The following is a similar patch to the same code.
I've tested it with your GetTimestampTest.java code and it looks good
from what I can see. I'm attaching both jdbc1 and jdbc2 patches. This
patch changes a bit less in the code and basically adds a check to the
fraction loop for the end of string, as well as a check for a tz before
adding the GMT bit.Tom.
On Thu, Jan 17, 2002 at 08:00:01PM +0900, Ryouichi Matsuda wrote:
Barry Lind wrote:
Then however I did try your last query:
select 'now'::timestamp without time zone;
and this does fail for me with the string index out of bounds exception.
An attached patch corrects problem of this bug and fractional second.
The handling of time zone was as follows:
(a) with time zone
using SimpleDateFormat("yyyy-MM-dd HH:mm:ss z")
(b) without time zone
using SimpleDateFormat("yyyy-MM-dd HH:mm:ss")About problem of fractional second,
Fractional second was changed from milli-second to nano-second.--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs
[ Attachment, skipping... ]
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--
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 19026Patch applied. Thanks.
---------------------------------------------------------------------------
Thomas O'Dowd wrote:
Hi Matsuda-san,
You beat me too it :) The following is a similar patch to the same code.
I've tested it with your GetTimestampTest.java code and it looks good
from what I can see. I'm attaching both jdbc1 and jdbc2 patches. This
patch changes a bit less in the code and basically adds a check to the
fraction loop for the end of string, as well as a check for a tz before
adding the GMT bit.Tom.
On Thu, Jan 17, 2002 at 08:00:01PM +0900, Ryouichi Matsuda wrote:
Barry Lind wrote:
Then however I did try your last query:
select 'now'::timestamp without time zone;
and this does fail for me with the string index out of bounds exception.
An attached patch corrects problem of this bug and fractional second.
The handling of time zone was as follows:
(a) with time zone
using SimpleDateFormat("yyyy-MM-dd HH:mm:ss z")
(b) without time zone
using SimpleDateFormat("yyyy-MM-dd HH:mm:ss")About problem of fractional second,
Fractional second was changed from milli-second to nano-second.--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs
[ Attachment, skipping... ]
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--
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