SAP Adaptive
Server Enterprise Database stored procedures
One of the most significant features of SAP Adaptive Server
Enterprise is database stored procedures. You can use database stored
procedures for:
-
Retrieval only
-
Update only
-
Update and retrieval
PowerBuilder supports all these uses in embedded SQL.
Using AutoCommit with database stored
procedures
The setting of the AutoCommit property of the transaction object
determines whether PowerBuilder issues SQL statements inside or outside
the scope of a transaction. When AutoCommit is set to false or 0 (the
default), SQL statements are issued inside the scope of a transaction.
When you set AutoCommit to true or 1, SQL statements are issued outside
the scope of a transaction.
Adaptive Server Enterprise requires you to execute Data Definition
Language (DDL) statements outside the scope of a transaction unless you
set the database option “ddl in tran” to true. If you execute a database
stored procedure that contains DDL statements within the scope of a
transaction, an error message is returned and the DDL statements are
rejected. When you use the transaction object to execute a database stored
procedure that creates a temporary table, you do not want to associate the
connection with a transaction.
To execute Adaptive Server Enterprise stored procedures containing
DDL statements, you must either set “ddl in tran” to true, or set
AutoCommit to true so PowerBuilder issues the statements outside the scope
of a transaction. However, if AutoCommit is set to true, you cannot issue
a ROLLBACK. Therefore, you should set AutoCommit back to false (the
default) immediately after completing the DDL operation.
When you change the value of AutoCommit from false to true,
PowerBuilder issues a COMMIT statement by default.
Using transaction statements in database
stored procedures
Transaction statements in database stored procedures are not honored
when the stored procedure is executing within the scope of a transaction.
For example, a ROLLBACK statement will not be honored if the following are
all true:
-
The AutoCommit property is FALSE (process transactions normally)
when the transaction is connected. -
The database stored procedure executes using a
transaction. -
The procedure contains a ROLLBACK statement.
You should use alternative means to execute the ROLLBACK. For
example, you can use return values as described in the information about
triggers in Transaction management statements (SAP Adaptive Server Enterprise
Transaction management statements).
See also
SAP Adaptive Server
Enterprise Retrieval
SAP Adaptive Server
Enterprise Temporary tables
SAP Adaptive Server
Enterprise Update
SAP Adaptive Server
Enterprise Return values and output parameters
SAP Adaptive Server
Enterprise System stored procedures
SAP Adaptive Server
Enterprise Using database stored procedures in DataWindow
objects