HP 3000 Manuals HP 3000 Manuals
Appendix F Date/Time API
Date/Time API Description
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:
* TO_DATE
* TO_TIME
* TO_DATETIME
* TO_INTERVAL
* TO_CHAR
* TO_INTEGER
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].
{sourcetype IN {* }}
U[PDATE] TYPE { {mappedtbl}}[TO newtype]
{ }
{IN mappedtbl.mappedcol }
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.