Re: patch against cvs for getTimestamp() problem.
От | Barry Lind |
---|---|
Тема | Re: patch against cvs for getTimestamp() problem. |
Дата | |
Msg-id | 3C124726.6020304@xythos.com обсуждение исходный текст |
Ответ на | patch against cvs for getTimestamp() problem. ("Thomas O'Dowd" <tom@nooper.com>) |
Список | pgsql-jdbc |
Thomas, The following formats also need to be supported: "2001-12-07 16:26:59.4613+09", "2001-12-07 16:26:59.4613+09:30", and possibly any number of digits of fractional seconds should be supported, but last I checked the backend output two at a time (i.e. either .99, .9999, or .999999) I will look at the patch in detail later this weekend. thanks, --Barry Thomas O'Dowd wrote: > Hi all, > > I just upgraded my development environment to 7.2b3 and the latest cvs > driver both built from source. I found that ResultSet.getTimestamp() > was no longer working against a 7.2 database and causing an exception > parsing the timestamp that pg was returning. > > Bad Timestamp Format at 23 in 2001-12-06 23:24:07.895882+09 > at org.postgresql.jdbc2.ResultSet.toTimestamp(ResultSet.java:1707) > at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:398) > > It seems that timestamps have become more accurate in 7.2 going from > 2 fractional second digits to 6 which was causing the exception. Looking > at the code in toTimestamp() I decided that it was better to rewrite > it, then add a small fix as the method of parsing before was error > prone in my opinion and the new code (less the comments) is smaller > and easier to read IMHO :) > > I'm including patches against jdbc1 and jdbc2 ResultSet.java. I generated > the patch against the latest cvs, using cvs diff -c. I've tested the > jdbc2 versions pretty well and it parses the following combinations > > "2001-12-07 16:29:22.47+09", > "2001-12-07 16:29:26+09", > "2001-12-07 16:26:50.144213+09", > "2001-12-07 16:26:59.461349+09", > "2001-12-07 16:26:59.461349+05:30", > "2001-12-07 00:00:00+09", > "2001-12-07 12:00:00", > "2001-12-07" > > The driver is set to return ISO datestyle formats so I'm not sure if the > latter two formats are required but as the older code supported them, I've > added code to do this. If anyone can suggest more formats that pg may > return I can test those on the new code too. I've been running the new > driver with my code with no problem now. > > If anyone has any questions or suggestions please let me know. I think > this is an important bug fix for the 7.2 release of pg. Can someone test > the changes in the jdbc1 driver as I don't have a means of doing this. > > The patch file is attached. > > Regards, > > Tom. > > > ------------------------------------------------------------------------ > > Index: src/interfaces/jdbc/org/postgresql/jdbc1/ResultSet.java > =================================================================== > RCS file: /projects/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/ResultSet.java,v > retrieving revision 1.27 > diff -c -r1.27 ResultSet.java > *** src/interfaces/jdbc/org/postgresql/jdbc1/ResultSet.java 2001/11/25 23:26:59 1.27 > --- src/interfaces/jdbc/org/postgresql/jdbc1/ResultSet.java 2001/12/08 05:18:03 > *************** > *** 492,497 **** > --- 492,505 ---- > * Get the value of a column in the current row as a > * 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 > * @exception SQLException if a database access error occurs > *************** > *** 499,600 **** > public Timestamp getTimestamp(int columnIndex) throws SQLException > { > String s = getString(columnIndex); > if (s == null) > return null; > > ! boolean subsecond; > ! //if string contains a '.' we have fractional seconds > ! if (s.indexOf('.') == -1) > ! { > ! subsecond = false; > ! } > ! else > ! { > ! subsecond = true; > ! } > > ! //here we are modifying the string from ISO format to a format java can understand > ! //java expects timezone info as 'GMT-08:00' instead of '-08' in postgres ISO format > ! //and java expects three digits if fractional seconds are present instead of two for postgres > ! //so this code strips off timezone info and adds on the GMT+/-... > ! //as well as adds a third digit for partial seconds if necessary > ! StringBuffer strBuf = new StringBuffer(s); > ! > ! //we are looking to see if the backend has appended on a timezone. > ! //currently postgresql will return +/-HH:MM or +/-HH for timezone offset > ! //(i.e. -06, or +06:30, note the expectation of the leading zero for the > ! //hours, and the use of the : for delimiter between hours and minutes) > ! //if the backend ISO format changes in the future this code will > ! //need to be changed as well > ! char sub = strBuf.charAt(strBuf.length() - 3); > ! if (sub == '+' || sub == '-') > ! { > ! strBuf.setLength(strBuf.length() - 3); > ! if (subsecond) > ! { > ! strBuf.append('0').append("GMT").append(s.substring(s.length() - 3, s.length())).append(":00"); > ! } > ! else > ! { > ! strBuf.append("GMT").append(s.substring(s.length() - 3, s.length())).append(":00"); > ! } > ! } > ! else if (sub == ':') > { > ! //we may have found timezone info of format +/-HH:MM, or there is no > ! //timezone info at all and this is the : preceding the seconds > ! char sub2 = strBuf.charAt(strBuf.length() - 5); > ! if (sub2 == '+' || sub2 == '-') > { > ! //we have found timezone info of format +/-HH:MM > ! strBuf.setLength(strBuf.length() - 5); > ! if (subsecond) > ! { > ! strBuf.append('0').append("GMT").append(s.substring(s.length() - 5)); > ! } > ! else > { > ! strBuf.append("GMT").append(s.substring(s.length() - 5)); > ! } > } > ! else if (subsecond) > { > ! strBuf.append('0'); > } > - } > - else if (subsecond) > - { > - strBuf = strBuf.append('0'); > - } > > ! s = strBuf.toString(); > ! > ! SimpleDateFormat df = null; > ! > ! if (s.length() > 23 && subsecond) > ! { > ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSzzzzzzzzz"); > ! } > ! else if (s.length() > 23 && !subsecond) > ! { > ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz"); > ! } > ! else if (s.length() > 10 && subsecond) > ! { > ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); > ! } > ! else if (s.length() > 10 && !subsecond) > ! { > df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); > } > else > { > df = new SimpleDateFormat("yyyy-MM-dd"); > } > > try > { > ! return new Timestamp(df.parse(s).getTime()); > } > catch (ParseException e) > { > --- 507,593 ---- > public Timestamp getTimestamp(int columnIndex) throws SQLException > { > 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. We could add some, but > ! // then we have to figure out what tz we're in so its easier > ! // to just let Java do that for us. I'm not sure if this is > ! // possible to have to parse 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 a date. Again, I wonder if we have to handle > + // this case, but it was in the old code and I've no time > + // to figure it out. > df = new SimpleDateFormat("yyyy-MM-dd"); > } > > + // NOTE: if we don't have to handle the last two cases, then > + // we can just initialise df to the first string in a resultset > + // and use that throughout without having to create an object > + // everytime. Leave this for now. > + > 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) > { > Index: src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java > =================================================================== > RCS file: /projects/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java,v > retrieving revision 1.45 > diff -c -r1.45 ResultSet.java > *** src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java 2001/11/25 23:26:59 1.45 > --- src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java 2001/12/08 05:18:04 > *************** > *** 1591,1705 **** > } > } > > ! public static Timestamp toTimestamp(String s, ResultSet resultSet) throws SQLException > { > if (s == null) > return null; > > ! boolean subsecond; > ! //if string contains a '.' we have fractional seconds > ! if (s.indexOf('.') == -1) > ! { > ! subsecond = false; > ! } > ! else > ! { > ! subsecond = true; > ! } > ! > ! //here we are modifying the string from ISO format to a format java can understand > ! //java expects timezone info as 'GMT-08:00' instead of '-08' in postgres ISO format > ! //and java expects three digits if fractional seconds are present instead of two for postgres > ! //so this code strips off timezone info and adds on the GMT+/-... > ! //as well as adds a third digit for partial seconds if necessary > synchronized (resultSet) > { > ! // We must be synchronized here incase more theads access the ResultSet > ! // bad practice but possible. Anyhow this is to protect sbuf and > ! // SimpleDateFormat objects > > ! // First time? > if (resultSet.sbuf == null) > resultSet.sbuf = new StringBuffer(); > > ! resultSet.sbuf.setLength(0); > resultSet.sbuf.append(s); > > ! //we are looking to see if the backend has appended on a timezone. > ! //currently postgresql will return +/-HH:MM or +/-HH for timezone offset > ! //(i.e. -06, or +06:30, note the expectation of the leading zero for the > ! //hours, and the use of the : for delimiter between hours and minutes) > ! //if the backend ISO format changes in the future this code will > ! //need to be changed as well > ! char sub = resultSet.sbuf.charAt(resultSet.sbuf.length() - 3); > ! if (sub == '+' || sub == '-') > { > ! //we have found timezone info of format +/-HH > > ! resultSet.sbuf.setLength(resultSet.sbuf.length() - 3); > ! if (subsecond) > ! { > ! resultSet.sbuf.append('0').append("GMT").append(s.substring(s.length() - 3)).append(":00"); > ! } > ! else > ! { > ! resultSet.sbuf.append("GMT").append(s.substring(s.length() - 3)).append(":00"); > ! } > ! } > ! else if (sub == ':') > ! { > ! //we may have found timezone info of format +/-HH:MM, or there is no > ! //timezone info at all and this is the : preceding the seconds > ! char sub2 = resultSet.sbuf.charAt(resultSet.sbuf.length() - 5); > ! if (sub2 == '+' || sub2 == '-') > { > ! //we have found timezone info of format +/-HH:MM > ! resultSet.sbuf.setLength(resultSet.sbuf.length() - 5); > ! if (subsecond) > { > ! resultSet.sbuf.append('0').append("GMT").append(s.substring(s.length() - 5)); > ! } > ! else > ! { > ! resultSet.sbuf.append("GMT").append(s.substring(s.length() - 5)); > ! } > } > ! else if (subsecond) > { > ! resultSet.sbuf.append('0'); > } > - } > - else if (subsecond) > - { > - resultSet.sbuf.append('0'); > - } > - > - // could optimize this a tad to remove too many object creations... > - SimpleDateFormat df = null; > > ! if (resultSet.sbuf.length() > 23 && subsecond) > ! { > ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSzzzzzzzzz"); > ! } > ! else if (resultSet.sbuf.length() > 23 && !subsecond) > ! { > ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz"); > ! } > ! else if (resultSet.sbuf.length() > 10 && subsecond) > ! { > ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); > ! } > ! else if (resultSet.sbuf.length() > 10 && !subsecond) > ! { > df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); > } > else > { > df = new SimpleDateFormat("yyyy-MM-dd"); > } > > try > { > return new Timestamp(df.parse(resultSet.sbuf.toString()).getTime()); > } > catch (ParseException e) > --- 1591,1709 ---- > } > } > > ! /** > ! * Parse a string and return a timestamp representing its value. > ! * > ! * 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. > ! * > ! * @return null if s is null or a timestamp of the parsed string s. > ! * > ! * @throws SQLException if there is a problem parsing s. > ! **/ > ! public static Timestamp toTimestamp(String s, ResultSet resultSet) > ! 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. We could add some, but > ! // then we have to figure out what tz we're in so its easier > ! // to just let Java do that for us. I'm not sure if this is > ! // possible to have to parse 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 a date. Again, I wonder if we have to handle > + // this case, but it was in the old code and I've no time > + // to figure it out. > df = new SimpleDateFormat("yyyy-MM-dd"); > } > > + // NOTE: if we don't have to handle the last two cases, then > + // we can just initialise df to the first string in a resultset > + // and use that throughout without having to create an object > + // everytime. Leave this for now. > + > 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) > *************** > *** 1708,1714 **** > } > } > } > - > - > } > > --- 1712,1716 ---- > > > ------------------------------------------------------------------------ > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
В списке pgsql-jdbc по дате отправления:
Следующее
От: Ned WolpertДата:
Сообщение: Re: Bug with caching SQLTypes in Connection:getSQLType(oid)