HP 3000 Manuals HP 3000 Manuals
Using Different Data Types Defined By ALLBASE/SQL and SQLBase
This section explains the differences in the ways that data types are
implemented by ALLBASE/SQL and SQLBase. These two database management
systems are compared for users who wish to connect their SQLBase
applications to ALLBASE/SQL. The following topics are discussed:
* How different data type implementations affect PC API users.
* How data type differences are handled by PC API.
How Different Data Typing Rules Affect PC API Users
When the application is connected to an ALLBASE/SQL DBEnvironment, in
most cases you can use either SQLBase or ALLBASE/SQL data type
declarations without encountering errors. Many data types are completely
compatible.
In most cases, PC API is able to translate the SQLBase data type into its
ALLBASE/SQL equivalent, when the database function call is passed to the
HP 3000 server. Likewise, PC API translates the ALLBASE/SQL data type
into its SQLBase equivalent when the result of the function call is
passed back to Gupta interface.
This chapter documents only those cases where the differences in the data
types could cause some degree of impact on the application. In a few
cases, the SQLBase data type is not supported by ALLBASE/SQL. Fields
declared as unsupported data types must be changed to a data type
supported by ALLBASE/SQL. In other cases, the maximum size of the SQLBase
data type is not the same as the maximum size of the same ALLBASE/SQL
data type. When the maximum sizes vary, you may need to change the
application to accommodate size differences.
Table 3-1 lists each data type defined by ALLBASE/SQL and SQLBase and
their differences in compatibility. The remainder of the chapter
discusses these differences in more detail.
Table 3-1. SQLBase and ALLBASE/SQL Data Type Differences
----------------------------------------------------------------------------------------------
| | | |
| SQLBase Data Type | ALLBASE/SQL Equivalent | Differences |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| CHAR | CHAR | SQLBase maximum length = 254 |
| | | bytes; ALLBASE/SQL maximum length |
| | | = 3996 bytes |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| VARCHAR | VARCHAR | SQLBase maximum length = 254 |
| | | bytes; ALLBASE/SQL maximum length |
| | | = 3996 bytes |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| LONG VARCHAR | VARCHAR | SQLBase unlimited length; |
| | | ALLBASE/SQL maximum length = 3996 |
| | | bytes |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| LONG | VARCHAR | SQLBASE unlimited length; |
| | | ALLBASE/SQL maximum length = 3996 |
| | | bytes |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| NUMBER | not valid | Treated as an error when connected |
| | | to ALLBASE/SQL |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| DECIMAL | DECIMAL | None |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| | NUMERIC | NUMERIC is valid synonym for |
| | | DECIMAL in ALLBASE/SQL only |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| INTEGER | INTEGER | None |
| | | |
----------------------------------------------------------------------------------------------
Table 3-1. SQLBase and ALLBASE/SQL Data Type Differences (cont.)
----------------------------------------------------------------------------------------------
| | | |
| SQLBase Data Type | ALLBASE/SQL Equivalent | Differences |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| INT | INT | None |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| SMALLINT | SMALLINT | None |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| FLOAT | FLOAT | Differences in precision |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| REAL | FLOAT | PC API does not support REAL as a |
| | | synonym for FLOAT |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| DOUBLE PRECISION | DOUBLE PRECISION | Differences in precision |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| DATETIME | DATETIME | Data represented differently; |
| | | differences in results of |
| | | mathematical expressions |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| TIMESTAMP | DATETIME | Data represented differently |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| DATE | DATE | Data represented differently |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| TIME | TIME | Data represented differently |
| | | |
----------------------------------------------------------------------------------------------
The following ALLBASE/SQL data types have no equivalent SQLBase data
types and are unsupported by either SQLBase or PC API:
* INTERVAL
* BINARY
* LONG BINARY
* VARBINARY
* LONG VARBINARY
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 Gupta interface.
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 Gupta interface, where it is then returned to
the application.
Table 3-2 summarizes ALLBASE/SQL data types that are translated into
SQLBase equivalents.
Table 3-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 3-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 |
| | | [REV BEG] |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| LONG VARBINARY | LONG VARCHAR | When length is greater than 254 |
| | | bytes. Length must be |
| | | specified.[REV END] |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| VARBINARY | CHAR | When length is less than 255 |
| | | bytes[REV BEG] |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| LONG VARBINARY | LONG VARCHAR | When length is greater than 254 |
| | | bytes. Length must be |
| | | specified.[REV END] |
| | | |
----------------------------------------------------------------------------------------------
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.[REV BEG] When you port a SQLBase
database to ALLBASE/SQL, you must convert each LONG and LONG VARCHAR
field to a LONG BINARY or LONG VARBINARY field.[REV END]
Once you have converted these fields to ALLBASE/SQL LONG BINARY or LONG
VARBINARY 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.
PC API accepts either ALLBASE/SQL or SQLBase DATETIME syntax for input.
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
However, PC API returns the ALLBASE/SQL, not SQLBase, DATETIME format on
FETCHs. 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 "SQLBase DATETIME Input Syntax."
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 3-3 summarizes the
SQLBase constants and their equivalent ALLBASE/SQL functions.
Table 3-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.