Description:
When insert into a table contains Date column, using
rpst.setInt(1, 20080509);
works perfect.
when fetch that column use
rs.getInt(1);
2008 is returned.
How to repeat:
a java class connecting to jdbc:mysql://localhost/test
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MysqlGetIntOnDate {
/**
* @param args
*/
public static void main(String[] args) {
Connection conn = getMySQLconn();
int day = 20080509;
testInsert(conn, day);
if (day == testFetch(conn))
System.out.println("getInt on Date field matched");
else
System.out.println("getInt on Date field failed");
}
static Connection getMySQLconn() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager
.getConnection(
"jdbc:mysql://localhost/test?useUnicode=yes&characterEncoding=UTF-8",
"root", "");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void testInsert(Connection conn, int day) {
PreparedStatement rpst = null;
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
stmt.execute("drop table if exists tmptest");
stmt
.execute("create table tmptest(id int not null primary key, day date not null) DEFAULT CHARSET=utf8");
stmt.close();
String thesql = "replace into tmptest(id, day) values(1, ?)";
rpst = conn.prepareStatement(thesql);
rpst.setInt(1, day);
rpst.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (rpst != null)
rpst.close();
} catch (SQLException e) {
}
}
}
public static int testFetch(Connection conn) {
PreparedStatement rpst = null;
ResultSet rs = null;
try {
String thesql = "select day from tmptest where id = 1";
rpst = conn.prepareStatement(thesql);
rs = rpst.executeQuery();
if (rs.next()) {
int day = rs.getInt(1);
System.out.println("got day: " + day);
return day;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
rpst.close();
} catch (SQLException e) {
}
}
return 0;
}
}
Suggested fix:
for Connector/J 5.0.8, a quick and dirty patch
diff -ur src.orig/com/mysql/jdbc/ResultSet.java src/com/mysql/jdbc/ResultSet.java
--- src.orig/com/mysql/jdbc/ResultSet.java 2007-10-05 05:11:24.000000000 +0800
+++ src/com/mysql/jdbc/ResultSet.java 2008-05-08 18:51:42.000000000 +0800
@@ -2649,7 +2649,12 @@
if (!needsFullParse) {
try {
- return parseIntWithOverflowCheck(columnIndex,
+ int field_mysqltype = this.fields[columnIndex - 1].getMysqlType();
+ if (field_mysqltype == MysqlDefs.FIELD_TYPE_DATE || field_mysqltype == MysqlDefs.FIELD_TYPE_NEWDATE)
+ return parseDateIntWithOverflowCheck(columnIndex,
+ intAsBytes, null);
+ else
+ return parseIntWithOverflowCheck(columnIndex,
intAsBytes, null);
} catch (NumberFormatException nfe) {
try {
@@ -7326,6 +7331,96 @@
return (int) valueAsDouble;
}
+ public static int getDateInt(byte[] buf, int offset, int endPos) throws NumberFormatException {
+ int base = 10;
+
+ int s = offset;
+
+ /* Skip white space. */
+ while (Character.isWhitespace((char) buf[s]) && (s < endPos)) {
+ ++s;
+ }
+
+ if (s == endPos) {
+ throw new NumberFormatException(new String(buf));
+ }
+
+ /* Check for a sign. */
+ boolean negative = false;
+
+ if ((char) buf[s] == '-') {
+ negative = true;
+ ++s;
+ } else if ((char) buf[s] == '+') {
+ ++s;
+ }
+
+ /* Save the pointer so we can check later if anything happened. */
+ int save = s;
+
+ int cutoff = Integer.MAX_VALUE / base;
+ int cutlim = (Integer.MAX_VALUE % base);
+
+ if (negative) {
+ cutlim++;
+ }
+
+ boolean overflow = false;
+
+ int i = 0;
+
+ for (; s < endPos; s++) {
+ char c = (char) buf[s];
+
+ if (Character.isDigit(c)) {
+ c -= '0';
+ } else if (Character.isLetter(c)) {
+ c = (char) (Character.toUpperCase(c) - 'A' + 10);
+ } else {
+ continue;
+ }
+
+ if (c >= base) {
+ break;
+ }
+
+ /* Check for overflow. */
+ if ((i > cutoff) || ((i == cutoff) && (c > cutlim))) {
+ overflow = true;
+ } else {
+ i *= base;
+ i += c;
+ }
+ }
+
+ if (s == save) {
+ throw new NumberFormatException(new String(buf));
+ }
+
+ if (overflow) {
+ throw new NumberFormatException(new String(buf));
+ }
+
+ /* Return the result of the appropriate sign. */
+ return (negative ? (-i) : i);
+ }
+
+ private int parseDateIntWithOverflowCheck(int columnIndex, byte[] valueAsBytes,
+ String valueAsString) throws NumberFormatException, SQLException {
+
+ if (valueAsBytes == null && valueAsString == null) {
+ return 0;
+ }
+
+ if (valueAsBytes != null) {
+ return getDateInt(valueAsBytes, 0 , valueAsBytes.length);
+ } else {
+ valueAsString = valueAsString.trim();
+ valueAsString.replace('-', ',');
+ return Integer.parseInt(valueAsString);
+ }
+ }
+
private int parseIntWithOverflowCheck(int columnIndex, byte[] valueAsBytes,
String valueAsString) throws NumberFormatException, SQLException {
Description: When insert into a table contains Date column, using rpst.setInt(1, 20080509); works perfect. when fetch that column use rs.getInt(1); 2008 is returned. How to repeat: a java class connecting to jdbc:mysql://localhost/test import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class MysqlGetIntOnDate { /** * @param args */ public static void main(String[] args) { Connection conn = getMySQLconn(); int day = 20080509; testInsert(conn, day); if (day == testFetch(conn)) System.out.println("getInt on Date field matched"); else System.out.println("getInt on Date field failed"); } static Connection getMySQLconn() { Connection conn = null; try { Class.forName("com.mysql.jdbc.Driver").newInstance(); conn = DriverManager .getConnection( "jdbc:mysql://localhost/test?useUnicode=yes&characterEncoding=UTF-8", "root", ""); } catch (Exception e) { e.printStackTrace(); } return conn; } public static void testInsert(Connection conn, int day) { PreparedStatement rpst = null; Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); stmt.execute("drop table if exists tmptest"); stmt .execute("create table tmptest(id int not null primary key, day date not null) DEFAULT CHARSET=utf8"); stmt.close(); String thesql = "replace into tmptest(id, day) values(1, ?)"; rpst = conn.prepareStatement(thesql); rpst.setInt(1, day); rpst.execute(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (rs != null) rs.close(); if (rpst != null) rpst.close(); } catch (SQLException e) { } } } public static int testFetch(Connection conn) { PreparedStatement rpst = null; ResultSet rs = null; try { String thesql = "select day from tmptest where id = 1"; rpst = conn.prepareStatement(thesql); rs = rpst.executeQuery(); if (rs.next()) { int day = rs.getInt(1); System.out.println("got day: " + day); return day; } } catch (Exception e) { e.printStackTrace(); } finally { try { rs.close(); rpst.close(); } catch (SQLException e) { } } return 0; } } Suggested fix: for Connector/J 5.0.8, a quick and dirty patch diff -ur src.orig/com/mysql/jdbc/ResultSet.java src/com/mysql/jdbc/ResultSet.java --- src.orig/com/mysql/jdbc/ResultSet.java 2007-10-05 05:11:24.000000000 +0800 +++ src/com/mysql/jdbc/ResultSet.java 2008-05-08 18:51:42.000000000 +0800 @@ -2649,7 +2649,12 @@ if (!needsFullParse) { try { - return parseIntWithOverflowCheck(columnIndex, + int field_mysqltype = this.fields[columnIndex - 1].getMysqlType(); + if (field_mysqltype == MysqlDefs.FIELD_TYPE_DATE || field_mysqltype == MysqlDefs.FIELD_TYPE_NEWDATE) + return parseDateIntWithOverflowCheck(columnIndex, + intAsBytes, null); + else + return parseIntWithOverflowCheck(columnIndex, intAsBytes, null); } catch (NumberFormatException nfe) { try { @@ -7326,6 +7331,96 @@ return (int) valueAsDouble; } + public static int getDateInt(byte[] buf, int offset, int endPos) throws NumberFormatException { + int base = 10; + + int s = offset; + + /* Skip white space. */ + while (Character.isWhitespace((char) buf[s]) && (s < endPos)) { + ++s; + } + + if (s == endPos) { + throw new NumberFormatException(new String(buf)); + } + + /* Check for a sign. */ + boolean negative = false; + + if ((char) buf[s] == '-') { + negative = true; + ++s; + } else if ((char) buf[s] == '+') { + ++s; + } + + /* Save the pointer so we can check later if anything happened. */ + int save = s; + + int cutoff = Integer.MAX_VALUE / base; + int cutlim = (Integer.MAX_VALUE % base); + + if (negative) { + cutlim++; + } + + boolean overflow = false; + + int i = 0; + + for (; s < endPos; s++) { + char c = (char) buf[s]; + + if (Character.isDigit(c)) { + c -= '0'; + } else if (Character.isLetter(c)) { + c = (char) (Character.toUpperCase(c) - 'A' + 10); + } else { + continue; + } + + if (c >= base) { + break; + } + + /* Check for overflow. */ + if ((i > cutoff) || ((i == cutoff) && (c > cutlim))) { + overflow = true; + } else { + i *= base; + i += c; + } + } + + if (s == save) { + throw new NumberFormatException(new String(buf)); + } + + if (overflow) { + throw new NumberFormatException(new String(buf)); + } + + /* Return the result of the appropriate sign. */ + return (negative ? (-i) : i); + } + + private int parseDateIntWithOverflowCheck(int columnIndex, byte[] valueAsBytes, + String valueAsString) throws NumberFormatException, SQLException { + + if (valueAsBytes == null && valueAsString == null) { + return 0; + } + + if (valueAsBytes != null) { + return getDateInt(valueAsBytes, 0 , valueAsBytes.length); + } else { + valueAsString = valueAsString.trim(); + valueAsString.replace('-', ','); + return Integer.parseInt(valueAsString); + } + } + private int parseIntWithOverflowCheck(int columnIndex, byte[] valueAsBytes, String valueAsString) throws NumberFormatException, SQLException {