Executing Stored Procedures

Go Up to Developing InterClient Programs

A stored procedure is a self-contained set of extended SQL statements that are stored in a database as part of its metadata. Stored procedures can pass parameters to and receive return values from applications. From the application, you can invoke a stored procedure directly to perform a task, or you can substitute the stored procedure for a table or view in a SELECT statement. There are two types of stored procedures:

Select procedures are used in place of a table or view in a SELECT statement. A selectable procedure generally has no IN parameters. See note below.
Executable procedures can be called directly from an application with the EXECUTE PROCEDURE statement; they may or may not return values to the calling program.

Use the Statement class to call select or executable procedures that have no SQL input (IN) parameters. Use the PreparedStatement class to call select or executable stored procedures that have IN parameters.

Note:
Although it is not commonly done, it is possible to use IN parameters in a SELECT statement. For example:

create procedure with_in_params(in_var integer)
returns (out_data varchar(10))
as
begin
  for select a_field1 from a_table
  where a_field2 = :in_var
  into :out_data
  do suspend;
end

To return one row:

execute procedure with_in_params(1)

To return more than one row:

select * from with_in_params(1)

Statement Example

An InterClient application can call a select procedure in place of a table or view inside a SELECT statement. For example, the stored procedure multiplyby10 multiplies all the rows in the NUMBERS table (visible only to the stored procedure) by 10, and returns the values in the result set. The following example uses the Statement.executeQuery() method to call the multiplyby10 stored procedure, assuming that you have already created the Connection and Statement objects:

//multiplyby10 multiplies the values in the resultOne, resultTwo, //resultThree columns of each row of the NUMBERS table by 10
//create a string object
String sql= new String ("SELECT resultone, resulttwo, resultthree FROM multiplyby10");
//Execute a SELECT statement and store results in resultSet:
java.sql.ResultSet resultSet = statement.executeQuery(sql);
//Step through the result rows
System.out.println("Got results:");
while (resultSet.next ()){
//get the values for the current row
int result1 = resultSet.getInt(1);
int result2 = resultSet.getInt(2);
int result3 = resultSet.getInt(3);
//print the values
System.out.print(" result one =" + result1);
System.out.print(" result two =" + result2);
System.out.print(" result three =" + result3);
System.out.print("\n");
}

PreparedStatement Example

In the example below, the multiply stored procedure is not selectable. Therefore, you have to call the procedure with the PreparedStatement class. The procedure arguments are the scale factor and the value of KEYCOL that uniquely identifies the row to be multiplied in the NUMBERS table.

//Define a PreparedStatement object type
java.sql.PreparedStatement preparedStatement;
//Create a new string object
java.sql.String sql = new String ("EXECUTE PROCEDURE multiply 10, 1");
//Create the PreparedStatement object
preparedStatement = connection.prepareStatement(sql);
//execute the stored procedure with preparedStatement
java.sql.ResultSet resultSet = preparedStatement.executeQuery(sql);
//step through the result set and print out as in Statement example

Advance To:

Troubleshooting InterClient Programs