Retrieval and Insertion of DATETIME and INTERVAL Values

When an application retrieves or inserts a DATETIME or INTERVAL value, the DataBlade API module must ensure that the qualifier field of the variable is valid:

• When an application fetches a DATETIME value into an mi_datetime variable or inserts a DATETIME value from an mi_datetime variable, the application must ensure that the dt_qual field of the dtime_t structure is valid.
• When an application fetches an INTERVAL value into an mi_interval variable or inserts an INTERVAL value from an mi_interval variable, the application must ensure that the in_qual field of the intrvl_t structure is valid.

Fetch or Insert into an mi_datetime Variable

When a DataBlade API module uses an mi_datetime variable to fetch or insert a DATETIME value, the module must find a valid qualifier in the mi_datetime variable. The DataBlade API takes one the following actions, based on the value of the dt_qual field in the dtime_t structure that is associated with the variable:

• When the dt_qual field contains a valid qualifier, the DataBlade API extends the column value to match the dt_qual qualifier.

  Extending is the operation of adding or dropping fields of a DATETIME value to make it match a given qualifier. You can explicitly extend DATETIME values with the SQL EXTEND function and the dtextend( ) function.

• When the dt_qual field does not contain a valid qualifier, the DataBlade API takes different actions for a fetch and an insert:
  • For a fetch, the DataBlade API uses the DATETIME column value and its qualifier to initialize the mi_datetime variable.

    Zero is an invalid qualifier. Therefore, if you set the dt_qual field to zero, you can ensure that the DataBlade API uses the qualifier of the DATETIME column.

  • For an insert, the DataBlade API cannot perform the insert or update operation.

    The DataBlade API sets the SQLSTATE status variable to an error-class code (and SQLCODE to a negative value) and the update or insert operation on the DATETIME column fails.

Fetch or Insert into an mi_interval Variable

When a DataBlade API module uses an mi_interval variable to fetch or insert an INTERVAL value, the DataBlade API must find a valid qualifier in the mi_interval variable. The DataBlade API takes one of the following actions, based on the value of the in_qual field the intrvl_t structure that is associated with the variable:

• When the in_qual field contains a valid qualifier, the DataBlade API checks it for compatibility with the qualifier from the INTERVAL column value.

  The two qualifiers are compatible if they belong to the same interval class: either year to month or day to fraction. If the qualifiers are incompatible, the DataBlade API sets the SQLSTATE status variable to an error-class code (and SQLCODE is set to a negative value) and the select, update, or insert operation fails.

  If the qualifiers are compatible but not the same, the DataBlade API extends the column value to match the in_qual qualifier. Extending is the operation of adding or dropping fields within one of the interval classes of an INTERVAL value to make it match a given qualifier. You can explicitly extend INTERVAL values with the invextend( ) function.

• When the in_qual field does not contain a valid qualifier, the DataBlade API takes different actions for a fetch and an insert:
  • For a fetch, if the in_qual field contains zero or is not a valid qualifier, the DataBlade API uses the INTERVAL column value and its qualifier to initialize the mi_interval variable.
  • For an insert, if the in_qual field is not compatible with the INTERVAL column or if it does not contain a valid value, the DataBlade API cannot perform the insert or update operation.

    The DataBlade API sets the SQLSTATE status variable to an error-class code (and SQLCODE is set to a negative value) and the update or insert operation on the INTERVAL column fails.

Implicit Data Conversion

You can select a DATETIME or INTERVAL column value into a character variable. The DataBlade API converts the DATETIME or INTERVAL column value to a character string before it stores it in the character variable. This character string conforms to the ANSI SQL standards for DATETIME and INTERVAL values.

You can also insert a DATETIME or INTERVAL column value from a character variable. The DataBlade API uses the data type and qualifiers of the column value to convert the character value to a DATETIME or INTERVAL value. It expects the character string to contain a DATETIME or INTERVAL value that conforms to ANSI SQL standards.

If the conversion fails, the DataBlade API sets the SQLSTATE status variable to an error-class code (and SQLCODE status variable to a negative value) and the update or insert operation fails.