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:
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.
EXAMPLEThis 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:
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.
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.
EXAMPLEThis example shows how the getValue() method works. This method returns a java object that must be cast to the corresponding SQL data type :
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |