Reference | Help | Introduction | Slide Show | Class Hierarchy | InterClient
PREV | NEXT | FRAMES | NO FRAMES |  |

## Executing Stored Procedures

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. Selectable stored procedures must be valid stored procedures that have SUSPEND commands and at least one return parameter.
- 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.

#### Statement Example

An InterClient application can call a select procedure in place of a table or view inside a SELECT statement. A select procedure returns one or more values, but has no input arguments. 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 (assume you've 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
    sql= new String ("SELECT resultone, resulttwo, resultthree FROM multiplyby10");

    //Execute a SELECT statement and store results in resSet:
    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 because it has input parameters. 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);

Also see the InterClient API reference for the CallableStatement class.

Reference | Help | Introduction | Slide Show | Class Hierarchy | InterClient
PREV | NEXT | FRAMES | NO FRAMES |  |

Send comments or suggestions to interclient@interbase.com