Mercurial > hg > monetdb-java

changeset 415:50e43af49d47

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improved DatabaseMetaData.getTypeInfo() output for temporal data types: sec_interval, day_interval, month_interval, date, time, timetz, timestamp and timestamptz.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Fri, 15 Jan 2021 00:50:29 +0100 (2021-01-14)
parents 1e278695fe54
children b3c876a0d61f
files ChangeLog src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java src/main/java/org/monetdb/jdbc/MonetDriver.java.in src/main/java/org/monetdb/jdbc/MonetResultSet.java
diffstat 4 files changed, 46 insertions(+), 28 deletions(-) [+]
line wrap: on
line diff
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,6 +1,11 @@
 # ChangeLog file for monetdb-java
 # This file is updated with Maddlog
 
+* Thu Jan 14 2021 Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
+- Improved DatabaseMetaData.getTypeInfo() output for temporal data
+  types: sec_interval, day_interval, month_interval, date, time, timetz,
+  timestamp and timestamptz.
+
 * Wed Jan  6 2021 Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
 - Corrected output of resultset columns UPDATE_RULE and DELETE_RULE
   when calling DatabaseMetaData API methods getImportedKeys() or
@@ -24,9 +29,9 @@
    nl.cwi.monetdb.mcl.net.MapiSocket
    nl.cwi.monetdb.client.JdbcClient
   They are implemented as simple wrappers of their org.monetdb.* equivalents.
-  Note: These nl.cwi.monetdb.* classes are now marked as deprecated and may be
-  removed in a future release. If you still use them in your Java code,
-  update them to use the new package names.
+  Note: These nl.cwi.monetdb.* classes are now marked as deprecated and may
+  be removed in a future release. If you still use them in your Java code or
+  configuration files, update them to use the new package names.
 
 * Thu Oct 29 2020 Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
 - Extended JdbcClient program with 3 new commands to quickly validate
@@ -42,11 +47,11 @@
   - Column NOT NULL constraint
   - Varchar(n) max length constraint
   - Idem for char(n), clob(n), blob(n), json(n) and url(n).
-  It can be usefull to run \vsci before and after an upgrade.
+  It can be usefull to run \vsci before and after an upgrade of MonetDB server.
   Use \vsi my_schema  to validate data in all tables of a specific schema.
   Use \vdbi  to validate integrity of data in all user schemas in
-  the database. Note this can take a while, depending on your number
-  of user schemas and tables sizes.  Despite being tested on several
+  the database. Note: this can take a while, depending on your number
+  of user schemas, tables and tables sizes.  Despite being tested on several
   internal dbs the functionality is still beta, so you can get false
   errors reported. If you encounter these let us know asap.
 
@@ -54,6 +59,8 @@
 - Improved performance of ResultSetMetaData methods isAutoIncrement(),
   getPrecision() and getScale() significantly for columns of specific data
   types as in some cases no costly meta data query is executed anymore.
+
+* Thu Oct  8 2020 Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
 - The connection properties  treat_clob_as_varchar  and  treat_blob_as_binary
   are now set to true by default within the JDBC driver.  This is done
   as it results by default in less memory usage, (much) faster response
@@ -74,6 +81,8 @@
   - adding 3 methods to MonetPreparedStatement
   - adding 4 methods to MonetResultSet
   - adding 8 methods to MonetStatement
+
+* Wed Sep 23 2020 Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
 - Corrected MonetDatabaseMetaData.getTypeInfo()
   - The LITERAL_PREFIX column now includes the required casting name for
     types: clob, inet, json, url, uuid and blob.
--- a/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java
+++ b/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java
@@ -1773,7 +1773,7 @@ public class MonetDatabaseMetaData
 				" WHEN 'hugeint' THEN 16 WHEN 'oid' THEN 8 WHEN 'wrd' THEN 8 ELSE a.\"type_digits\" END AS \"LENGTH\", " +
 			"cast(CASE WHEN a.\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric'," +
 				"'time','timetz','timestamp','timestamptz','day_interval','month_interval','sec_interval') THEN a.\"type_scale\" ELSE NULL END AS smallint) AS \"SCALE\", " +
