FairCom Java & .NET Stored Procedures

Passing Values to SQL Statements

Stored procedures need to be able to pass and receive values from FairCom DB 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 a FairCom DB SQL statement’s parameter marker to the specified value (a literal, a procedure variable, or a procedure input parameter).

The setParam() method takes two arguments:

setParam ( marker_num , value ) ;

• marker_num is an integer that specifies which parameter marker in the SQL statement is to receive the value (1 denotes the first parameter marker, 2 denotes the second, and so on).
• value is a literal or the name of a variable or input parameter that contains the value to be assigned to the parameter marker.

The following 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.

setParam() and FairCom DB SQL Statement Input Parameters

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.

The format and arguments for getValue() are as follows:

getValue ( col_num , data_type ) ;

The getValue() interface returns an object, it should be explicitly typecasted to appropriate type.

• col_num is an integer that specifies which column of the result set is of interest. 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, and so on).
• data_type specifies the expected FairCom DB SQL type of the returned parameter (see Implicit Data Type Conversion Between SQL and Java Types for details on how FairCom DB SQL data types map to Java data types).

The following example shows an excerpt from a stored procedure that uses getValue() to assign values from two result-set columns to procedure variables. In this example, the result set is generated by a FairCom DB SQL SELECT statement.

Using getValue() to Pass Values from Result Sets

StringBuffer ename = new StringBuffer (20) ;

java.math.BigDecimal esal = new java.math.BigDecimal () ;

SQLCursor empcursor = new SQLCursor (

"SELECT name, sal FROM emp " ) ;

empcursor.open () ;

empcursor.fetch ();

if (empcursor.found ())

{

ename = (StringBuffer)empcursor.getValue (1, CHAR);

esal = (BigDecimal)empcursor.getValue (2, DECIMAL);

.

.

.

In the SELECT statement in previous example, it was clear that the result set had two columns, name and sal. If the SELECT statement had used a wildcard in its select list (SELECT * FROM EMP) you have to know the structure of the EMP table in order to correctly specify the column numbers in the getValue() method.