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. A selectable procedure has no IN parameters.
-
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.
Send comments or suggestions to icsupport@interbase.com