-			"cast(CASE WHEN a.\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric') THEN 10" +
+			"cast(CASE WHEN a.\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric','day_interval','month_interval','sec_interval') THEN 10" +
 				" WHEN a.\"type\" IN ('real','float','double') THEN 2 ELSE NULL END AS smallint) AS \"RADIX\", " +
 			"cast(").append(DatabaseMetaData.procedureNullableUnknown).append(" AS smallint) AS \"NULLABLE\", " +
 			"cast(null as char(1)) AS \"REMARKS\", " +
@@ -2940,12 +2940,14 @@ public class MonetDatabaseMetaData
 	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'
 			"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') THEN ''''" +
-				" WHEN \"sqlname\" IN ('clob','inet','json','url','uuid','blob','sqlblob') THEN \"sqlname\"||' '''" +
-				" ELSE NULL END AS varchar(9)) AS \"LITERAL_PREFIX\", " +
-			"cast(CASE WHEN \"systemname\" IN ('str','inet','json','url','uuid','blob','sqlblob') THEN ''''" +
+			"cast(CASE WHEN \"sqlname\" IN ('char','varchar','sec_interval','day_interval','month_interval') THEN ''''" +
+				" WHEN \"sqlname\" IN ('clob','inet','json','url','uuid','date','time','timetz','timestamp','timestamptz','blob','sqlblob') THEN \"sqlname\"||' '''" +
+				" ELSE NULL END AS varchar(16)) AS \"LITERAL_PREFIX\", " +
+			"cast(CASE WHEN \"sqlname\" IN ('char','varchar','sec_interval','day_interval','month_interval'" +
+						",'clob','inet','json','url','uuid','date','time','timetz','timestamp','timestamptz','blob','sqlblob') THEN ''''" +
 				" ELSE NULL END AS varchar(2)) AS \"LITERAL_SUFFIX\", " +
 			"CASE WHEN \"sqlname\" IN ('char','varchar') THEN 'max length'" +
 				" WHEN \"sqlname\" = 'decimal' THEN 'precision, scale'" +
@@ -2956,7 +2958,8 @@ public class MonetDatabaseMetaData
 			"CASE WHEN \"systemname\" IN ('str','json','url') THEN true ELSE false END AS \"CASE_SENSITIVE\", " +
 			"cast(CASE WHEN \"systemname\" IN ('str','inet','json','url','uuid') THEN ").append(DatabaseMetaData.typeSearchable)
 				.append(" ELSE ").append(DatabaseMetaData.typePredBasic).append(" END AS smallint) AS \"SEARCHABLE\", " +
-			"CASE WHEN \"sqlname\" IN ('tinyint','smallint','int','bigint','hugeint','decimal','real','double','day_interval','month_interval','sec_interval') THEN false ELSE true END AS \"UNSIGNED_ATTRIBUTE\", " +
+			"CASE WHEN \"sqlname\" IN ('tinyint','smallint','int','bigint','hugeint','decimal','real','double'" +
+				",'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\", " +
@@ -2965,7 +2968,7 @@ public class MonetDatabaseMetaData
 				" WHEN \"sqlname\" IN ('time','timetz','timestamp','timestamptz','sec_interval') THEN 6 ELSE 0 END AS smallint) AS \"MAXIMUM_SCALE\", " +
 			"cast(0 AS int) AS \"SQL_DATA_TYPE\", " +
 			"cast(0 AS int) AS \"SQL_DATETIME_SUB\", " +
-			"cast(\"radix\" as int) AS \"NUM_PREC_RADIX\" " +
+			"cast(CASE WHEN \"sqlname\" IN ('time','timetz','timestamp','timestamptz','sec_interval') THEN 10 ELSE \"radix\" END AS int) AS \"NUM_PREC_RADIX\" " +
 		"FROM \"sys\".\"types\" " +
 		"ORDER BY \"DATA_TYPE\", \"sqlname\", \"id\"");
 
