 |
» |
|
|
|
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. (These methods of assigning data are discussed in the "Programming with with Dynamic Parameters" section in this document.) 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'
|
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 4-8 ALLBASE/SQL Default Data Formats for Dynamic Parameters | Type of Dynamic Parameter | ALLBASE/SQL Default Data Type | ALLBASE/SQL Default Data Format |
|---|
| 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 ADD_MONTHS function | INTEGER | INTEGER | | first or second argument in a TO_DATE, TO_TIME, TO_DATETIME, or TO_INTERVAL function | CHAR or VARCHAR | CHAR(72) | | second argument in a TO_CHAR or TO_INTEGER function | CHAR or VARCHAR | CHAR(72) | | escape character in a LIKE predicate | CHAR | CHAR(2) | | RAISE ERROR number | INTEGER | INTEGER | | RAISE ERROR text | CHAR | CHAR(250) | | second or third argument in a SUBSTRING function | INTEGER | INTEGER |
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 4-9 “Actual to Default Data Type Conversion for Dynamic Parameters”. 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 4-9 Actual to Default Data Type Conversion for Dynamic Parameters | Actual Data Type, Based on Your Application | Default Data Type, 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 | | CHAR or VARCHAR | Case Insensitive CHAR or VARCHAR
|
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. |
