Details of the JDBC Interface¶

This section describes information specific to the JDBC driver of Denodo

Description of Views and Their Fields
Retrieving the Content Type of Blob Values
Working with Datetime Values with the Denodo JDBC Driver
- Setting Datetime Values on Parameters of Prepared Statements
- How the Driver Reports the Datetime and Interval Types
Obtaining the Names of Elements Inside a Struct (Register)
Connecting from Applications that Filter Incoming Serialization Data
Queries with Datetime Literals

Description of Views and Their Fields¶

The driver publishes the description of the views and their fields in the column “REMARKS” of the metadata of tables/views and their fields.

Retrieving the Content Type of Blob Values¶

The driver makes available the content type of blob fields. The example below shows how to do it.

Retrieving the content type of a blob value¶

ResultSet rs = stmt.executeQuery(...);
...
com.denodo.vdb.jdbcdriver.VDBJDBCBlob blob =
    (com.denodo.vdb.jdbcdriver.VDBJDBCBlob) rs.getBlob(index);
String contentType = blob.getContentType();

Working with Datetime Values with the Denodo JDBC Driver¶

The following subsections provide information about how to use the JDBC driver to work with the different datetime values.

Setting Datetime Values on Parameters of Prepared Statements
How the Driver Reports the Datetime and Interval Types

Setting Datetime Values on Parameters of Prepared Statements¶

The table below lists the method of the class PreparedStatement you have to invoke to set the value of a parameter (“?”), depending on the type of the value.

Methods to set datetime parameter on a PreparedStatement¶
Type	Method(s) to Set a Parameter of that Type on a PreparedStatement
localdate	Any of these: PreparedStatement.setObject(java.time.LocalDate) PreparedStatement.setDate(java.sql.Date) For example: // Creating a LocalDate object setObject(1, java.time.LocalDate.of(2018,01,15)); // Creating a Date object setDate(1, java.sql.Date.valueOf("2018-01-15")); According to the documentation of the class java.sql.Date, when using “setDate”, the “Date” object must be “normalized” by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated. Because of this, using the LocalDate object is simpler.
time	Any of these: PreparedStatement.setObject(java.time.LocalTime) PreparedStatement.setTime(java.sql.Time) For example: // Creating a LocalTime object setObject(1, LocalTime.of(11, 58, 59, 123000000)); // Creating a java.sql.Time object setTime(2, java.sql.Time.valueOf("11:58:59")); According to the documentation of the class java.sql.Time, when using “setTime”, the date components of the Time object should be set to the “zero epoch” value of January 1, 1970. Because of this, using the LocalTime object is simpler.
timestamp	PreparedStatement.setObject(java.time.LocalDateTime) For example: setObject(1, java.time.LocalDateTime.of( 2018, 01, 15, 23, 58, 59, 256000000)) The last parameter represents 256 milliseconds because the value is in nanoseconds. In Denodo the maximum precision for timestamp,timestamptz and time is milliseconds, not nanoseconds. PreparedStatement.setTimeStamp() must only be used with timestamptz values. Otherwise, the query will fail unless the query has a cast from timestamptz to timestamp; and even if you have it, in order for this to work, the parameter i18n of the connection URI has to match the i18n setting of the Denodo server.
date (deprecated)	The same as for timestamptz (see below)
timestamptz	Any of these: PreparedStatement.setObject(java.time.OffsetDateTime) PreparedStatement.setTimestamp(java.sql.Timestamp) For example: // Creating an OffsetDateTime object setObject(1, OffsetDateTime.parse( "2018-01-01T21:15:00+01:00")) // Creating a Timestamp object SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss XXX"); sdf.setTimeZone("GMT"); setTimestamp( 1 , sdf.parse("1982-12-13 01:59:59 +0000"));
interval_year_month	setObject(java.time.Period) For example: // Equivalent to INTERVAL '145-11' YEAR TO MONTH setObject(1, Period.ofYears(145).plusMonths(11)); // Equivalent to INTERVAL '145' YEAR setObject(Period.ofYears (145));
interval_day_second	setObject(java.time.Duration) For example: // Equivalent to INTERVAL '4 5:12' DAY TO MINUTE setObject(Duration.ofDays(4).plusHours(5).plusMinutes(12)); // Equivalent to INTERVAL '4 5:12:10.222' DAY TO SECOND setObject(Duration.parse("P4DT5H12M10.222S"));

