SQL/MX 3.2 Guide to Stored Procedures in Java (H06.25+, J06.14+)

Invoking SPJs in NonStop SQL/MX

HP NonStop SQL/MX Release 3.2 Guide to Stored Procedures in Java—691166-001

5-2

Effect of Calling an SPJ

When you issue a CALL statement, the SPJ method of the invoked SPJ executes

inside a JVM within an SPJ environment. For more information, see the Effect of

Invoking an SPJ on page 1-9.

Transaction Behavior

A CALL statement automatically initiates a transaction if there is no active transaction.

An SPJ method invoked by a CALL statement usually inherits the transaction from its

caller, except when jdbcmx.transactonMode is set to internal. For more

information, see the Effect of the jdbcmx.transactionMode Property on page 5-3.

Transaction Statements or Methods in an SPJ Method

You can start transactions within a stored procedure by performing the following steps:

1. Suspend the current transaction (if supported by the API).

2. Start a new transaction.

3. Complete the work.

4. End the new transaction.

5. Resume the current transaction (if suspended).

You must ensure that the transactions started within the stored procedure are cleaned

up properly. Some of the Java APIs do not support the ability to suspend and resume

transactions. In this case, TMF marks the newly started transaction as the current

transaction even though original transaction is still active, because an SPJ will be

executed in a multi-threaded environment.

Committing or Rolling Back a Transaction

In an application, the failure of a CALL statement does not always automatically abort

a transaction:



When AUTOCOMMIT is ON during a system-initiated transaction, errors that occur

during the execution of a CALL statement cause NonStop SQL/MX to roll back the

transaction automatically, including database operations performed by the SPJ

method, at the end of statement execution.

Caution. NonStop SQL/MX does not maintain the Java class file that contains the SPJ

method. NonStop SQL/MX records only a reference to the location of the Java class file in

system metadata. Subsequent changes to the Java class file after you register an SPJ might

result in run-time errors when you try to execute it. For guidelines on how to alter an SPJ, see

Altering an SPJ and Its Java Class

on page 4-14.

Note. SPJs that return data via result sets are not allowed to use transactions started within an

SPJ for statements that populate the result set.