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

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 37) and Chapter 6
(page 47).
This chapter covers these topics:
• “Required Privileges for Calling an SPJ”
• “Transaction Behavior”
• “Multithreading” (page 52)
• “Using the CALL Statement” (page 52)
• “Calling SPJs in NCI” (page 54)
• “Calling SPJs From an ODBC Client Application” (page 56)
• “Calling SPJs From a JDBC Client Application” (page 57)
• “Calling an SPJ in a Trigger” (page 59)
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:
• NCI 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 NCI. 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 47).
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.
Transaction Statements or Methods in an SPJ Method
IMPORTANT: 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() and
Connection.rollback().
If you try to use transaction control statements or methods in an SPJ method, the statements or
methods have no effect on the transaction, and the Java virtual machine (JVM) does not report
any errors or warnings. Instead, use transaction control statements or methods in the application
that calls the SPJ and allow the calling application to manage the transactions.
Committing or Rolling Back a Transaction
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
Required Privileges for Calling an SPJ 51