Progress
SQL-92
Guide and Reference


Passing Values to SQL-92 Statements

Stored procedures must be able to pass and receive values from SQL statements they execute. They do this through the setParam and getValue methods.

The setParam Method: Pass Input Values to SQL Statements

The setParam method sets the value of an SQL statement’s parameter marker to the specified value (a literal value, a procedure variable, or a procedure input parameter).

The setParam method takes two arguments. This is the syntax for setParam:

SYNTAX
setParam ( marker_num , value ) ; 

marker_num

Specifies the ordinal number of the parameter marker in the SQL statement that is to receive the value as an integer. 1 denotes the first parameter marker, 2 denotes the second, n denotes the nth.

value

Specifies a literal, variable name, or input parameter that contains the value to be assigned to the parameter marker.

EXAMPLE

This example shows an excerpt from a stored procedure that uses setParam to assign values from two procedure variables to the parameter markers in an SQL INSERT statement. When the procedure executes, it substitutes the value of the cust_number procedure variable for the first parameter marker and the value of the cust_name variable for the second parameter marker:

SQLIStatement insert_cust = new SQLIStatement (
     "INSERT INTO customer VALUES (?,?) ");
     insert_cust.setParam (1, cust_number);
     insert_cust.setParam (2, cust_name); 

The getValue Method: Pass Values from SQL Result Sets to Variables

The getValue method of the SQLCursor class assigns a single value from an SQL result set (returned by an SQL query or another stored procedure) to a procedure variable or output parameter.

SYNTAX
getValue ( col_num , sql_data_type ) ; 

col_num

Specifies the desired column of the result set as integer. getValue retrieves the value in the currently fetched record of the column denoted by col_num. ’1’ denotes the first column of the result set, ’2’ denotes the second, n denotes the nth.

sql_data_type

Specifies the corresponding SQL data type. For a complete list of appropriate data types, refer to Table 5–2.

EXAMPLE

This example shows how the getValue() method works. This method returns a java object that must be cast to the corresponding SQL data type :

cnum = (Integer) NEWROW.getValue(1, INTEGER); 
cnname = (String) NEWROW.getValue(1, CHARACTER);   


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