Database Programming - Java in 60 Minutes a Day - page 658

♦ Stored Procedures in Microsoft Access (continued)

Enter the procedure defined in Figure 18.6. After you have entered it, click File and then
Save. Save the module as “StoredProcedures”.
The Visual Basic statements of interest in this procedure are:

Set query = .CreateQueryDef(“SelectByCategory”, _
“PARAMETERS category String; SELECT * FROM Movies WHERE
Movies.category = [category]”)
The CreateQueryDef function creates a stored procedure. The first argument is the
name of the stored procedure, which in this example is SelectByCategory. The second argu-
ment is the SQL of the procedure, which in this example selects the rows in the Movies
table that match the given category parameter.
After you have entered the AddNewStoredProcedure() subroutine from Figure 18.6, you
need to run it so that the stored procedure gets created. Run the subroutine by Go/Con-
tinue from the Run menu (or by selecting F5).
After you have successfully run the subroutine, the stored procedure SelectByCategory
is now ready to be invoked from your Java applications using the SQL statement:

{call SelectByCategory(?)}
of a CallableStatement object, as demonstrated by the showByCategory() method of the
MovieDatabase class. Be sure to close the Module window and the movies.mdb database
before running the CallableDemo program.

Use one of the prepareCall() methods of the Connection interface to create a
CallableStatement object:

public CallableStatement prepareCall(String sql) throws SQLException.
Creates a callable statement using the given SQL statement.
public CallableStatement prepareCall(String sql, int resultSetType, int
concurrency) throws SQLException. Creates a callable statement
using the given SQL statement, result set type, and concurrency type.
public CallableStatement prepareCall(String sql, int resultSetType, int
concurrency, int holdability) throws SQLException. Creates a callable
statement using the given SQL statement, result set type, concurrency
type, and holdability type.

The SQL for invoking a callable procedure that has parameters looks similar to:

{call procedure_name (?, ?, ...)}