Kbase P178041: Calls to stored procedures are not rolled back after an UNDO statement within a transaction block
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
26/11/2010 |
|
Status: Unverified
SYMPTOM(s):
Calls to stored procedures are not rolled back after an UNDO statement within a transaction block
The following code illustrates the behavior:
DEF VAR i# AS INT INIT ?.
DEF VAR j# AS INT.
DEF VAR k# AS INT INIT ?.
DEF VAR l# AS INT INIT ?.
DO TRANSACTION ON ERROR UNDO, LEAVE:
DO i# = 1 TO 10:
RUN STORED-PROCEDURE SPSetCustomer j# = PROC-HANDLE
(
(INPUT-OUTPUT PARAM ID = k#,
INPUT PARAM FirstName = "John",
INPUT PARAM Name = "Doe",
INPUT PARAM Gender = "M",
INPUT PARAM AddressCode = l#
).
CLOSE STORED-PROC SPSetCustomer WHERE PROC-HANDLE = j#.
IF i# = 9 THEN DO:
MESSAGE 1 VIEW-AS ALERT-BOX.
UNDO.
END.
END.
END.
FACT(s) (Environment):
Connection management is enabled
-Dsrv PRGRS_SP_CACHE_CONN,n is set with n > 0
All Supported Operating Systems
OpenEdge 10.x
MS SQL DataServer
CAUSE:
This is an expected behavior.
Running a strored procedure with connection management enabled changes the timing of changes being commited to the data source. When a sorted procedure is run in a separate connection, changes not explicitely commited during the execution of the stored procedure are commited at the time the procedure handle is closed and the connection recycled.
FIX:
Disable connection management by removing the -Dsrv PRGRS_SP_CACHE_CONN,n switch or by setting n to 0