Details of the JDBC Interface

This section describes information specific to the JDBC driver of Denodo

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

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:

  1. Obtain the name of the subfields of a Struct object.

  2. 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