Progress
SQL-92
Guide and Reference


Implicit Data Type Conversion Between SQL-92 and Java Types

When the SQL server creates a stored procedure, it converts the type of any input and output parameters. See the "Passing Values to SQL-92 Statements" section.

The java.lang package, part of the Java core classes, defines classes for all the primitive Java types that “wrap” values of the corresponding primitive type in an object. The SQL server converts the SQL data types declared for input and output parameters to one of these wrapper types, as shown in Table 5–2.

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. See also the example at the end of the SQLCursor section i n Java Class Reference."

EXAMPLE

The following example illustrates the use of the Java wrapper type Long for a SQL type BIGINT:

CREATE PROCEDURE proc1(INOUT f1 char(50), INOUT f2 BIGINT)
 
BEGIN
   f1 = new String("new rising sun");
   f2 = new Java.math.BigInteger("999");
END
 
CREATE PROCEDURE proc2()
 
BEGIN
   String in1 = new String("String type");
   String out1 = new String();
   Long out2 = new Long("0");
 
  SQLCursor call_proc = new SQLCursor("call proc1(?, ?)");
   call_proc.setParam(1,in1);
   // In setParam you can use either String or String type
   // for SQL types CHAR, and VARCHAR
 
   call_proc.setParam(2,new Long("111"));
   call_proc.open();
 
   out1 = (String)call_proc.getParam(1,CHAR); 
            
   // getParam requires String type for CHAR
     out2 = (Long)call_proc.getParam(2,BIGINT);
        
   call_proc.close();
END 

When the SQL server 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 Table 5–2 for declaring parameters and result-set fields in the procedure specification and the Java variables in the procedure body:

Table 5–2: Mapping Between SQL-92 and Java Data Types 
SQL Type
Java Methods
Java Wrapper Type
CHAR, VARCHAR
all
String
CHAR, VARCHAR
set, setParam
String
NUMERIC
all
java.math.BigDecimal
DECIMAL
all
java.math.BigDecimal
BIT
all
Boolean
TINYINT
all
Byte[1]
SMALLINT
all
Integer
INTEGER
all
Integer
REAL
all
Float
FLOAT
all
Double
DOUBLE PRECISION
all
Double
BINARY
all
Byte[ ]
VARBINARY
all
Byte[ ]
DATE
all
java.sql.Date
TIME
all
java.sql.Time
TIMESTAMP
all
java.sql.Timestamp


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095