|
|
|
When FairCom DB SQL creates a stored procedure, it converts the type of any input and output parameters (see The setParam Method: Pass Input Values to SQL Statements) from the FairCom DB SQL data types in the procedure specification to Java wrapper types.
The Java.lang package defines classes for all the primitive Java types that “wrap” values of the corresponding primitive type in an object. FairCom DB SQL converts the FairCom DB SQL data types declared for input and output parameters to one of these wrapper types, described in the table below.
You must be sure to use wrapper types when declaring procedure variables to use as arguments to the getValue(), setParam(), and set methods. These methods take objects as arguments and will generate compilation errors if you pass a primitive type to them.
The following example shows two stored procedures. The first tries to use a variable declared as the Java int primitive type as an argument to the SQLResultSet.set() method, and will generate a compilation error. The second correctly declares the variable using the integer wrapper class.
Using Wrapper Types as Arguments to FairCom DB SQL Classes
CREATE PROCEDURE type_mismatch( )
RESULT (res_int INTEGER )
BEGIN
// Create a variable as (primitive) type int
// to test the working of set method of SQLResultSet
int pvar_int = 4;
// Transfer the value from the procedure variable to the result set.
SQLResultSet.set(1,pvar_int); //Error!
// Insert the row into the procedure result set.
SQLResultSet.insert();
END
CREATE PROCEDURE type_okmatch( )
RESULT (res_int INTEGER )
BEGIN
// Create a variable as (wrapper) type Integer
Integer pvar_int = new Integer(4);
// Transfer the value from the procedure variable to the result set.
SQLResultSet.set(1,pvar_int); //Success!
// Insert the row into the procedure result set.
SQLResultSet.insert();
END
When the SQL engine submits the Java class it creates from the stored procedure to the Java compiler, the compiler checks for data-type consistency between the converted parameters and variables you declare in the body of the stored procedure.
To avoid type mismatch errors, use the data-type mappings shown in the following table for declaring parameters and result-set fields in the procedure specification and the Java variables in the procedure body.
SQL Type |
Java Wrapper Type |
Enumeration Type |
|---|---|---|
CHAR |
String |
CHAR |
NCHAR |
String |
NCHAR |
VARCHAR |
String |
VARCHAR |
LONGVARCHAR |
String |
LONGVARCHAR |
NVARCHAR |
String |
NVARCHAR |
NUMERIC |
java.math.BigDecimal |
NUMERIC |
DECIMAL |
java.math.BigDecimal |
DECIMAL |
MONEY |
java.math.BigDecimal |
MONEY |
BIT |
Boolean |
BIT |
TINYINT |
byte[1] |
TINYINT |
SMALLINT |
Integer |
SMALLINT |
INTEGER |
Integer |
INTEGER |
BIGINT |
Long |
BIGINT |
REAL |
Float |
REAL |
FLOAT |
Double |
FLOAT |
DOUBLE PRECISION |
Double |
DOUBLE |
BINARY |
byte[ ] |
BINARY |
VARBINARY |
byte[ ] |
VARBINARY |
LONGVARBINARY |
byte[ ] |
LONGVARBINARY |
DATE |
java.sql.Date |
DATE |
TIME |
java.sql.Time |
TIME |
TIMESTAMP |
java.sql.Timestamp |
TIMESTAMP |
