|
BY: Doug Myers, HP Database Lab
This document describes the new Date/Time API that was added to ALLBASE/SQL and
IMAGE/SQL in ALLBASE/SQL release G1.15.
The following subjects are covered in this document:
Introduction
One of the requests that came out of INTEREX '95 was to allow conversion
between ALLBASE/SQL date/time values programmatically. This request was
mainly being pushed by IMAGE/SQL customers who are taking advantage of data
type mapping allowed in the IMAGESQL utility to map X16 fields to one of the
ALLBASE/SQL date/time data types. By doing this, dates, times, and intervals
can be entered through the SQL interface and stored directly in an IMAGE
database in the internal format of the ALLBASE/SQL date/time data types. This
is particularly useful in a PC client/server environment where many of the PC
tools have date and time data types which map directly to the ALLBASE/SQL
date/time data types through the PC API layers supported with IMAGE/SQL and
ALLBASE/SQL. With this new functionality, customers now have the desire to be
able to read and write data from these fields in their IMAGE database using
the Turbo IMAGE intrinsic interface. Since these fields store data in the
internal ALLBASE/SQL date/time format, a way of converting this data into a
readable character string was needed. Also character data needs to be
converted to the ALLBASE/SQL date/time format before it can be inserted into
the IMAGE database using the Turbo IMAGE intrinsics.
In response to this request, two approaches were considered.
- Fully describe the internal ALLBASE/SQL date/time data type format, and
have the customers write there own routines to convert data to and from
this internal format.
- Provide callable routines which will handle the conversion.
The second approach seemed more desirable from both a customer perspective
(less work for the customer) and from an HP perspective (easier to support).
Therefore, we have reworked the conversion routines which we use internally to
perform the ALLBASE/SQL date/time functions (TO_DATE, TO_TIME, TO_DATETIME,
TO_INTERVAL, TO_CHAR, TO_INTEGER) and made them externally callable. The
collection of these new routines are called the DATE/TIME Application
Programming Interface, or DATE/TIME API in abbreviated form.
This article describes the Date/Time API and how to use it. For additional
information on ALLBASE/SQL date/time functions, refer to Date/Time
Functions discussion in the Expressions chapter of the
ALLBASE/SQL Reference Manual. The Date/Time API is available in the
A.G1.15 release of ALLBASE/SQL.
As a side note, one of the other requests from Interex was to allow K8 to be
mapped to the ALLBASE date/time data types, in addition to X16. K8 is more
desirable than X16 since Query will not try to print out K8 as it does X16.
Trying to print out the date/time data as characters, as does Query with X16,
can cause terminals to hang. This change was made in IMAGE/SQL version
B.G1.10.
Date/time API description
There is a one-to-one mapping between the Date/Time API routines and the
ALLBASE/SQL date/time functions as 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 description of all the parameters for each of the
Date/Time API routines is as follows.
Dbtodate
DBTODATE (charval, stringlen, format, fmtlen, dateval, error)
Parameters
- charval
- is a 4 byte address of an array of characters
holding the character representation
of a date.
- stringlen
- is a 4 byte integer length of charval in bytes.
- format
- is a 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
- is a 4 byte integer length of format in bytes.
- dateval
- is a 4 byte integer address of a 16-byte buffer in which
the resulting date in ALLBASE/SQL
internal format is stored.
- error
- is a 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
DBTOTIME (charval, stringlen, format, fmtlen, timeval, error)
Parameters
- charval
- is a 4 byte address of an array of characters
holding the character representation
of time.
- stringlen
- is a 4 byte integer length of charval in bytes.
- format
- is a 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
- is a 4 byte integer length of format in bytes.
- timeval
- is a 4 byte integer address of a 16 byte buffer in which
the resulting time in ALLBASE/SQL
internal format is stored.
- error
- is a 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
DBTODTTM (charval, stringlen, format, fmtlen, dttmal, error)
Parameters
- charval
- is a 4 byte address of an array of characters
holding the character representation
of date-time value.
- stringlen
- is a 4 byte integer length of charval in bytes.
- format
- is a 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
- is a 4 byte integer length of format in bytes.
- dttmal
- is a 4 byte integer address of a 16 byte buffer in which
the resulting time in ALLBASE/SQL
internal format is stored.
- error
- is a 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
DBTOITVL (charval, stringlen, format, fmtlen, itvlval, error)
Parameters
- charval
- is a 4 byte address of an array of characters
holding the character representation of interval value.
- stringlen
- is a 4 byte integer length of charval in bytes.
- format
- is a 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
- is a 4 byte integer length of format in bytes.
- itvlval
- is a 4 byte integer address of a 16 byte buffer in which
the resulting interval in ALLBASE/SQL internal format is stored.
- error
- is a 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
DBTOCHAR(dateval,datatype,format,fmtlen,charval,bufflen,error)
Parameters
- dateval
- is a 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
- is a 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
- is a 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
- is a 4 byte integer length of format in bytes.
- charval
- is a 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
- is a 4 byte integer length of charval buffer in bytes.
- error
- is a 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
DBTOINT(dateval, datatype, format, fmtlen, intval, error)
Parameters
- dateval
- is a 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
- is a 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
- is a 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
- is a 4 byte integer length of format in bytes.
- intval
- is a 4 byte address of a 4-byte buffer where
the integer result gets stored.
- error
- is a 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.
Sample programs
Here are links to a sample program written in four different languages:
COBOL,
C, Pascal,
and FORTRAN.
Each version of the program calls each of the Date/Time API routines
at least once. For C and Pascal it is fairly straight forward. However,
there is currently a linking problem which exists when trying to link directly
to the Date/Time API routines from FORTRAN, and COBOL. In order to work
around this problem, the FORTRAN and COBOL programs make calls through a very
thin layer of C code or Pascal code. I have also included links the code for
this thin layer for both C and
Pascal. This problem should be fixed in the
next available patch A.G1.16.
Page last updated on March 11, 1996
|
Printable version
