 |
» |
|
|
|
The collection of conversion routines called the "Date/Time Application Programming Interface (API)" allows conversion of data to and from internal format of ALLBASE/SQL date/time values programmatically. You can enter dates, times, and intervals through the SQL interface in the format of the ALLBASE/SQL date/time data types and store it in the same format in your TurboIMAGE/XL database. This is particularly useful in a PC client/server environment where many of the PC tools have date/time data types which map directly to the ALLBASE/SQL date/time data types. However, you may want to read from and write to these fields using your TurboIMAGE/XL applications as well. In order to help you with that, the conversion routines of ALLBASE/SQL were made externally callable. They are the following: The necessary information on this API is given later in this appendix.
For additional information on ALLBASE/SQL date/time functions, refer to "Date/Time Functions" in the "Expressions" chapter of the ALLBASE/SQL Reference Manual. Updating K8 Data Types | |
You can use K8 data type of TurboIMAGE/XL for ALLBASE/SQL date/time data types.
The UPDATE TYPE command of IMAGE/SQL allows you to update your K8
data type of TurboIMAGE/XL to SQL data type DATE, TIME,
DATETIME, or INTERVAL. The default K8 data type mapping to
SQL is CHAR[16]. U[PDATE] TYPE { sourcetype IN { *
mappedtbl }
IN mappedtbl.mappedcol } [TO newtype] where newtype can be
DATE, TIME, DATETIME, or INTERVAL Some examples are:
UPDATE TYPE IN table1.K8item1 TO DATE
UPDATE TYPE IN table2.K8item2 TO TIME
UPDATE TYPE IN table3.K8item3 TO DATETIME
UPDATE TYPE IN table4.K8item4 TO INTERVAL
UPDATE TYPE K8 IN * TO DATE
|
After you update your K8 data type to one of the SQL date/time data types for SQL interface,
you may want to read from and write to these K8 data type fields in your TurboIMAGE/XL database using TurboIMAGE/XL applications. The field is still K8 for TurboIMAGE/XL applications; it is updated to one of ALLBASE/SQL date/time types for the SQL interface. Because the data is stored in the format of ALLBASE/SQL, it is necessary to convert it to a readable string after you retrieve the data. Similarly, it is necessary to
convert character data into the ALLBASE/SQL date/time format before it can
be inserted into your TurboIMAGE/XL database using the TurboIMAGE/XL
intrinsics. Use the new externally callable procedures to handle this conversion.
This section describes the Date/Time API and how to use it. Mapping of DATE/TIME Functions | |
There is a one-to-one mapping between the Date/Time API routines and the
ALLBASE/SQL date/time functions as shown below:
Date/Time ALLBASE/SQL
API routine Date/Time function
----------- -----------------
DBTODATE TO_DATE
DBTOTIME TO_TIME
DBTODTTM TO_DATETIME
DBTOITVL TO_INTERVAL
DBTOCHAR TO_CHAR
DBTOINT TO_INTEGER
|
The functionality of the API routine is equivalent to its counterpart
ALLBASE/SQL Date/Time function. The descriptions of the parameters for each of the
Date/Time API routines follow. DBTODATE | |
Syntax.
DBTODATE (charval, stringlen, format, fmtlen, dateval,
error)
Parameters.
- charval
4-byte address of an array of characters
holding the character representation of a date.
- stringlen
4-byte integer length of charval in bytes.
- format
4-byte address of an array of characters
holding the format specification of
the input character string, charval.
Only valid format specifications for the TO_DATE function are allowed.
- fmtlen
4-byte integer length of format in bytes.
- dateval
4-byte integer address of a 16-byte buffer in which
the resulting date in ALLBASE/SQL internal format is stored.
- error
4-byte address of a 4-byte integer where
error code is returned. error is set
to 0 if no error occurred. Otherwise,
it is set to the DBERR code for the
error returned.
DBTOTIME | |
Syntax.
DBTOTIME (charval, stringlen, format, fmtlen, timeval, error)
Parameters.
- charval
4-byte address of an array of characters
holding the character representation of time.
- stringlen
4-byte integer length of charval in bytes.
- format
4-byte address of an array of characters
holding the format specification of
the input character string, charval.
Only valid format specifications for the TO_TIME function are allowed.
- fmtlen
4-byte integer length of format in bytes.
- timeval
4-byte integer address of a 16-byte buffer in which
the resulting time in ALLBASE/SQL
internal format is stored.
- error
4-byte address of a 4-byte integer where
error code is returned. error is set
to 0 if no error occurred. Otherwise,
it is set to the DBERR code for the
error returned.
DBTODTTM | |
Syntax.
DBTODTTM (charval, stringlen, format, fmtlen, dttmal, error)
Parameters.
- charval
4-byte address of an array of characters
holding the character representation of date-time value.
- stringlen
4-byte integer length of charval in bytes.
- format
4-byte address of an array of characters
holding the format specification of
the input character string, charval.
Only valid format specifications for
the TO_DATETIME function are allowed.
- fmtlen
4-byte integer length of format in bytes.
- dttmal
4-byte integer address of a 16-byte buffer in which
the resulting time in ALLBASE/SQL
internal format is stored.
- error
4-byte address of a 4-byte integer where
error code is returned. error is set
to 0 if no error occurred. Otherwise,
it is set to the DBERR code for the
error returned.
DBTOITVL | |
Syntax.
DBTOITVL (charval, stringlen, format, fmtlen, itvlval, error)
Parameters.
- charval
4-byte address of an array of characters
holding the character representation of interval value.
- stringlen
4-byte integer length of charval in bytes.
- format
4-byte address of an array of characters
holding the format specification of
the input character string, charval.
Only valid format specifications for
the TO_INTERVAL function are allowed.
- fmtlen
4-byte integer length of format in bytes.
- itvlval
4-byte integer address of a 16-byte buffer in which
the resulting interval in ALLBASE/SQL internal format is stored.
- error
4-byte address of a 4-byte integer where error code is
returned. error is set to 0 if no error occurred.
Otherwise, it is set to the DBERR code for the error returned.
DBTOCHAR | |
Syntax.
DBTOCHAR (dateval,datatype,format,fmtlen,charval,bufflen,error)
Parameters.
- dateval
4-byte address of the 16-byte date, time, datetime, or
interval value stored in the ALLBASE/SQL
date/time format to be converted.
- datatype
4-byte integer representing the data type of the input,
DATEVAL. It must be one of the following values: - 10
Date - 11
Time - 12
DateTime - 13
Interval
- format
4-byte address of an array of characters
holding the format specification of
the desired format for the character string
result. Only valid format specifications for
the TO_CHAR function are allowed.
- fmtlen
4-byte integer length of format in bytes.
- charval
4-byte address of a character buffer to put
result. This routine will fill this
buffer with the character string representation of the date/time value,
blank-filling to the end of the buffer
as indicated by the length. If the
character string representation for
the date/time value is longer than the
specified length of the buffer, the
character string will be truncated to
specified length.
- bufflen
4-byte integer length of charval buffer in bytes.
- error
4-byte address of a 4-byte integer where
error code is returned. error is set
to 0 if no error occurred. Otherwise,
it is set to the DBERR code for the error returned.
DBTOINT | |
Syntax.
DBTOINT (dateval, datatype, format, fmtlen, intval, error)
Parameters.
- dateval
4-byte address of the 16-byte date, time, datetime, or
interval value stored in the ALLBASE/SQL date/time format to be converted.
- datatype
4-byte integer representing the data type of the input,
DATEVAL. It must be one of the following values: - 10
Date - 11
Time - 12
DateTime - 13
Interval
- format
4-byte address of an array of characters
holding the format specification
specifying which component (month, day, hour,
etc.) of the input, dateval, should be
converted to the integer. Only valid format specifications for
the TO_INTEGER function are allowed.
- fmtlen
4-byte integer length of format in bytes.
- intval
4-byte address of a 4-byte buffer where the integer result gets stored. - error
4-byte address of a 4-byte integer where
error code is returned. error is set
to 0 if no error occurred. Otherwise, it is set to the DBERR code for the
error returned.
|
