Mercurial > hg > monetdb-java

changeset 198:376a15ce49e3

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improve parsing negative years. Also updated and extended test program for those cases.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 14 Dec 2017 17:24:38 +0100 (2017-12-14)
parents 95b48b249f68
children e87d89d371f1
files src/main/java/nl/cwi/monetdb/jdbc/MonetResultSet.java tests/Test_Rtimedate.java
diffstat 2 files changed, 127 insertions(+), 110 deletions(-) [+]
line wrap: on
line diff
--- a/src/main/java/nl/cwi/monetdb/jdbc/MonetResultSet.java
+++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetResultSet.java
@@ -2638,12 +2638,14 @@ public class MonetResultSet
 		if (cal == null)
 			throw new IllegalArgumentException("No Calendar object given!");
 
+		final String monetDateStr;
 		final String monetDate;
 		final String MonetDBType;
 		int JdbcType;
+		boolean negativeYear = false;
 		try {
-			monetDate = tlp.values[columnIndex - 1];
-			if (monetDate == null) {
+			monetDateStr = tlp.values[columnIndex - 1];
+			if (monetDateStr == null) {
 				lastReadWasNull = true;
 				return -1;
 			}
@@ -2659,6 +2661,14 @@ public class MonetResultSet
 			{
 				JdbcType = type;
 			}
+
+			if ((JdbcType == Types.DATE || JdbcType == Types.TIMESTAMP) && monetDateStr.startsWith("-")) {
+				// the SimpleDateFormat parsers do not support to parse negative year numbers, deal with it separately
+				negativeYear = true;
+				monetDate = monetDateStr.substring(1);
+			} else {
+				monetDate = monetDateStr;
+			}
 		} catch (IndexOutOfBoundsException e) {
 			throw newSQLInvalidColumnIndexException(columnIndex);
 		}
@@ -2702,35 +2712,47 @@ public class MonetResultSet
 				}
 				timestampFormat.setTimeZone(ptz);
 				pdate = timestampFormat.parse(monetDate, ppos);
+				// if parsing with timestampFormat failed try to parse it in dateFormat
+				if (pdate == null && monetDate.length() <= 10 && monetDate.contains("-")) {
+					if (dateFormat == null) {
+						// first time usage, create and keep the dateFormat object for next usage
+						dateFormat = new SimpleDateFormat("yyyy-MM-dd");
+					}
+					dateFormat.setTimeZone(ptz);
+					pdate = dateFormat.parse(monetDate, ppos);
+				}
 				break;
 			default:
-				addWarning("unsupported data type", "01M03");
-				cal.clear();
-				return 0;
+				throw new SQLException("Internal error, unsupported data type: " + type, "01M03");
 		}
 		if (pdate == null) {
 			// parsing failed
+			String errMsg;
 			int epos = ppos.getErrorIndex();
 			if (epos == -1) {
-				addWarning("parsing '" + monetDate + "' failed", "01M10");
+				errMsg = "parsing '" + monetDateStr + "' failed";
 			} else if (epos < monetDate.length()) {
-				addWarning("parsing failed," +
-						 " found: '" + monetDate.charAt(epos) + "'" +
-						 " in: \"" + monetDate + "\"" +
-						 " at pos: " + epos, "01M10");
+				errMsg = "parsing failed," +
+					 " found: '" + monetDate.charAt(epos) + "'" +
+					 " in: \"" + monetDateStr + "\"" +
+					 " at pos: " + (epos + (negativeYear ? 2 : 1));
 			} else {
-				addWarning("parsing failed, expected more data after '" +
-						monetDate + "'", "01M10");
+				errMsg = "parsing failed, expected more data after '" +	monetDateStr + "'";
 			}
-			// default value
-			cal.clear();
-			return 0;
+			throw new SQLException(errMsg, "01M10");
 		}
+
 		cal.setTime(pdate);
-
-		int nanos = 0;
+		if (negativeYear) {
+			// System.out.println("Current cal: " + cal.toString());
+			// using cal.set(Calendar.YEAR, -(cal.get(Calendar.YEAR))); does not work. We must set the ERA instead
+			cal.set(Calendar.ERA, java.util.GregorianCalendar.BC);
+			// System.out.println("Corrected cal: " + cal.toString());
+		}
+
 		if (JdbcType == Types.TIME || JdbcType == Types.TIMESTAMP) {
 			// parse additional nanos (if any)
+			int nanos = 0;
 			int pos = ppos.getIndex();
 			char[] monDate = monetDate.toCharArray();
 			if (pos < monDate.length && monDate[pos] == '.') {
@@ -2760,12 +2782,13 @@ public class MonetResultSet
 							" in: \"" + monetDate + "\"" +
 							" at pos: " + e.getErrorOffset(), "01M10");
 					// default value
-					cal.clear();
 					nanos = 0;
 				}
 			}
