Neoview Guide to Stored Procedures in Java (R2.2)

7 Executing SPJs
This chapter describes how to execute SPJs by using the CALL statement and assumes that you
have already registered the SPJs in the Neoview database and that you have granted privileges
to execute the SPJs to the appropriate roles. For information, see Chapter 5 (page 43) and Chapter 6
(page 51).
This chapter covers these topics:
• “Required Privileges for Calling an SPJ”
• “Transaction Behavior”
• “Multithreading” (page 64)
• “Using the CALL Statement” (page 64)
• “Calling SPJs in Neoview Script” (page 66)
• “Calling SPJs From an ODBC Client Application” (page 68)
• “Calling SPJs From a JDBC Client Application” (page 69)
• “Calling an SPJ in a Trigger” (page 70)
The CALL statement invokes an SPJ in a Neoview database. You can issue a CALL statement
from any of these applications or interfaces supported by the Neoview platform:
• Neoview Script command-line interface or script file
• JDBC Type 4 client applications
• ODBC client applications
You can use a CALL statement as a stand-alone SQL statement in applications or command-line
interfaces, such as Neoview Script. You can also use a CALL statement in a trigger but not inside
a compound statement or with rowsets. The SPJ that you use in a trigger must not have any OUT
or INOUT parameters or return any result sets.
Required Privileges for Calling an SPJ
To execute the CALL statement, your user role must have the EXECUTE privilege on the
procedure. For more information, see Chapter 6 (page 51).
Transaction Behavior
A CALL statement automatically initiates a transaction if there is no active transaction. An SPJ
method invoked by a CALL statement inherits the transaction from the calling application. To
enable the calling application to manage the transaction, do not use transaction control statements
(or equivalent JDBC transaction methods) in the SPJ method. Transaction control statements
include COMMIT WORK and ROLLBACK WORK, and the equivalent JDBC transaction methods
are Connection.commit() or Connection.rollback().
If you do not use transaction control statements in the calling application, the transaction initiated
by the CALL statement might not automatically commit or roll back changes to the database.
When AUTOCOMMIT is ON (the default setting), Neoview SQL automatically commits or rolls
back any changes made to the database at the end of the CALL statement execution. However,
when AUTOCOMMIT is OFF, the current transaction remains active until the end of the client
session or until you explicitly commit or roll back the transaction. To ensure an atomic unit of
work when calling an SPJ, use the COMMIT WORK statement in the calling application to commit
the transaction when the CALL statement succeeds, and use the ROLLBACK WORK statement
to roll back the transaction when the CALL statement fails. For more information about transaction
management, see the Neoview SQL Reference Manual.
Required Privileges for Calling an SPJ 63