Using Datetime Values in Denodo Stored Procedures¶

This section explains how to develop stored procedures that have input and/or output parameters of a datetime type.

When you develop a stored procedure, the main class of the procedure has to override the method getParameters(), which defines the output schema of the procedure. This method has to return an array of StoredProcedureParameter objects. Each object represents an input parameter or an output one.

The second parameter of the constructors of the class StoredProcedureParameter is the parameter type. The value of this parameter is a constant of the class java.sql.Types.

The table below displays the mapping between:

A Denodo data type and the constant of the class java.sql.Types that has to be passed to the constructor of StoredProcedureParameter.
A Denodo data type and the class of the Java object that the stored procedure has to return.

Denodo Data Type	Constant of the Class java.sql.Types	Java Class of the Input Parameters	Java Class of the Output Values
localdate	Types.DATE	java.sql.Date	java.time.LocalDate or java.sql.Date
time	Types.TIME	java.sql.Time	java.time.LocalTime or java.sql.Time
timestamp	Types.TIMESTAMP	java.sql.Timestamp	java.time.LocalDateTime or java.sql.Timestamp
timestamptz	Types.TIMESTAMP_WITH_TIMEZONE	java.sql.Timestamp	java.time.OffsetDateTime or java.sql.Timestamp
intervalyearmonth	JDBCTypeUtil.INTERVAL_YEAR_MONTH	java.time.Period	java.time.Period
intervaldaysecond	JDBCTypeUtil.INTERVAL_DAY_SECOND	java.time.Duration	java.time.Duration

JDBCTypeUtil = com.denodo.vdb.vdbinterface.common.clientResult.vo.descriptions.type.util.JDBCTypeUtil

For example, if you want to have an input parameter of type time, the value of the parameter type has to be Types.TIME.

At runtime, when the procedure is executed, the execution engines invokes the method doCall(Object[] inputValues) of the procedure. The Java class of the objects of the array inputValues depends on the value of the type declared in the constructor of StoredProcedureParameter.

@Override
public StoredProcedureParameter[] getParameters() {

    return new StoredProcedureParameter[] {

          // Input parameter of type "localdate"
          new StoredProcedureParameter("date_field", Types.DATE, StoredProcedureParameter.DIRECTION_IN)

          // Input parameter of type "timestamp"
        , new StoredProcedureParameter("timestamp_field", Types.TIMESTAMP, StoredProcedureParameter.DIRECTION_IN)

          // Input parameter of type "timestamptz"
        , new StoredProcedureParameter("timestamptz_field", Types.TIMESTAMP_WITH_TIMEZONE, StoredProcedureParameter.DIRECTION_IN)

          // Input parameter of type "time"
        , new StoredProcedureParameter("time_field", Types.TIME, StoredProcedureParameter.DIRECTION_IN)

          // Input/output parameter of type "interval_day_second"
        , new StoredProcedureParameter("intervaldaysecond_field", JDBCTypeUtil.INTERVAL_DAY_SECOND, StoredProcedureParameter.DIRECTION_INOUT)

          // Output parameter of type "interval_year_month"
        , new StoredProcedureParameter("intervalyearmonth_field", JDBCTypeUtil.INTERVAL_YEAR_MONTH, StoredProcedureParameter.DIRECTION_OUT)
     };
}

The listing below shows a sample implementation of doCall(...). This method is invoked by the execution engine to run the procedure.

Example of doCall()¶

public void doCall(Object[] inputValues) {

    Date sqlDate = (Date) inputValues[0]; //Types.DATE
    LocalDate localDate = sqlDate.toLocalDate();

    Timestamp sqlTimestamp = (Timestamp) inputValues[1]; //Types.TIMESTAMP
    LocalDateTime localDateTime = sqlTimestamp.toLocalDateTime();

    Timestamp sqlTimestamptz = (Timestamp) inputValues[2];  //Types.TIMESTAMP_WITH_TIMEZONE
    OffsetDateTime offsetDateTime = sqlTimestamptz.toInstant().atOffset(ZoneOffset.UTC);

    Time sqlTime = (Time) inputValues[3]; //Types.TIME
    LocalTime localTime = sqlTime.toLocalTime();

    Duration d = (Duration) inputValues[4]; // JDBCTypeUtil.INTERVAL_DAY_SECOND
    Period p = (Period) inputValues[5]; ]; // JDBCTypeUtil.INTERVAL_YEAR_MONTH
}

Regarding datetime values, the procedure can return objects of the package java.sql or java.time.

In the listing below, the procedure returns two rows that are equivalent. The first row is generated with objects of the package java.sql and the second one, with objects of the package java.time.

public void doCall(Object[] inputValues) {

     // Adding a row with java.sql objects
     getProcedureResultSet().addRow(new Object[]{
         Date.valueOf("2017-10-11"),
         Timestamp.valueOf("2015-03-08 01:59:59"),
         new Timestamp(sdf.parse("2015-03-08 01:59:59 +01:00").getTime()),
         Time.valueOf("21:15:45"),
         Duration.ofHours(65).plusMinutes(23),
         Period.ofMonths(25)});

     // Adding a row with java.time objects
     getProcedureResultSet().addRow(new Object[]{
         LocalDate.parse("2017-10-11"),
         LocalDateTime.parse("2015-03-08T01:59:59"),
         OffsetDateTime.parse("2015-03-08T01:59:59+01:00"),
         LocalTime.parse("21:15:45"),
         Duration.ofHours(65).plusMinutes(23),
         Period.ofMonths(25)
}

Compatibility with Stored Procedures of Previous Versions¶

In previous versions, the constants of the class java.sql.Types are mapped to different data types of Denodo.

Types.DATE:
- Starting with version 7.0: mapped to localdate
- Previous versions: mapped to date (deprecated)
Types.TIMESTAMP:
- Starting with version 7.0: mapped to timestamp
- Previous versions: mapped to date (deprecated)
Types.TIME:
- Starting with version 7.0: mapped to time
- Previous versions: mapped to long

If you developed a stored procedure that still relies on these objects, declare the procedure with the token USE_DENODO_6_0_TYPE_MAPPING. For example:

CREATE PROCEDURE testnewtypesprocedure2
    CLASSNAME='com.denodo.vdb.test.TestNewTypesProcedure'
    CLASSPATH=''
    USE_DENODO_6_0_TYPE_MAPPING = true;

USER MANUALS

Using Datetime Values in Denodo Stored Procedures¶

Compatibility with Stored Procedures of Previous Versions¶