SQL/MX 3.2.1 Guide to Stored Procedures in Java (H06.26+, J06.15+)

/* Execute the CALL statement. */
SQLExecDirect(hstmt, "{call adjustsalary(?,?,?)}", SQL_NTS);
NOTE: Nonstop ODBC/MX Client does not support SQLExecDirect API for Stored Procedure in
Java with Result Sets (SPJ RS).
For more information about NonStop ODBC/MX clients, see the ODBC/MX Driver for Windows
Manual.
Invoking SPJs in a JDBC/MX Program
You can execute a CALL statement in a JDBC/MX program by using the JDBC
CallableStatement interface. JDBC/MX requires that you put the CALL statement in an escape
clause:
{call procedure-name([parameter[{, parameter}...]])}
Set input values for IN and INOUT parameters by using the settype() methods of the
CallableStatement interface.
Retrieve output values from OUT and INOUT parameters by using the gettype() methods of the
CallableStatement interface.
If the parameter mode is OUT or INOUT, you must register the parameter as an output parameter
by using the registerOutParameter() method of the CallableStatement interface before
executing the CALL statement.
In this example, a CALL statement is executed from a JDBC/MX program:
CallableStatement stmt =
con.prepareCall("{call adjustsalary(?,?,?)}");
stmt.setBigDecimal(1,202); // x = 202
stmt.setDouble(2,5.5); // y = 5.5
stmt.registerOutParameter(3, java.sql.Types.NUMERIC);
stmt.execute();
int z = stmt.getBigDecimal(3); // Retrieve the value of the
// OUT parameter
For more information about JDBC/MX and mappings of SQL/MX to JDBC d ata types, see the
JDBC Driver for SQL/MX Programmer's Reference.
Invoking SPJs in a Trigger
A trigger is a mechanism in the database that enables the database engine to perform certain
actions when a specified event occurs. SPJs are useful as triggered actions, because they can help
you encapsulate and enforce rules in the database. For more information about the benefits of
using SPJs, see Benefits of SPJs (page 19). Nonstop SQL/MX supports a CALL statement in a trigger,
provided that the SPJ in the CALL statement does not have any OUT or INOUT parameters or return
any result sets. For information about OUT and INOUT parameters, see Returning Output Values
From the Java Method (page 50) and Output Parameter Arguments (page 74). For more information
about result sets, see Stored Procedure Result Sets (page 51).
Example
Consider a library environment, where each time a member borrows a book, the member is charged
a fee. If the fees exceeds the credit limit, the student is blocked. The Database Administrator (DBA)
needs to update the dues and block the students who have crossed the limit of credit. Also, DBA
needs to be informed if a user has been blocked by some external means. One might think that a
SIGNAL statement can be used for this task to emit a message to the console for the same. The
Invoking SPJs in a JDBC/MX Program 81