USER MANUALS

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 8.0: mapped to localdate

    • Previous versions: mapped to date (deprecated)

  • Types.TIMESTAMP:

    • Starting with version 8.0: mapped to timestamp

    • Previous versions: mapped to date (deprecated)

  • Types.TIME:

    • Starting with version 8.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;
Add feedback