Changeset: 78253fdb3c3f for monetdb-java URL: https://dev.monetdb.org/hg/monetdb-java/rev/78253fdb3c3f Modified Files: ChangeLog src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java src/main/java/org/monetdb/jdbc/MonetPreparedStatement.java src/main/java/org/monetdb/jdbc/MonetResultSet.java tests/JDBC_API_Tester.java Branch: default Log Message:
Corrected returned SQL TypeName values for the MonetDB interval types: 'day_interval', 'month_interval' and 'sec_interval'. Those MonetDB type names can not be used in CREATE TABLE statements. Instead one has to use SQL type names: 'interval day', 'interval month' or 'interval second'. The JDBC driver now returns those SQL type names. This applies to methods: DatabaseMetaData.getTypeInfo() for the output column TYPE_NAME (the output column LOCAL_TYPE_NAME now returns the original type name), ResultSetMetaData.getColumnTypeName() and ParameterMetaData.getParameterTypeName(). diffs (281 lines): diff --git a/ChangeLog b/ChangeLog --- a/ChangeLog +++ b/ChangeLog @@ -1,6 +1,17 @@ # ChangeLog file for monetdb-java # This file is updated with Maddlog +* Thu Nov 3 2022 Martin van Dinther <[email protected]> +- Corrected returned SQL TypeName values for the MonetDB interval types: + 'day_interval', 'month_interval' and 'sec_interval'. Those MonetDB + type names can not be used in CREATE TABLE statements. Instead + one has to use SQL type names: 'interval day', 'interval month' or + 'interval second'. The JDBC driver now returns those SQL type names. + This applies to methods: DatabaseMetaData.getTypeInfo() for the output + column TYPE_NAME (the output column LOCAL_TYPE_NAME now returns + the original type name), ResultSetMetaData.getColumnTypeName() and + ParameterMetaData.getParameterTypeName(). + * Thu Oct 27 2022 Martin van Dinther <[email protected]> - Corrected PreparedStatement methods getMetaData().getColumnDisplaySize(), getMetaData().getPrecision() and getParameterMetaData().getPrecision(). diff --git a/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java b/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java --- a/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java +++ b/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java @@ -3035,9 +3035,11 @@ public final class MonetDatabaseMetaData */ @Override public ResultSet getTypeInfo() throws SQLException { - final StringBuilder query = new StringBuilder(2300); - query.append("SELECT \"sqlname\" AS \"TYPE_NAME\", " + - // TODO map 'day_interval' to 'interval day' (or 'interval day to second'), 'month_interval' to 'interval month' (or 'interval year to month'), 'sec_interval' to 'interval second' + final StringBuilder query = new StringBuilder(3200); + query.append("SELECT CASE \"sqlname\" WHEN 'day_interval' THEN 'interval day'" + + " WHEN 'month_interval' THEN 'interval month'" + + " WHEN 'sec_interval' THEN 'interval second'" + + " ELSE \"sqlname\" END AS \"TYPE_NAME\", " + "cast(").append(MonetDriver.getSQLTypeMap("\"sqlname\"")).append(" AS int) AS \"DATA_TYPE\", " + "\"digits\" AS \"PRECISION\", " + // note that when radix is 2 the precision shows the number of bits "cast(CASE WHEN \"sqlname\" IN ('char','varchar','sec_interval','day_interval','month_interval') THEN ''''" + @@ -3059,7 +3061,7 @@ public final class MonetDatabaseMetaData ",'day_interval','month_interval','sec_interval') THEN false ELSE true END AS \"UNSIGNED_ATTRIBUTE\", " + "CASE \"sqlname\" WHEN 'decimal' THEN true ELSE false END AS \"FIXED_PREC_SCALE\", " + "CASE WHEN \"sqlname\" IN ('tinyint','smallint','int','bigint') THEN true ELSE false END AS \"AUTO_INCREMENT\", " + - "\"systemname\" AS \"LOCAL_TYPE_NAME\", " + + "CASE WHEN \"sqlname\" IN ('sec_interval','day_interval','month_interval') THEN \"sqlname\" ELSE \"systemname\" END AS \"LOCAL_TYPE_NAME\", " + "cast(0 AS smallint) AS \"MINIMUM_SCALE\", " + "cast(CASE WHEN \"sqlname\" = 'decimal' THEN (CASE \"systemname\" WHEN 'int' THEN 9 WHEN 'lng' THEN 18 WHEN 'sht' THEN 4 WHEN 'hge' THEN 38 WHEN 'bte' THEN 2 ELSE 0 END)" + " WHEN \"sqlname\" IN ('time','timetz','timestamp','timestamptz','sec_interval') THEN 6 ELSE 0 END AS smallint) AS \"MAXIMUM_SCALE\", " + @@ -3069,6 +3071,7 @@ public final class MonetDatabaseMetaData "FROM \"sys\".\"types\" " + "ORDER BY \"DATA_TYPE\", \"sqlname\", \"id\""); + /* if (query.length() >= 3200) System.err.println("getTypeInfo(), extend query default size to: " + query.length()); */ return executeMetaDataQuery(query.toString()); } diff --git a/src/main/java/org/monetdb/jdbc/MonetPreparedStatement.java b/src/main/java/org/monetdb/jdbc/MonetPreparedStatement.java --- a/src/main/java/org/monetdb/jdbc/MonetPreparedStatement.java +++ b/src/main/java/org/monetdb/jdbc/MonetPreparedStatement.java @@ -775,7 +775,19 @@ public class MonetPreparedStatement @Override public String getColumnTypeName(final int column) throws SQLException { try { - return monetdbType[getColumnIdx(column)]; + final String monettype = monetdbType[getColumnIdx(column)]; + if (monettype.endsWith("_interval")) { + /* convert the interval type names to valid SQL data type names, + * such that generic applications can use them in create table statements + */ + if ("day_interval".equals(monettype)) + return "interval day"; + if ("month_interval".equals(monettype)) + return "interval month"; + if ("sec_interval".equals(monettype)) + return "interval second"; + } + return monettype; } catch (IndexOutOfBoundsException e) { throw MonetResultSet.newSQLInvalidColumnIndexException(column); } @@ -994,7 +1006,18 @@ public class MonetPreparedStatement @Override public String getParameterTypeName(final int param) throws SQLException { try { - return monetdbType[getParamIdx(param)]; + final String monettype = monetdbType[getParamIdx(param)]; + if (monettype.endsWith("_interval")) { + /* convert the interval type names to valid SQL data type names + */ + if ("day_interval".equals(monettype)) + return "interval day"; + if ("month_interval".equals(monettype)) + return "interval month"; + if ("sec_interval".equals(monettype)) + return "interval second"; + } + return monettype; } catch (IndexOutOfBoundsException e) { throw newSQLInvalidParameterIndexException(param); } diff --git a/src/main/java/org/monetdb/jdbc/MonetResultSet.java b/src/main/java/org/monetdb/jdbc/MonetResultSet.java --- a/src/main/java/org/monetdb/jdbc/MonetResultSet.java +++ b/src/main/java/org/monetdb/jdbc/MonetResultSet.java @@ -1752,13 +1752,13 @@ public class MonetResultSet { // these data types may have a variable scale, max scale is 38 - // Special handling for: day_interval and sec_interval as they - // are mapped to these column types (see MonetDriver typemap) + // Special handling for: day_interval and sec_interval as they are + // mapped to Types.NUMERIC and Types.DECIMAL types (see MonetDriver typeMap) // They appear to have a fixed scale (tested against Oct2020) final String monettype = getColumnTypeName(column); - if ("day_interval".equals(monettype)) + if ("interval day".equals(monettype)) return 0; - if ("sec_interval".equals(monettype)) + if ("interval second".equals(monettype)) return 3; if (scales != null) { @@ -1989,7 +1989,19 @@ public class MonetResultSet public String getColumnTypeName(final int column) throws SQLException { checkColumnIndexValidity(column); try { - return types[column - 1]; + final String monettype = types[column - 1]; + if (monettype.endsWith("_interval")) { + /* convert the interval type names to valid SQL data type names, + * such that generic applications can use them in create table statements + */ + if ("day_interval".equals(monettype)) + return "interval day"; + if ("month_interval".equals(monettype)) + return "interval month"; + if ("sec_interval".equals(monettype)) + return "interval second"; + } + return monettype; } catch (IndexOutOfBoundsException e) { throw MonetResultSet.newSQLInvalidColumnIndexException(column); } diff --git a/tests/JDBC_API_Tester.java b/tests/JDBC_API_Tester.java --- a/tests/JDBC_API_Tester.java +++ b/tests/JDBC_API_Tester.java @@ -63,6 +63,7 @@ final public class JDBC_API_Tester { jt.Test_DBCmetadata(); jt.Test_FetchSize(); jt.Test_Int128(); + jt.Test_Interval_Types(); jt.Test_PlanExplainTraceDebugCmds(); jt.Test_PSgeneratedkeys(); jt.Test_PSgetObject(); @@ -1484,6 +1485,84 @@ final public class JDBC_API_Tester { "SUCCESS\n"); } + private void Test_Interval_Types() { + sb.setLength(0); // clear the output log buffer + + Statement stmt = null; + ResultSet rs = null; + PreparedStatement pstmt = null; + try { + stmt = con.createStatement(); + stmt.executeUpdate("create table Test_Interval_Types (c1 interval day, c2 interval month, c3 interval second)"); + rs = stmt.executeQuery("select * from Test_Interval_Types"); + if (rs != null) { + sb.append("Showing query ResultSetMetaData\n"); + ResultSetMetaData rsmd = rs.getMetaData(); + int colCount = rsmd.getColumnCount(); + for (int col = 1; col <= colCount; col++) { + sb.append("ColumnName: ").append(rsmd.getColumnName(col)) + .append("\tColumnTypeName: ").append(rsmd.getColumnTypeName(col)) + .append("\tPrecision: ").append(rsmd.getPrecision(col)) + .append("\tScale: ").append(rsmd.getScale(col)) + .append("\tColumnDisplaySize: ").append(rsmd.getColumnDisplaySize(col)) + .append("\tColumnType: ").append(rsmd.getColumnType(col)) + .append("\tColumnClassName: ").append(rsmd.getColumnClassName(col)) + .append("\n"); + } + rs.close(); + rs = null; + } + pstmt = con.prepareStatement("select * from Test_Interval_Types where c1 = ? or c2 = ? or c3 = ?"); + if (pstmt != null) { + sb.append("Showing prepared query ResultSetMetaData\n"); + ResultSetMetaData rsmd = pstmt.getMetaData(); + int colCount = rsmd.getColumnCount(); + for (int col = 1; col <= colCount; col++) { + sb.append("ColumnName: ").append(rsmd.getColumnName(col)) + .append("\tColumnTypeName: ").append(rsmd.getColumnTypeName(col)) + .append("\tPrecision: ").append(rsmd.getPrecision(col)) + .append("\tScale: ").append(rsmd.getScale(col)) + .append("\tColumnDisplaySize: ").append(rsmd.getColumnDisplaySize(col)) + .append("\tColumnType: ").append(rsmd.getColumnType(col)) + .append("\tColumnClassName: ").append(rsmd.getColumnClassName(col)) + .append("\n"); + } + sb.append("Showing prepared query ParameterMetaData\n"); + ParameterMetaData pmd = pstmt.getParameterMetaData(); + int paramCount = pmd.getParameterCount(); + for (int param = 1; param <= paramCount; param++) { + sb.append("ParameterTypeName: ").append(pmd.getParameterTypeName(param)) + .append("\tPrecision: ").append(pmd.getPrecision(param)) + .append("\tScale: ").append(pmd.getScale(param)) + .append("\tParameterType: ").append(pmd.getParameterType(param)) + .append("\tParameterClassName: ").append(pmd.getParameterClassName(param)) + .append("\n"); + } + pstmt.close(); + pstmt = null; + } + stmt.executeUpdate("drop table Test_Interval_Types"); + } catch (SQLException e) { + sb.append("FAILED: ").append(e.getMessage()).append("\n"); + } + closeStmtResSet(pstmt, null); + closeStmtResSet(stmt, rs); + + compareExpectedOutput("Test_Interval_Types", + "Showing query ResultSetMetaData\n" + + "ColumnName: c1 ColumnTypeName: interval day Precision: 4 Scale: 0 ColumnDisplaySize: 5 ColumnType: 2 ColumnClassName: java.math.BigDecimal\n" + + "ColumnName: c2 ColumnTypeName: interval month Precision: 10 Scale: 0 ColumnDisplaySize: 1 ColumnType: 4 ColumnClassName: java.lang.Integer\n" + + "ColumnName: c3 ColumnTypeName: interval second Precision: 13 Scale: 3 ColumnDisplaySize: 5 ColumnType: 3 ColumnClassName: java.math.BigDecimal\n" + + "Showing prepared query ResultSetMetaData\n" + + "ColumnName: c1 ColumnTypeName: interval day Precision: 4 Scale: 0 ColumnDisplaySize: 4 ColumnType: 2 ColumnClassName: java.math.BigDecimal\n" + + "ColumnName: c2 ColumnTypeName: interval month Precision: 10 Scale: 0 ColumnDisplaySize: 10 ColumnType: 4 ColumnClassName: java.lang.Integer\n" + + "ColumnName: c3 ColumnTypeName: interval second Precision: 13 Scale: 0 ColumnDisplaySize: 13 ColumnType: 3 ColumnClassName: java.math.BigDecimal\n" + + "Showing prepared query ParameterMetaData\n" + + "ParameterTypeName: interval day Precision: 4 Scale: 0 ParameterType: 2 ParameterClassName: java.math.BigDecimal\n" + + "ParameterTypeName: interval month Precision: 10 Scale: 0 ParameterType: 4 ParameterClassName: java.lang.Integer\n" + + "ParameterTypeName: interval second Precision: 13 Scale: 0 ParameterType: 3 ParameterClassName: java.math.BigDecimal\n"); + } + private void Test_PlanExplainTraceDebugCmds() { sb.setLength(0); // clear the output log buffer @@ -5904,13 +5983,41 @@ final public class JDBC_API_Tester { } private void compareExpectedOutput(String testname, String expected) { - if (!expected.equals(sb.toString())) { + final String produced = sb.toString(); + if (!expected.equals(produced)) { foundDifferences = true; System.err.print("Test '"); System.err.print(testname); if (!testname.endsWith(")") && !testname.endsWith(";")) System.err.print("()"); System.err.println("' produced different output!"); + int expLen = expected.length(); + int prodLen = produced.length(); + int max_pos = expLen; + if (prodLen > max_pos) + max_pos = prodLen; + int line = 1; + int rowpos = 0; + for (int pos = 0; pos < max_pos; pos++) { + char a = (pos < expLen ? expected.charAt(pos) : '~'); + char b = (pos < prodLen ? produced.charAt(pos) : '~'); + if (a == '\n') { + line++; + rowpos = 0; + } + rowpos++; + if (a != b) { + if (pos + 30 < expLen) + expLen = pos + 30; + if (pos + 30 < prodLen) + prodLen = pos + 30; + System.err.println("Difference found at line " + line + " position " + rowpos + + ": Expected \"" + expected.substring(pos < expLen ? pos : expLen-1, expLen-1) + + "\" but gotten \"" + produced.substring(pos < prodLen ? pos : prodLen-1, prodLen-1) + "\""); + pos = max_pos; + } + } + System.err.println(); System.err.println("Expected:"); System.err.println(expected); System.err.println("Gotten:"); _______________________________________________ checkin-list mailing list -- [email protected] To unsubscribe send an email to [email protected]
