Java Reference
In-Depth Information
CallableStatement extends PreparedStatement, so a CallableStatement object can
take input parameters as a PreparedStatement object can. A CallableStatement can
also take output parameters or parameters that are for both input and output.
This escape syntax used to call a stored procedure has two forms: one that includes
a result parameter and one that does not. Here's an example:
{?= call <procedure-name>[<arg1>,<arg2>, ...]}
{call <procedure-name>[<arg1>,<arg2>, ...]}
Question marks (?) serve as placeholders for parameters defined in the stored
procedure using the @Name convention as shown in the example. IN parameter
values are set using the set methods inherited from PreparedStatement. If used, the
result parameter must be registered as an OUT parameter using the
registerOutParameter() method before one of the execute methods is called.
Consider this example:
cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);
OUT parameter values can be retrieved after execution using get methods
appropriate to the data types of the values.
Listing 4-4
is an example of a simple stored procedure that checks a user name and
password against the database, returning the String "PASS" if a match is found or
"FAIL" otherwise:
Listing 4-4:
Stored procedure with input and output parameters
CREATE PROCEDURE CHECK_USER_NAME
@UserName varchar(30),
@Password varchar(20),
@PassFail varchar(20) OUTPUT
As
IF EXISTS(Select * From Customers
Where UserName = @UserName
And
Password = @Password)
SELECT @PassFail = "PASS"
else
SELECT @PassFail = "FAIL"
Because of limitations imposed by some relational database management systems, it
is recommended that, for maximum portability, all of the results generated by the
execution of a CallableStatement object be retrieved before OUT parameters are
retrieved.