How the Driver Reports the Datetime and Interval Types¶

The tables below list how the JDBC driver reports each datetime type.

Type Name in Denodo	Type Name Returned by the Method ResultSetMetaData.getColumnTypeName()	Value Returned by the Method ResultSetMetaData.getColumnType(int)
localdate	DATE	91
time	TIME	92
timestamp	TIMESTAMP	93
date (deprecated)	TIMESTAMP_WITH_TIMEZONE	2014
timestamptz	TIMESTAMP_WITH_TIMEZONE	2014
interval_year_month	INTERVAL_YEAR_MONTH	2020
interval_day_second	INTERVAL_DAY_SECOND	2021

The types date and timestamptz are reported with the same type (TIMESTAMP WITH TIMEZONE) so a client application cannot distinguish them. This is intentional, to facilitate the migration from versions prior to Denodo 7.0. Client applications do not need to distinguish between these types and treat both as timestamptz.

The codes for the types interval_year_month and interval_day_second are defined by Denodo because they are not part of the JDBC API.

Type Name in Denodo	Result of ResultSetMetaData.getColumnClassName(int)	Java Class of the Objects Returned by the class ResultSet.getObject()
localdate	java.sql.Date	java.sql.Date
time	java.sql.Time	java.sql.Time
timestamp	java.sql.Timestamp	java.sql.Timestamp
date (deprecated)	java.sql.Timestamp	java.sql.Timestamp
timestamptz	java.sql.Timestamp	java.sql.Timestamp
interval_year_month	java.lang.Long	java.lang.Long Invoke `java.time.Period.ofMonths (value)` to convert this value into a `java.time.Period` object. To obtain a `Duration` object from the driver, invoke `ResultSet.getObject(col, java.time.Period.class)`.
interval_day_second	java.lang.Long	java.lang.Long Invoke `java.time.Duration. ofMillis(value)` to convert this value into a `java.time.Duration` object. To obtain a `Duration` object from the driver, invoke `ResultSet.getObject(col, java.time.Duration.class)`.

Obtaining the Names of Elements Inside a Struct (Register)¶

The JDBC driver of Denodo transforms compound values to classes of the JDBC API:

Converts values of type register to java.sql.Struct objects.
Converts values of type array to java.sql.Array objects.
java.sql.Array objects are arrays of Struct objects.

The standard JDBC API provides methods to obtain the values inside java.sql.Struct objects (i.e. inside register fields). However, it does not offer any way of obtaining the name of the subfields of a Struct or obtaining these values by the name of the subfield.

This section explains how to, using the Denodo JDBC driver:

Obtain the name of the subfields of a Struct object.
Obtain a value of a subfield by its name, instead of its position inside the register.

For example, let us say that you have an application that executes a query that returns a register field whose subfields are last_name and first_name. For each row, the result set returns a Struct object. To obtain the values of each Struct object, the application has to invoke the method Struct.getAttributes(), which returns an array of two values: the last name and the first name. If later, you modify this register to add a subfield (e.g. telephone), the array returned by the method Struct.getAttributes() will have three elements instead of two. In addition, if the first element of the array is now the telephone and not the last name, the application will obtain invalid data.

To avoid this sort of maintainability issues you may want to use the classes of the Denodo JDBC API to obtain the values of a Struct by name and not by its position in the register. This will make your application more robust to changes.

The example below shows how to do this.

