Description
Allows PowerBuilder to specify whether to start the transaction when executing a stored procedure for PowerServer (2021, 2022, or later).
Applies to
ASE (ODBC)
MySQL
Oracle
PostgreSQL
SQL Server
SQL Anywhere (ODBC)
Syntax
ProcedureInTransaction=value
-
0 -- Do not start the transaction.
-
1 (Default) -- Start the transaction
Usage
In the installable cloud app, to prevent transaction timeout (when ProcedureInTransaction is set to 1), you may consider the following solutions:
-
Increase the transaction timeout value (120 seconds by default) to have the same number as the session timeout value (3600 seconds by default). For how to set transaction timeout values, refer to Configure the timeout settings.
-
First set ProcedureInTransaction=0. When the procedure or cursor requires to start the transaction, dynamically set ProcedureInTransaction=1 in the script to start the transaction and then set autocommit = true or execute Commit or RollBack to commit/close the transaction in time (see the example code below). In this case, you can set a reasonable value for the transaction timeout according to the business logics.
Examples
This example specifies to start a transaction when executing a stored procedure.
string ls_sql
long ll_deptid
int li_type
//Executes a procedure and starts the transaction
li_type=1
ll_deptid = 700
sqlca.dbparm = gs_dbparm+",procedureintransaction=1"
declare mypro procedure for pro_sp_dept_update :li_type,:ll_deptid;
execute mypro;
if sqlca.sqlcode = -1 then
rollback;
else
commit;
end if
close mypro;
//do not start transaction when executing a procedure
sqlca.dbparm = gs_dbparm+",procedureintransaction=0"
