JDBC Concepts
2090
that when using
CallableStatement
,
ResultSetMetaData
may return
NULL
.
The following example shows a stored procedure that returns the value of
inOutParam
incremented
by 1, and the string passed in using
inputParam
as a
ResultSet
:
Example 20.3. Connector/J: Calling Stored Procedures
CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), \
INOUT inOutParam INT)
BEGIN
DECLARE z INT;
SET z = inOu 1;
SET inOutParam = z;
SELECT inputParam;
SELECT CONCAT('zyxw', inputParam);
END
To use the
demoSp
procedure with Connector/J, follow these steps:
1. Prepare the callable statement by using
Connection.prepareCall()
.
Notice that you have to use JDBC escape syntax, and that the parentheses surrounding the
parameter placeholders are not optional:
Example 20.4. Connector/J: Using
Connection.prepareCall()
import java.sql.CallableStatement;
...
//
// Prepare a call to the stored procedure 'demoSp'
// with two parameters
//
// Notice the use of JDBC-escape syntax ({call ...})
//
CallableStatement cStmt = conn.prepareCall("{call demoSp(?, ?)}");
cStmt.setString(1, "abcdefg");
Note
Connection.prepareCall()
is an expensive method, due to
the metadata retrieval that the driver performs to support output
parameters. For performance reasons, minimize unnecessary calls to
Connection.prepareCall()
by reusing
CallableStatement
instances in your code.
2. Register the output parameters (if any exist)
To retrieve the values of output parameters (parameters specified as
OUT
or
INOUT
when you
created the stored procedure), JDBC requires that they be specified before statement execution
using the various
registerOutputParameter()
methods in the
CallableStatement
interface:
Example 20.5. Connector/J: Registering output parameters
import java.sql.Types;
...
//
// Connector/J supports both named and indexed
// output parameters. You can register output
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...