3.9 Interacting with Stored Procedures

You can provide a StoredProcedureCall object to any query instead of an expression or a SQL string, but the procedure must return all data required to build an instance of the class you query.

Example 3-14 A Read-All Query with a Stored Procedure

ReadAllQuery readAllQuery = new ReadAllQuery();
call = new StoredProcedureCall();
call.setProcedureName("Read_All_Employees");
readAllQuery.setCall(call);
Vector employees = (Vector) session.executeQuery(readAllQuery);

Using a StoredProcedureCall, you can access the following:

Specifying an Input Parameter
Specifying an Output Parameter
Specifying an Input / Output Parameter
Using an Output Parameter Event

Note:

You no longer need to use DatabaseQuery method bindAllParameters when using a StoredProcedureCall with OUT or INOUT parameters. However, you should always specify the Java type for all OUT and INOUT parameters. If you do not, be aware of the fact that they default to type String.

3.9.1 Specifying an Input Parameter

In Example 3-15, you specify the parameter POSTAL_CODE as an input parameter using the StoredProcedureCall method addNamedArgument, and you can specify the value of the argument using method addNamedArgumentValue.

Example 3-15 Stored Procedure Call with an Input Parameter

StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("CHECK_VALID_POSTAL_CODE");
call.addNamedArgument("POSTAL_CODE");
call.addNamedArgumentValue("L5J1H5");
call.addNamedOutputArgument(
    "IS_VALID",    // procedure parameter name
    "IS_VALID",    // out argument field name
    Integer.class  // Java type corresponding to type returned by procedure
);
ValueReadQuery query = new ValueReadQuery();
query.setCall(call);
Number isValid = (Number) session.executeQuery(query);

The order in which you add arguments must correspond to the order in which you add argument values. In Example 3-16, the argument NAME is bound to the value Juliet and the argument SALARY is bound to the value 80000.

Example 3-16 Matching Arguments and Values in a Stored Procedure Call

StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("CHECK_VALID_POSTAL_CODE");
call.addNamedArgument("NAME");
call.addNamedArgument("SALARY");
call.addNamedArgumentValue("Juliet");
call.addNamedArgumentValue(80000);

3.9.2 Specifying an Output Parameter

Output parameters enable the stored procedure to return additional information. You can use output parameters to define a readObjectQuery if they return all the fields required to build the object.

In Example 3-17, you specify the parameter IS_VALID as an output parameter using the StoredProcedureCall method addNamedOutputArgument.

Example 3-17 Stored Procedure Call with an Output Parameter

StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("CHECK_VALID_POSTAL_CODE");
call.addNamedArgument("POSTAL_CODE");
call.addNamedOutputArgument(
    "IS_VALID",    // procedure parameter name
    "IS_VALID",    // out argument field name
    Integer.class  // Java type corresponding to type returned by procedure
);
ValueReadQuery query = new ValueReadQuery();
query.setCall(call);
query.addArgument("POSTAL_CODE");
Vector parameters = new Vector();
parameters.addElement("L5J1H5");
Number isValid = (Number) session.executeQuery(query,parameters);

Note:

Not all databases support the use of output parameters to return data. However, because these databases generally support returning result sets from stored procedures, they do not require output parameters.

If you are using an Oracle database, you can make use of TopLink cursor and stream query results.

3.9.3 Specifying an Input / Output Parameter

In Example 3-18, you specify the parameter LENGTH as an input/output parameter and specify the value of the argument when it is passed to the stored procedure using the StoredProcedureCall method addNamedInOutputArgumentValue. If you do not want to specify a value for the argument, use method addNamedInOutputArgument.

Example 3-18 Stored Procedure Call with an Input/Output Parameter

StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("CONVERT_FEET_TO_METERs");
call.addNamedInOutputArgumentValue(
    "LENGTH",          // procedure parameter name
    new Integer(100),  // in argument value
    "LENGTH",          // out argument field name
    Integer.class      // Java type corresponding to type returned by procedure
)
ValueReadQuery query = new ValueReadQuery();
query.setCall(call);
Integer metricLength = (Integer) session.executeQuery(query);

3.9.4 Using an Output Parameter Event

TopLink manages output parameter events for databases that support them. For example, if a stored procedure returns an error code that indicates that the application wants to check for an error condition, TopLink raises the session event OutputParametersDetected to allow the application to process the output parameters.

Example 3-19 Stored Procedure with Reset Set and Output Parameter Error Code

StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("READ_EMPLOYEE");
call.addNamedArgument("EMP_ID");
call.addNamedOutputArgument(
    "ERROR_CODE",    // procedure parameter name
    "ERROR_CODE",    // out argument field name
    Integer.class  // Java type corresponding to type returned by procedure
);
ReadObjectQuery query = new ReadObjectQuery();
query.setCall(call);
query.addArgument("EMP_ID");
ErrorCodeListener listener = new ErrorCodeListener();
session.getEventManager().addListener(listener);
Vector args = new Vector();
args.addElement(new Integer(44));
Employee employee = (Employee) session.executeQuery(query, args);

3.9.5 Using a StoredFunctionCall

You use a StoredProcedureCall to invoke stored procedures defined on databases that support them. You can also use a StoredFunctionCall to invoke stored functions defined on databases that support them, that is, on databases for which the DatabasePlatform method supportsStoredFunctions returns true.

In general, both stored procedures and stored functions let you specify input parameters, output parameters, and input and output parameters. However, stored procedures need not return values, while stored functions always return a single value.

The StoredFunctionCall class extends StoredProcedureCall to add one new method: setResult. Use this method to specify the name (and alternatively both the name and type) under which TopLink stores the return value of the stored function.

When TopLink prepares a StoredFunctionCall, it validates its SQL and throws a ValidationException under the following circumstances:

If your current platform does not support stored functions
If you fail to specify the return type

In Example 3-20, note that the name of the stored function is set using StoredFunctionCall method setProcedureName.

Example 3-20 Creating a StoredFunctionCall

StoredFunctionCall functionCall = new StoredFunctionCall();
functionCall.setProcedureName("READ_EMPLOYEE");
functionCall.addNamedArgument("EMP_ID");
functionCall.setResult("FUNCTION_RESULT", String);
ReadObjectQuery query = new ReadObjectQuery();
query.setCall(functionCall);
query.addArgument("EMP_ID");
Vector args = new Vector();
args.addElement(new Integer(44));
Employee employee = (Employee) session.executeQuery(query, args);

3.9.6 Query Sequencing

With query sequencing, you can access a sequence resource using custom read (ValueReadQuery) and update (DataModifyQuery) queries and a preallocation size that you specify. This allows you to perform sequencing using stored procedures and allows you to access sequence resources that are not supported by the other sequencing types that TopLink provides.