+			return nanos;
 		}
-		return nanos;
+
+		return 0;
 	}
 
 	/**
--- a/tests/Test_Rtimedate.java
+++ b/tests/Test_Rtimedate.java
@@ -14,110 +14,67 @@ public class Test_Rtimedate {
 		Connection con = DriverManager.getConnection(args[0]);
 		Statement stmt = con.createStatement();
 		ResultSet rs = null;
-		//DatabaseMetaData dbmd = con.getMetaData();
 
 		con.setAutoCommit(false);
 		// >> false: auto commit should be off now
 		System.out.println("false\t" + con.getAutoCommit());
 
 		try {
-			stmt.executeUpdate("CREATE TABLE table_Test_Rtimedate ( id int, ts timestamp, t time, d date, vc varchar(30), PRIMARY KEY (id) )");
+			stmt.executeUpdate("CREATE TABLE table_Test_Rtimedate ( id int PRIMARY KEY, ts timestamp, t time, d date, vc varchar(30) )");
 
-			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, ts) VALUES (1, timestamp '2004-04-24 11:43:53.000')");
-			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, t) VALUES (2, time '11:43:53.000')");
+			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, ts) VALUES (1, timestamp '2004-04-24 11:43:53.123')");
+			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, t) VALUES (2, time '11:43:53.123')");
 			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, d) VALUES (3, date '2004-04-24')");
-
-			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, vc) VALUES (4, '2004-04-24 11:43:53.000000')");
+			// same values but now as strings to test string to timestamp / time / date object conversions
+			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, vc) VALUES (4, '2004-04-24 11:43:53.654321')");
 			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, vc) VALUES (5, '11:43:53')");
 			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, vc) VALUES (6, '2004-04-24')");
 
+			// test also with small years (< 1000) (see bug 6468)
+			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, ts) VALUES (11, timestamp '904-04-24 11:43:53.567')");
+			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, ts) VALUES (12, timestamp '74-04-24 11:43:53.567')");
+			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, ts) VALUES (13, timestamp '4-04-24 11:43:53.567')");
+			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, d) VALUES (14, date '904-04-24')");
+			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, d) VALUES (15, date '74-04-24')");
+			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, d) VALUES (16, date '4-04-24')");
+			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, vc) VALUES (17, '904-04-24 11:43:53.567')");
+			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, vc) VALUES (18, '74-04-24 11:43:53.567')");
+			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, vc) VALUES (19, '4-04-24 11:43:53.567')");
+
+			// test also with negative years (see bug 6468)
+			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, ts) VALUES (21, timestamp '-4-04-24 11:43:53.567')");	// negative year
+			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, ts) VALUES (22, timestamp '-2004-04-24 11:43:53.567')"); // negative year
+			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, d) VALUES (23, date '-4-04-24')");	// negative year
+			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, d) VALUES (24, date '-3004-04-24')");	// negative year
+			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, vc) VALUES (25, '-2004-04-24 11:43:53.654321')");	// negative year
+			stmt.executeUpdate("INSERT INTO table_Test_Rtimedate(id, vc) VALUES (26, '-3004-04-24')");	// negative year
+
 			rs = stmt.executeQuery("SELECT * FROM table_Test_Rtimedate");
 
-			rs.next();
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			// the next three should all go well
-			System.out.println("1. " + rs.getString("id") + ", " + rs.getString("ts") + ", " + rs.getTimestamp("ts"));
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			System.out.println("2. " + rs.getString("id") + ", " + rs.getString("ts") + ", " + rs.getTime("ts"));
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			System.out.println("3. " + rs.getString("id") + ", " + rs.getString("ts") + ", " + rs.getDate("ts"));
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			rs.next();
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			// the next two should go fine
-			System.out.println("4. " + rs.getString("id") + ", " + rs.getString("t") + ", " + rs.getTimestamp("t"));
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			System.out.println("5. " + rs.getString("id") + ", " + rs.getString("t") + ", " + rs.getTime("t"));
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			// this one should return 0
-			System.out.println("6. " + rs.getString("id") + ", " + rs.getString("t") + ", " + rs.getDate("t"));
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			rs.next();
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			// the next one passes
-			System.out.println("7. " + rs.getString("id") + ", " + rs.getString("d") + ", " + rs.getTimestamp("d"));
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			// this one should return 0
-			System.out.println("8. " + rs.getString("id") + ", " + rs.getString("d") + ", " + rs.getTime("d"));
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			// and this one should pass again
-			System.out.println("9. " + rs.getString("id") + ", " + rs.getString("d") + ", " + rs.getDate("d"));
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
+			readNextRow(rs, 1, "ts");
+			readNextRow(rs, 2, "t");
+			readNextRow(rs, 3, "d");
+
+			readNextRow(rs, 4, "vc");
+			readNextRow(rs, 5, "vc");
+			readNextRow(rs, 6, "vc");
 
-			// in the tests below a bare string is parsed
-			// everything will fail except the ones commented on
-			rs.next();
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			// timestamp -> timestamp should go
-			System.out.println("1. " + rs.getString("id") + ", " + rs.getString("vc") + ", " + rs.getTimestamp("vc"));
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			System.out.println("2. " + rs.getString("id") + ", " + rs.getString("vc") + ", " + rs.getTime("vc"));
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			// timestamp -> date goes because the begin is the same
-			System.out.println("3. " + rs.getString("id") + ", " + rs.getString("vc") + ", " + rs.getDate("vc"));
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			rs.next();
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			System.out.println("4. " + rs.getString("id") + ", " + rs.getString("vc") + ", " + rs.getTimestamp("vc"));
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			// time -> time should fit
-			System.out.println("5. " + rs.getString("id") + ", " + rs.getString("vc") + ", " + rs.getTime("vc"));
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			System.out.println("6. " + rs.getString("id") + ", " + rs.getString("vc") + ", " + rs.getDate("vc"));
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			rs.next();
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			System.out.println("7. " + rs.getString("id") + ", " + rs.getString("vc") + ", " + rs.getTimestamp("vc"));
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			System.out.println("8. " + rs.getString("id") + ", " + rs.getString("vc") + ", " + rs.getTime("vc"));
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
-			// date -> date should be fine
-			System.out.println("9. " + rs.getString("id") + ", " + rs.getString("vc") + ", " + rs.getDate("vc"));
-			readWarnings(rs.getWarnings());
-			rs.clearWarnings();
+			readNextRow(rs, 11, "ts");
+			readNextRow(rs, 12, "ts");
+			readNextRow(rs, 13, "ts");
+			readNextRow(rs, 14, "d");
+			readNextRow(rs, 15, "d");
+			readNextRow(rs, 16, "d");
+			readNextRow(rs, 17, "vc");
+			readNextRow(rs, 18, "vc");
+			readNextRow(rs, 19, "vc");
+
+			readNextRow(rs, 21, "ts");
+			readNextRow(rs, 22, "ts");
+			readNextRow(rs, 23, "d");
+			readNextRow(rs, 24, "d");
+			readNextRow(rs, 25, "vc");
+			readNextRow(rs, 26, "vc");
 
 			readWarnings(stmt.getWarnings());
 			readWarnings(con.getWarnings());
@@ -130,9 +87,46 @@ public class Test_Rtimedate {
 		con.close();
 	}
 
+	private static void readNextRow(ResultSet rs, int rowseq, String colnm) throws SQLException {
+		rs.next();
+		readWarnings(rs.getWarnings());
+		rs.clearWarnings();
+
+		// fetch the column value using multiple methods: getString(), getTimestamp(), getTime() and getDate()
+		// to test proper conversion and error reporting
+		String data = rs.getString("id") + ". " + colnm + " " + rs.getString(colnm) + " to ";
+
+		// getTimestamp() may raise a conversion warning when the value is of type Time or a String which doesn't match format yyyy-mm-dd hh:mm:ss
+		try {
+			System.out.println(data + "ts: " + rs.getTimestamp(colnm));
+		} catch (SQLException e) {
+			System.out.println("rs.getTimestamp(colnm) failed with error: " + e.getMessage());
+		}
+		readWarnings(rs.getWarnings());
+		rs.clearWarnings();
+
+		// getTime() may raise a conversion warning when the value is of type Date or a String which doesn't match format hh:mm:ss
+		try {
+			System.out.println(data + "tm: " + rs.getTime(colnm));
+		} catch (SQLException e) {
+			System.out.println("rs.getTime(colnm) failed with error: " + e.getMessage());
+		}
+		readWarnings(rs.getWarnings());
+		rs.clearWarnings();
+
+		// getDate() may raise a conversion warning when the value is of type Time or a String which doesn't match format yyyy-mm-dd
+		try {
+			System.out.println(data + "dt: " + rs.getDate(colnm));
+		} catch (SQLException e) {
+			System.out.println("rs.getDate(colnm) failed with error: " + e.getMessage());
+		}
+		readWarnings(rs.getWarnings());
+		rs.clearWarnings();
+	}
+
 	private static void readWarnings(SQLWarning w) {
 		while (w != null) {
-			System.out.println("warning: " + w.toString());
+			System.out.println("Warning: " + w.toString());
 			w = w.getNextWarning();
 		}
 	}