HP 3000 Manuals HP 3000 Manuals
Using Default Data Types with Dynamic Parameters
When the PREPARE statement executes, ALLBASE/SQL assigns a default data
type to any dynamic parameter in the dynamic section that is created.
Depending on how you provide dynamic parameter data to the database, one
of the following occurs. If you are using the DESCRIBE INPUT statement,
the default data type information is loaded into the related format
array. If you are using a host variable, the data type of the host
variable is compared to the default, and a conversion is done if
possible. (with These methods of assigning data are discussed in the
"Programming with with Dynamic Parameters" section in this document.)
with The following topics are discussed in this section:
* How ALLBASE/SQL Derives a Default Data Type.
* Dynamic Parameter Formats.
* Conversion of Actual Data Types to Default Data Types.
* Data Overflow and Truncation.
How ALLBASE/SQL Derives a Default Data Type
The following explains how the default data type of a dynamic parameter
is derived by ALLBASE/SQL:
* When the parameter is an operand of an arithmetic operator or a
comparison operator, its data type is assumed to be that of the
other operand. In the following example, the dynamic parameter is
assumed to be an integer because SalesPrice is defined as an
integer column in the database:
UPDATE PurchDB.Parts
SET SalesPrice = (SalesPrice * ?)
* When the parameter is the second and/or third operand in a BETWEEN
predicate, its data type is assumed to be that of the first
operand. The assumed data type of both dynamic parameters in the
following example is decimal with a precision of six and a scale
of two, because this is the data type of the SalesPrice column in
the database.
SELECT * FROM PurchDB.Parts
WHERE SalesPrice BETWEEN ? AND ?
* When the parameter is any value in an IN predicate, its data type
is assumed to be that of the expression. In the following
example, the dynamic parameters are assumed to be integers,
because OrderNumber is defined as an integer in the database:
SELECT * FROM PurchDB.Orders
WHERE OrderNumber IN (?, ?, ?)
* When the parameter is the pattern value in a LIKE predicate, it is
assumed to be of character data type. The default length is based
on the other operand of the LIKE predicate. In the following
example, the default length of the dynamic parameter is 16 since
PartNumber is defined as a 16 byte character column.
SELECT * FROM PurchDB.Parts WHERE PartNumber LIKE ?
* When the parameter is a parameter in the SET clause of an UPDATE
statement, its data type is assumed to be that of the update
column. The assumed data type of the dynamic parameter in the
following example is decimal with a precision of six and a scale
of two, because this is the column definition of SalesPrice in the
database.
UPDATE PurchDB.Parts
SET SalesPrice = ?
WHERE PartNumber = '12345'
* Used in the VALUES clause of an INSERT statement, its data type is
assumed to be that of the inserted column. In the example below,
both dynamic parameters are assumed to be of character data type
because both PartNumber and PartName are defined as such in the
database.
INSERT INTO PurchDB.Parts (PartNumber, PartName)
VALUES(?,?)
_________________________________________________________________
NOTE The following examples are syntactically correct but have no
or little value symantically. They illustrate additional
places where dynamic parameters can be used in accord with
standard SQL.
_________________________________________________________________
* When the parameter is the first operand in a BETWEEN predicate,
its data type is assumed to be that of the second operand. In the
following example, the dynamic parameter is assumed to be an
integer because 1000 is an integer:
SELECT * FROM PurchDB.Orders
WHERE ? BETWEEN 1000 AND 2000
* When the parameter is used as the first and third operands in a
BETWEEN predicate, its data type is assumed to be that of the
second operand. For example, both dynamic parameters in the
following example are assumed to be integer because 1000 is an
integer.
SELECT * FROM PurchDB.Orders
WHERE ? BETWEEN 1000 AND ?
* When the parameter is the expression in an IN predicate, its data
type is assumed to be that of the first value or that of the
result column of a subquery. In the following example, the data
type of the dynamic parameter is assumed to be integer, because
30507 is an integer value.
SELECT DISTINCT * FROM PurchDB.Orders
WHERE ? IN (30507, 30517, 30518)
In the following example, the data type of the dynamic parameter
is assumed to be that of the result column, OrderNumber, which is
defined in the database as integer.
SELECT * FROM PurchDB.Orders
WHERE ? IN (SELECT OrderNumber FROM PurchDB.Orders
WHERE OrderNumber BETWEEN 1000 AND 2000)
* When the parameter is the expression in a quantified predicate,
its data type is assumed to be that of the result column of the
subquery. In the following example, the data type of the dynamic
parameter is assumed to be integer, because that is the column
definition of the OrderNumber column.
SELECT * FROM PurchDB.Orders
WHERE ? = ANY (SELECT OrderNumber FROM PurchDB.Orders WHERE OrderNumber >= 500)
NOTE When a dynamic parameter is used in a non-assignment operation and
the default data type is determined to be REAL, ALLBASE/SQL
promotes it to FLOAT for better performance and data accuracy.
Therefore, the assumed data type for a non-assignment operation is
never REAL.
Dynamic Parameter Formats
In addition to default data types, dynamic parameters have default data
formats as shown in the table below:
Table 3-7. ALLBASE/SQL Default Data Formats for Dynamic Parameters
----------------------------------------------------------------------------------------------
| | | |
| Type of Dynamic Parameter | ALLBASE/SQL | ALLBASE/SQL Default Data Format |
| | Default Data Type | |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| column value | LONG BINARY | CHAR(96) - contains the long |
| | | column descriptor |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| column value | LONG VARBINARY | CHAR(96) - contains the long |
| | | column descriptor |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| column value | BINARY(n) | BINARY(n) |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| column value | VARBINARY(n) | VARBINARY(n) |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| column value | DATE | CHAR(10) |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| column value | TIME | CHAR(8) |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| column value | DATETIME | CHAR(23) |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| column value | INTERVAL | CHAR(20) |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| second argument in an | INTEGER | INTEGER |
| ADD_MONTHS function | | |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| first or second argument | CHAR or VARCHAR | CHAR(72) |
| in a TO_DATE, TO_TIME, | | |
| TO_DATETIME, or | | |
| TO_INTERVAL function | | |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| second argument in a | CHAR or VARCHAR | CHAR(72) |
| TO_CHAR or TO_INTEGER | | |
| function | | |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| escape character in a | CHAR | CHAR(2) |
| LIKE predicate | | |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| RAISE ERROR number | INTEGER | INTEGER |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| RAISE ERROR text | CHAR | CHAR(250) |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| second or third argument | INTEGER | INTEGER |
| in a SUBSTRING function | | |
| | | |
----------------------------------------------------------------------------------------------
Conversion of Actual Data Types to Default Data Types
Your application provides a dynamic parameter value at run time by using
either a host variable or a set of ALLBASE/SQL data structures and a data
buffer. (These coding techniques are further discussed in the section in
this document, "Programming with Dynamic Parameters.") When this actual
data type differs from the ALLBASE/SQL default data type, data conversion
takes place from the actual data type to the default data type when
either the OPEN or the EXECUTE statement executes. Conversion occurs as
follows:
* For assignment operations, if the data types are compatible. (See
the ALLBASE/SQL application programming guides and the ALLBASE/SQL
Reference Manual "Data Types" chapter for further information on
data type compatibility.)
For instance, in an INSERT VALUES clause or an UPDATE SET clause,
be sure to assign dynamic parameter data to a program variable
having a data type that is compatible with the ALLBASE/SQL default
data type for the dynamic parameter.
* For expressions involving a comparison predicate or an arithmetic
operator, conversion takes place from a smaller to a larger or
equal data type, as shown in table Table 3-8 .
For example, suppose your application specifies a host variable to
hold data for a column defined in a table as VARCHAR with a
maximum length of 32. If this host variable can hold 32 bytes or
less of character data, data conversion will take place. By
contrast, if you have defined the host variable to hold more than
32 bytes of character data, it is not smaller than the dynamic
parameter default data type, and a run time error will result.
Table 3-8. Actual to Default Data Type Conversion for Dynamic Parameters
--------------------------------------------------------------------------------------------
| | |
| Actual Data Type, | Default Data Type, |
| Based on Your Application | Based on Dynamic Parameter Usage |
| | |
--------------------------------------------------------------------------------------------
| | |
| SMALLINT, INTEGER, DECIMAL, REAL, or FLOAT | FLOAT |
| | |
--------------------------------------------------------------------------------------------
| | |
| SMALLINT, INTEGER, or DECIMAL | DECIMAL |
| | |
--------------------------------------------------------------------------------------------
| | |
| SMALLINT or INTEGER | INTEGER |
| | |
--------------------------------------------------------------------------------------------
| | |
| SMALLINT | SMALLINT |
| | |
--------------------------------------------------------------------------------------------
| | |
| CHAR(N) or VARCHAR(N) | CHAR(N+M) or VARCHAR(N+M) where M |
| | >= 0 and N > 0 |
| | |
--------------------------------------------------------------------------------------------
Note that for a non-assignment operation, when the default data type is
determined to be REAL, ALLBASE/SQL promotes it to FLOAT for better
performance and data accuracy. Therefore, the assumed data type for a
non-assignment operation is never REAL.
Data Overflow and Truncation
For character data types, no error or warning is given if truncation
occurs when an INSERT or UPDATE statement executes. For numeric data
types, when zeroes are dropped from the left or when any digit is dropped
from the fractional part of DECIMAL or FLOAT values, no error or warning
occurs. Otherwise, any overflow or underflow of numeric values causes an
error.