We're attempting to migrate our large 20+ year old application to PowerServer 2022 R2. We have an ORACLE database, and have a number of RPCs as well as a few datawindows using stored procedure updates. Some of the remote procedures have PRAGMA autonomous transactions with their own commit/rollback, some are not autonomous transactions but do have commit/rollback in them, some return values with no DB updates, and some do DB updates and rely on the script calling them to perform the commit/rollback. Autocommit is false.

As a result, some of the RPC calls should start the transaction when they are executed, and some should not.

My question is: Can the transaction object's dbparm value be changed in the sqlpreview event of the transaction object itself, to set the appropriate value of ProcedureInTransaction based on the name of the RPC (which is found in the sqlsyntax argument), or does it need to be set earlier than that?

Here is some of the code in n_tr.sqlpreview event we're hoping to use. It includes some logging to a file, so we can see that the values are getting recognized and set, but we don't know if the changing of ProcedureInTransaction to the desired value of 0 or 1 is actually effective at this point.

IF gb_ispowerserverapp THEN
	integer i, li_FileNum
	String	ls_sqlfunc, ls_syntax
	String	ls_default

	CHOOSE CASE sqlfunc
		CASE SQLDBProcedure!,SQLDBRPC!
			CHOOSE CASE sqlfunc
				CASE SQLDBProcedure!
					ls_sqlfunc = "SQLDBProcedure"
					CHOOSE CASE upper(trim(ls_syntax))
						CASE	"EXECUTE CB_LEASE_PMT"	,&
							"EXECUTE CB6.CB_DATAACCESS.P_CBXML_INSERT"	,&
							"EXECUTE DBMS_SESSION.SET_IDENTIFIER"	
							this.of_set_procedureintransaction(0)
						CASE	"EXECUTE CB6.CB_DATAACCESS.P_CBATTACHMENT_DELETE"	,&
							"EXECUTE CB6.CB_DATAACCESS.P_CBATTACHMENT_INSERT"	,&
							"EXECUTE CB6.CB_DATAACCESS.P_CBATTACHMENT_UPDATE"	
							this.of_set_procedureintransaction(1)
						CASE ELSE
							this.of_set_procedureintransaction(1)
							ls_default = "~tNO MATCH IN CASE, DEFAULT TO 1"
					END CHOOSE //upper(trim(ls_syntax))
				CASE SQLDBRPC!
					ls_sqlfunc = "SQLDBRPC"
					CHOOSE CASE trim(ls_syntax)
						CASE	"RPCEXECUTE CB6.CB_DATAACCESS.F_CBATTACHMENT_BLOB_UPDATE"	,&
							"RPCEXECUTE CB6.CB_DATAACCESS.F_CBERROR_INSERT"	,&
							"RPCEXECUTE CB6.PKG_TERRITORY.VALIDATE_MODEL"
							this.of_set_procedureintransaction(0)
						CASE	"RPCEXECUTE CB6.ARCHIVE_AND_DELETE.CANCEL_BATCH"	,&
							"RPCEXECUTE CB6.ARCHIVE_AND_DELETE.CREATE_BATCH"	,&
							"RPCEXECUTE CB6.PKG_TERRITORY.REFRESH_UPDATED_COMPANY"	
							this.of_set_procedureintransaction(1)
						CASE ELSE
							this.of_set_procedureintransaction(1)
							ls_default = "~tNO MATCH IN CASE, DEFAULT TO 1"
					END CHOOSE //trim(ls_syntax)
			END CHOOSE //sqlfunc inner
			li_FileNum = FileOpen("C:\Temp\RPCCALLS.txt",LineMode!,Write!,LockReadWrite!,Append!,EncodingANSI!)
			FileWrite(li_FileNum, string(today(),"yymmdd hh:mm:ss") + "~t" + ls_sqlfunc + "~t~t" + ls_syntax + ls_default)
			FileClose(li_FileNum)
		CASE else
			this.of_set_procedureintransaction(1) //set back to a 1 ??
			//other sqldb calls, show 1st 240 chars, but change carriage return and linefeed so shows on 1 line in logfile, but can convert back if needed when examining log
			ls_syntax = sqlsyntax
			ls_syntax = f_globalreplace(mid(ls_syntax,1,240),"~r","<CR>",true)
			ls_syntax = f_globalreplace(mid(ls_syntax,1,240),"~n","<LF>",true)
			li_FileNum = FileOpen("C:\Temp\RPCCALLS.txt",LineMode!,Write!,LockReadWrite!,Append!,EncodingANSI!)
			FileWrite(li_FileNum, string(today(),"yymmdd hh:mm:ss") + "~t" + ls_syntax )
			FileClose(li_FileNum)
	END CHOOSE //sqlfunc outer
END IF