Obtaining the name of a value inside a Struct object¶

 import com.denodo.vdb.jdbcdriver.printer.Field;
 import com.denodo.vdb.jdbcdriver.VDBJDBCResultSetMetaData;
 import com.denodo.vdb.vdbinterface.common.clientResult.vo.descriptions.type.RegisterVO;
 import com.denodo.vdb.vdbinterface.common.clientResult.vo.descriptions.type.RegisterValueVO;

 ...

 public static void main(String[] args)
         throws Exception {

     /*
      * The method getConnection() returns a Connection to Virtual
      * DataPort
      */
     Connection connection = getConnection();
     Statement st = connection.createStatement();
     String query = "SELECT * FROM view_with_compound_fields";
     ResultSet rs = st.executeQuery(query);

     /*
      * The classes 'VDBJDBCResultSetMetaData' and 'Field' are part
      * of the Denodo JDBC API. They do not belong to the standard
      * JDBC API.
      */
     VDBJDBCResultSetMetaData metaData =
             (VDBJDBCResultSetMetaData) rs.getMetaData();
     Field[] fields = metaData.getFields();
     while (rs.next()) {

         int columnCount = metaData.getColumnCount();
         for (int i = 1; i <= columnCount; i++) {

             Object value = rs.getObject(i);
             if (value != null) {

                 if (metaData.getColumnType(i) == Types.STRUCT) {
                     /*
                      * The JDBC API represents the values of type
                      * 'register' as 'Struct' objects.
                      */

                     /*
                      * The classes 'RegisterVO' and
                      * 'RegisterValueVO' are part of the Denodo JDBC
                      * API. They do not belong to the standard Java
                      * API.
                      */
                     RegisterVO vdpType =
                        ((RegisterVO) fields[i - 1].getVdpType());
                     List<RegisterValueVO> registerSubTypes =
                         vdpType.getElements();
                     Struct struct = (Struct) value;
                     Object[] structValues = struct.getAttributes();
                     String firstName = null, lastName = null;
                     for (int j=0; j < registerSubTypes.size(); j++) {
                         /*
                          * The variable 'registerSubTypes'
                          * contains the names of the names of the
                          * subfields.
                          */

                         String subFieldName =
                             registerSubTypes.get(j).getName();
                         switch (subFieldName) {
                         case "first_name":
                             firstName = (String) structValues[j];
                             break;

                         case "last_name":
                             lastName = (String) structValues[j];
                             break;
                         }
                         /*
                          * ...
                          */
                     }
                 } else if (metaData.getColumnType(i)==Types.ARRAY) {
                     /*
                      * The JDBC API represents the values of type
                      * 'array' as 'Array' objects.
                      */
                     Object[] register =
                         (Object[]) rs.getArray(i).getArray();
                     for (Object o : register) {
                         /*
                          * In the Denodo JDBC API, the content of an
                          * 'Array' is an array of 'Struct' objects.
                          */

                         Struct s = (Struct) o;
                         /*
                          * ...
                          */
                     }
                 } // else ...
             }
         }
     }

     /*
      * Close ResultSet, Statement and Connection.
      */
 }

Connecting from Applications that Filter Incoming Serialization Data¶

The Denodo JDBC driver uses Java Remote Method Invocation (RMI) to connect to Virtual DataPort. Some client applications (e.g. the latest versions of Oracle WebLogic Server) include a mechanism that filters data received using RMI. This mechanism increases the security of the client application. However, the default configuration of this filter in the application may prevent the application from receiving data from Denodo.

If you are connecting to Denodo from an application that has this filter enabled, the administrator of the application has to add the following to the parameter that controls this filter.

com.denodo.**

The configuration of this filter depends on the client application.

Find more information about this in the official specification JEP 290: Filter Incoming Serialization Data

Queries with Datetime Literals¶

When the escape processing is enabled in a JDBC statement, the Denodo JDBC driver interprets the JDBC escape syntax for timestamp, date, and time literals equivalent to the correct VQL syntax for timestamps. This is, the following syntax for timestamp literals:

{ts 'yyyy-mm-dd hh:mm:ss.f...'}

the following syntax for date literals:

{d 'yyyy-mm-dd'}

and the following syntax for time literals:

{t 'hh:mm:ss'}

For example this query

SELECT *
FROM my_view
WHERE time_field = {t '20:00:03'}
  AND date_field = {d '1999-01-09'}
      AND timestamp_field = {ts '1999-01-09 20:11:11.123455'}

will be processed by the JDBC driver and sent to Virtual DataPort as this:

SELECT *
FROM my_view
WHERE time_field = TIME '20:00:03'
  AND date_field = DATE '1999-01-09'
      AND timestamp_field = TIMESTAMP '1999-01-09 20:11:11.123455'

The driver supports the escape syntax for datetime literals, not for functions.

USER MANUALS