HP 3000 Manuals HP 3000 Manuals
How Data Type Differences Are Handled by PC API
Some differences between ALLBASE/SQL and SQLBase data types are
automatically resolved when PC API translates the data type returned by
ALLBASE/SQL into its SQLBase equivalent, which is then returned to the
application via the Gupta C/API.
For example, an application issues a FETCH on an ALLBASE/SQL table that
contains a CHAR field of 2500 characters. ALLBASE/SQL returns a CHAR
field of 2500 to PC API. In SQLBase, the maximum size for a CHAR field is
254. PC API translates this CHAR field of 2500 to a LONG VARCHAR field
of 2500 and returns it to the Gupta C/API, where it is then returned to
the application.
Table 5-2 summarizes ALLBASE/SQL data types that are translated into
SQLBase equivalents.
Table 5-2. Automatic Translation of Data Types from ALLBASE/SQL
----------------------------------------------------------------------------------------------
| | | |
| ALLBASE/SQL Data Type | SQLBase Data Type | Comments |
| | Returned to Application | |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| CHAR | CHAR | When length is less than 255 bytes |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| CHAR | LONG VARCHAR | When length is greater than 254 |
| | | bytes |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| VARCHAR | VARCHAR | When length is less than 255 bytes |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| VARCHAR | LONG VARCHAR | When length is greater than 254 |
| | | bytes |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| DECIMAL | DECIMAL | Compatible |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| NUMERIC | DECIMAL | Compatible |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| INTEGER | INTEGER | Compatible with current version of |
| | | SQLBase |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| SMALLINT | SMALLINT | Compatible with current version of |
| | | SQLBase |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| FLOAT | FLOAT | Differences in precision |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| DOUBLE PRECISION | DOUBLE PRECISION | Differences in precision |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| REAL | FLOAT | REAL not supported by PC API |
| | | |
----------------------------------------------------------------------------------------------
Table 5-2. Automatic Translation of Data Types from ALLBASE/SQL (cont.)
----------------------------------------------------------------------------------------------
| | | |
| ALLBASE/SQL Data Type | SQLBase Data Type | Comments |
| | Returned to Application | |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| DATETIME | DATETIME | Results of arithmetic expressions |
| | | differ |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| DATE | DATE | Results of arithmetic expressions |
| | | differ |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| TIME | TIME | Results of arithmetic expressions |
| | | differ |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| INTERVAL | CHAR | INTERVAL is not supported by |
| | | SQLBase |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| BINARY | CHAR | When length is less than 255 bytes |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| BINARY | LONG VARCHAR | When length is greater than 254 |
| | | bytes |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| VARBINARY | CHAR | When length is less than 255 bytes |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| VARBINARY | LONG VARCHAR | When length is greater than 254 |
| | | bytes |
| | | |
----------------------------------------------------------------------------------------------
Some data types are implemented differently by ALLBASE/SQL and SQLBase.
The rest of this chapter discusses those data types and their
differences.
CHAR and VARCHAR
The ALLBASE/SQL syntax for CHAR and VARCHAR is:
CHAR[ACTER][(length)] VARCHAR(length)
Difference. In SQLBase, length is from 1 to 254 for both CHAR and
VARCHAR. In ALLBASE/SQL, length is from 1 to 3,996 for both CHAR and
VARCHAR.
Impact. When an application is accessing an ALLBASE/SQL table containing
CHAR data items larger than 254 characters, PC API translates the
returned string into a LONG VARCHAR. You must code the application to
accommodate the longer returned string.
The SQLBase LONG Data Type
If you have defined tables in a SQLBase database with LONG fields, or
LONG VARCHAR fields of unspecified length, you can store strings of
unlimited length in those tables.
PC API does not support fields of unlimited length, however. When you
port a SQLBase database to ALLBASE/SQL, you must convert each LONG and
LONG VARCHAR field to a VARCHAR field, which can store a maximum of 3,996
bytes. For each LONG or LONG VARCHAR field exceeding 3,996 bytes, you
must define multiple VARCHAR fields. The number of multiple entries you
define depends on the length of the field. For example, for a 5000 byte
SQLBase LONG field, you need to define two ALLBASE/SQL VARCHAR fields;
and for a 10,000 byte SQLBase LONG field, define three ALLBASE/SQL
VARCHAR fields.
Once you have converted these fields to ALLBASE/SQL VARCHAR fields, you
can access them through your SQLBase application as if they were LONG
fields.
DECIMAL and NUMERIC
The ALLBASE/SQL syntax for DECIMAL and NUMERIC is:
{DEC[IMAL]} [(precision[,scale])]
{NUMERIC }
Difference. DECIMAL, DEC, and NUMERIC are all valid ALLBASE/SQL data
types; NUMERIC is not a valid SQLBase data type.
INTEGER
The ALLBASE/SQL syntax for INTEGER is:
INT[EGER]
Difference. None. The current version of SQLBase allows the same range
as ALLBASE/SQL: from -2,147,483,648 to +2,147,483,648.
Impact. When the application is connected to ALLBASE/SQL, you cannot
achieve the extended range supported by earlier SQLBase versions.
Suggestion. Declare INTEGER fields as DECIMAL(10,0) fields.
NUMBER
The SQLBase syntax for the NUMBER data type is:
NUMBER
Difference. The SQLBase data type NUMBER allows 22 digits of precision.
This data type is not currently supported by ALLBASE/SQL.
Impact. An application connected to ALLBASE/SQL receives an error if it
references a field declared as NUMBER. PC API does not attempt to
translate NUMBER fields.
Suggestion. Declare NUMBER fields as DECIMAL where no more than 15
digits of precision is required. Where more than 15 digits is required,
declare NUMBER fields as FLOAT.
SMALLINT
The ALLBASE/SQL syntax for the SMALLINT data type is:
SMALLINT
Difference. None. The current version of SQLBase supports the same
range as ALLBASE/SQL:-32,768 to +32,767.
Impact. When the application is connected to ALLBASE/SQL, you cannot
achieve the extended range provided by earlier SQLBase versions.
Suggestion. Change the declaration of SMALLINT fields to INTEGER fields.
FLOAT and DOUBLE PRECISION (Long Floating Point)
The ALLBASE/SQL syntax for FLOAT and DOUBLE PRECISION (long 64-bit
floating point number) is:
FLOAT[(precision)] DOUBLE PRECISION
Difference: In SQLBase, when the value of precision is from 22 to 53,
the format is double-precision floating point.
In ALLBASE/SQL, the value of precision can be from 25 to 53. If no
precision is specified, the default is FLOAT(53). The range of negative
numbers that can be represented is -1.79769313486230E+308 to
-2.22507385850721E-308. The range of positive numbers that can be
represented is 2.22507385850721E-308 to 1.7976931486230E+308.
ALLBASE/SQL recognizes E (exponential) or L (Pascal longreal) notation.
Impact. Possible loss of significance or precision in intermediate
translations when an application attempts to access an ALLBASE/SQL table
that contains FLOAT data outside the existing SQLBase range.
Suggestion. Examine the application to ensure that it can handle the
extended range.
FLOAT or REAL (Short Floating Point)
The syntax for FLOAT or REAL (short 32-bit floating point number) is the
same for both ALLBASE/SQL and SQLBase:
FLOAT(precision) or REAL
Differences. In SQLBase, when the value of precision is from 1 to 21,
inclusive, the format is single-precision floating point. In
ALLBASE/SQL, the value of precision can be from 1 to 24 inclusive.
In SQLBase, the keyword REAL is a synonym for FLOAT(21), specifying
single-precision floating point numbers. Although REAL is supported by
ALLBASE/SQL, PC API does not support the keyword REAL.
Impact. You must change REAL fields to FLOAT fields when connected to
ALLBASE/SQL.
Date/Time Data Types
The date and time related data types include DATETIME, DATE, TIME and
INTERVAL. Both ALLBASE/SQL and SQLBase allow arithmetic functions to be
used with DATETIME and DATE data types. However, the results returned
differ between ALLBASE/SQL and SQLBase. ALLBASE/SQL returns an integer
value that is not recognizable as a DATETIME or DATE output.
DATETIME.
ALLBASE/SQL supports the same DATETIME data type syntax for both input
and output, while SQLBase supports many variations of input syntax, but
only one output syntax.
SQLBase DATETIME Input Syntax.
SQLBase supports many different variations of DATETIME on input:
[date][time][AM][PM]
where date is defined as any of the following:
{dd-mon-yyyy}
{dd-mon-yy }
{dd/mon/yyyy}
{dd/mon/yy }
{mm/dd/yy }
{mm-dd-yy }
{mm-dd-yyyy }
{dd-mm-yy }
{yyyy-mm-dd }
and where
dd is a two digit day number from 0 to 31;
mon is an English three letter abbreviation for month: jan, feb, mar,
apr, may, jun, jul, aug, sep, oct, nov, dec;
yyyy is a four digit year number;
yy is a two digit year number of the current century;
and where time is defined as any of the following:
{hh }
{hh:mm }
{hh:mm:ss }
{hh:mm:ss:nnnnnn}
where
hh is a two digit hour number;
mm is a two digit minute number;
ss is a two digit second number;
nnnnnn is a six digit number representing microseconds (1/1,000,000).
SQLBase DATETIME Output Syntax. SQLBase supports only one output syntax
for DATETIME:
yyyy-mm-dd-hh.mm.ss.nnnnnn
ALLBASE/SQL DATETIME Syntax for Both Input and Output. ALLBASE/SQL
supports the same syntax for both input and output:
yyyy-mm-dd hh:mm:ss.nnn
Difference. In ALLBASE/SQL, nnn is a three digit number representing
milliseconds (1/1,000).
Impact. When applications are connected to ALLBASE/SQL, they cannot
retrieve or insert microsecond values.
Using SQLBase DATETIME Input Syntax with ALLBASE/SQL. When the
application software is connected to ALLBASE/SQL, it can use any of the
SQLBase input syntaxes, when DATETIME is part of a bind variable. The
SQLBase output syntax is returned.
Impact. When DATETIME data is part of a statement contained within a
literal string, you must use the ALLBASE/SQL syntax.
DATE and TIME.
Both SQLBase and ALLBASE/SQL support the DATE and TIME data types.
Differences. SQLBase uses the same syntax for input and output DATE and
TIME as described in the section above for date and time.
ALLBASE/SQL uses the following syntax for DATE:
yyyy-mm-dd
ALLBASE/SQL uses the following syntax for TIME:
hh:mm:ss
where yyyy, mm, dd, hh, mm, and ss are defined as described under
DATETIME above.
Using SQLBase DATE and TIME Syntax with ALLBASE/SQL. The same
restrictions apply as noted in "Using SQLBase DATETIME Input Syntax with
ALLBASE/SQL."
SQLBase Date and Time Constants. SQLBase defines constants for date and
time, while ALLBASE/SQL defines functions. If you are porting an SQLBase
application to ALLBASE/SQL, you must modify the program so that it uses
the functions, rather than the constants. Table 5-3 summarizes the
SQLBase constants and their equivalent ALLBASE/SQL functions.
Table 5-3. Date/Time SQLBase Constants and ALLBASE/SQL Functions
-----------------------------------------------------------------------------------------------
| | | |
| SQLBase Constant | ALLBASE/SQL Function | Returned Value |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| SYSDATETIME | CURRENT_DATETIME | Current date and time |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| SYSDATE | CURRENT_DATE | Current date |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| SYSTIME | CURRENT_TIME | Current time |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| SYSTIMEZONE | none | Current timezone |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| MICROSECONDS[s] | TO_TIME | time, to the microsecond |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| SECOND[s] | TO_TIME | time, to the second |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| MINUTE[s] | TO_TIME | time, to the minute |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| DAY[s] | TO_TIME | time, to the day |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| MONTH[s] | TO_TIME | time, to the month |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| YEAR[s] | TO_TIME | time, to the year |
| | | |
-----------------------------------------------------------------------------------------------
INTERVAL
SQLBase does not support the INTERVAL data type, but both ALLBASE/SQL and
PC API do support it. You can access INTERVAL data in an ALLBASE/SQL
database from PC API applications.