@@ -3709,17 +3712,13 @@ public class MonetDatabaseMetaData
 
 	/**
 	 * Retrieves a list of the client info properties that the driver
-	 * supports. The result set contains the following columns
-	 *
+	 * supports. The result set contains the following columns:
 	 *    1. NAME String =&gt; The name of the client info property
-	 *    2. MAX_LEN int =&gt; The maximum length of the value for the
-	 *       property
-	 *    3. DEFAULT_VALUE String =&gt; The default value of the
-	 *       property
-	 *    4. DESCRIPTION String =&gt; A description of the
-	 *       property. This will typically contain information as
-	 *       to where this property is stored in the database.
-	 *
+	 *    2. MAX_LEN int =&gt; The maximum length of the value for the property
+	 *    3. DEFAULT_VALUE String =&gt; The default value of the property
+	 *    4. DESCRIPTION String =&gt; A description of the property.
+	 *       This will typically contain information as to
+	 *       where this property is stored in the database.
 	 * The ResultSet is sorted by the NAME column
 	 *
 	 * @return A ResultSet object; each row is a supported client info property
@@ -3738,8 +3737,8 @@ public class MonetDatabaseMetaData
 		"SELECT 'debug', 5, 'false', 'boolean flag true or false' UNION ALL " +
 		"SELECT 'logfile', 1024, 'monet_######.log', 'name of logfile used when debug is enabled' UNION ALL " +
 		"SELECT 'hash', 128, '', 'hash methods list to use in server connection. Supported are SHA512, SHA384, SHA256 and SHA1' UNION ALL " +
-		"SELECT 'treat_blob_as_binary', 5, 'false', 'should blob columns be mapped to Types.VARBINARY instead of default Types.BLOB in ResultSets and PreparedStatements' UNION ALL " +
-		"SELECT 'treat_clob_as_varchar', 5, 'false', 'should clob columns be mapped to Types.VARCHAR instead of default Types.CLOB in ResultSets and PreparedStatements' UNION ALL " +
+		"SELECT 'treat_blob_as_binary', 5, 'true', 'should blob columns be mapped to Types.VARBINARY instead of default Types.BLOB in ResultSets and PreparedStatements' UNION ALL " +
+		"SELECT 'treat_clob_as_varchar', 5, 'true', 'should clob columns be mapped to Types.VARCHAR instead of default Types.CLOB in ResultSets and PreparedStatements' UNION ALL " +
 		"SELECT 'so_timeout', 10, '0', 'timeout (in milliseconds) of communication socket. 0 means no timeout is set' " +
 		"ORDER BY \"NAME\"";
 
@@ -3917,7 +3916,7 @@ public class MonetDatabaseMetaData
 				" WHEN 'hugeint' THEN 16 WHEN 'oid' THEN 8 WHEN 'wrd' THEN 8 ELSE a.\"type_digits\" END AS \"LENGTH\", " +
 			"cast(CASE WHEN a.\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric'," +
 				"'time','timetz','timestamp','timestamptz','day_interval','month_interval','sec_interval') THEN a.\"type_scale\" ELSE NULL END AS smallint) AS \"SCALE\", " +
-			"cast(CASE WHEN a.\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric') THEN 10" +
+			"cast(CASE WHEN a.\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric','day_interval','month_interval','sec_interval') THEN 10" +
 				" WHEN a.\"type\" IN ('real','float','double') THEN 2 ELSE NULL END AS smallint) AS \"RADIX\", " +
 			"cast(").append(DatabaseMetaData.functionNullableUnknown).append(" AS smallint) AS \"NULLABLE\", " +
 			"cast(null as char(1)) AS \"REMARKS\", " +
--- a/src/main/java/org/monetdb/jdbc/MonetDriver.java.in
+++ b/src/main/java/org/monetdb/jdbc/MonetDriver.java.in
@@ -334,7 +334,7 @@ public class MonetDriver implements Driv
 		typeMap.put("char", Integer.valueOf(Types.CHAR));
 		typeMap.put("clob", Integer.valueOf(Types.CLOB));
 		typeMap.put("date", Integer.valueOf(Types.DATE));
-		typeMap.put("day_interval", Integer.valueOf(Types.BIGINT));	// New as of Oct2020 release
+		typeMap.put("day_interval", Integer.valueOf(Types.NUMERIC));	// New as of Oct2020 release
 		typeMap.put("decimal", Integer.valueOf(Types.DECIMAL));
 		typeMap.put("double", Integer.valueOf(Types.DOUBLE));
 		// typeMap.put("geometry", Integer.valueOf(Types.???));
--- a/src/main/java/org/monetdb/jdbc/MonetResultSet.java
+++ b/src/main/java/org/monetdb/jdbc/MonetResultSet.java
@@ -1636,7 +1636,16 @@ public class MonetResultSet
 				switch (getColumnType(column)) {
 					case Types.DECIMAL:
 					case Types.NUMERIC:
-						// these data types may have a scale, max scale is 38
+					{
+						// special handling for: day_interval and sec_interval as these are mapped to these result types (see MonetDriver typemap)
+						// they appear to have a fixed scale (tested against Oct2020)
+						final String monettype = getColumnTypeName(column);
+						if ("day_interval".equals(monettype))
+							return 0;
+						if ("sec_interval".equals(monettype))
+							return 3;
+
+						// these data types may have a variable scale, max scale is 38
 						try {
 							if (_is_fetched[column] != true) {
 								fetchColumnInfo(column);
@@ -1645,6 +1654,7 @@ public class MonetResultSet
 						} catch (IndexOutOfBoundsException e) {
 							throw MonetResultSet.newSQLInvalidColumnIndexException(column);
 						}
+					}
 					case Types.TIME:
 					case Types.TIME_WITH_TIMEZONE:
 					case Types.TIMESTAMP: