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.

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.

// 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"));

// 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"));

setObject(1, java.time.LocalDateTime.of(
    2018, 01, 15, 23, 58, 59, 256000000))

// 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"));

// Equivalent to INTERVAL '145-11' YEAR TO MONTH
setObject(1, Period.ofYears(145).plusMonths(11));

// Equivalent to INTERVAL '145' YEAR
setObject(Period.ofYears (145));

// 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"));

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.

 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'

Get Auto-Generated Keys in IDU Statements¶

The JDBC driver supports retrieving the values that the underlying databases generate when inserting a row. Often, this auto-generated value is the primary key of the new row.

For example:

Statement stmt = conn.createStatement();
/* With "Statement.RETURN_GENERATED_KEYS", this code requests Virtual DataPort
   to return the auto-generated values. */
int rows = stmt.executeUpdate("INSERT INTO customer " +
                              "(first_name, last_name) " +
                              "VALUES ('John', 'Smith')",
                              Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
boolean b = rs.next();
if (b == true) {
    // Retrieve the new key value
    Long customerId = rs.getLong(1);
...
}

To obtain the auto-generated key, you have to execute an IDU statement (i.e. “INSERT”, “UPDATE” or “DELETE”) with one of these methods:

• boolean execute​(String sql, int autoGeneratedKeys) (the second argument has to be “Statement.RETURN_GENERATED_KEYS”)

• boolean execute​(String sql, int[] columnIndexes)

• boolean execute​(String sql, String[] columnNames)

• int executeUpdate​(String sql, int autoGeneratedKeys) (the second argument has to be “Statement.RETURN_GENERATED_KEYS”)

• int executeUpdate​(String sql, int[] columnIndexes)

• int executeUpdate​(String sql, String[] columnNames)

• long executeLargeBatch​(String sql, int autoGeneratedKeys) (the second argument has to be “Statement.RETURN_GENERATED_KEYS”)

• long executeLargeBatch​(String sql, int[] columnIndexes)

• long executeLargeBatch​(String sql, String[] columnNames)

You can use the class java.sql.Statement or java.sql.PreparedStatement.

Virtual DataPort does not store data itself so the support for this feature depends on what the underlying database supports; not all database vendors implement this functionality. For example, let us say an application connects to Virtual DataPort using the JDBC driver and this application executes code like the one above. If the data of the view “customer” is stored in Oracle, Oracle will return the auto-generated values to Virtual DataPort and the driver will pass them to the application. If you are using another database, test this behavior to check that the database you use returns the values.

How it Works Internally

When an application requests to return the generated keys, the driver modifies the IDU query it sends to Virtual DataPort:

• When the application requests to obtain all the generated keys (Statement.RETURN_GENERATED_KEYS), the driver appends this to the IDU statement:

  RETURNING ALL GENERATED KEYS
  

• When the application requests the auto-generated values of specific fields by name, the driver appends something like this to the IDU statement:

  RETURNING field_name_2, field_name_4, field_name_5
  

• When the application requests the auto-generated values of specific fields by index, the driver appends something like this to the IDU statement:

  RETURNING (3, 7, 11)