Oracle transaction rollback

General discussion forum about all databases other than SqlBase.
ozmenbetul
Turkey
Posts: 284
Joined: 28 Aug 2019, 08:57
Location: Turkey

Oracle transaction rollback

Post by ozmenbetul » 14 Jan 2013, 08:03

Hi all,
I use TD 61. SP2 against Oracle 11g database.

This is how I manage a transaction :

Set SqlDatabase = "DEMO"
Set SqlUser = "DEMO"
Set SqlPassword = "DEMO"
Call SqlConnect( hSqlTransaction )
!
Call SqlSetParameter( hSqlTransaction, DBP_AUTOCOMMIT, FALSE, "" )
!
Call SqlCommit( hSqlTransaction )
!
Set sSql = "UPDATE TABLE1 SET
COLUMN1 = :nColumn1Value
WHERE ID = :nID1"
Call SqlPrepareAndExecute( hSqlTransaction, sSql )
!
Set sSql = "UPDATE TABLE2 SET
COLUMN2 = :nColumn2ValueThatWillResultInAnSqlError
WHERE ID = :nID2"
Call SqlPrepareAndExecute( hSqlTransaction, sSql )
!
Set sSql = "UPDATE TABLE3 SET
COLUMN3 = :nColumn3Value
WHERE ID = :nID3"
Call SqlPrepareAndExecute( hSqlTransaction, sSql )
!
Call SqlCommit( hSqlTransaction )
*********************************************************************
If the second sql statement causes an sql error, the first update statement should also rollback.
Formerly, with TD 2.0 and Oracle 8i,
this was how I managed a transaction and it worked.
This doesn't work with TD 6.1 and Oracle 11g.

So what do you suggest for handling transactions?

Thanks for any help,
Betul

Jeff Luther

Re: Oracle transaction rollback

Post by Jeff Luther » 15 Jan 2013, 00:24

If the second sql statement causes an sql error, the first update statement should also rollback.
From what I read of your code, this is the problem with "Call SqlPrepareAndExecute" that I have mentioned before.

I really recommend that you catch the Boolean T/F from calls like this, Betul. As I read the code, even if the first update causes a SQL error (and the function returns FALSE!) that "Call..." throws away that FALSE return and continue to run update #2.

In fact, as the code read any/all of those 3 calls failing will be ignored and whatever changes/DML do get through will be committed.
So what do you suggest for handling transactions?

Code: Select all

Set sSql = "UPDATE TABLE1 SET
COLUMN1 = :nColumn1Value
WHERE ID = :nID1"
If NOT SqlPrepareAndExecute( hSqlTransaction, sSql )
        Call SqlPrepareAndExecute( hSqlTransaction, "ROLLBACK" )  ! I assume if any of 3 fail, rollback entire transaction
        Return FALSE
etc...
If all 3 of those UPDATEs must succeed before your transaction is successful, then your code needs to cleanly get out of the transaction (and do a ROLLBACK) if any fails. You cannot control that with "Call...". Correct way is "If NOT...". This is how I do it.

I'm not sure why it worked in old CTD v2.0, but using "Call..." is still not correct coding. My 2 cents opinion, anyway.

---------------------------------
In fact, if I may point out, this is not good coding either:
Call SqlConnect( hSqlTransaction )

What happens if the connect fails? Your user is then going to see 4 more SQL Errors -- 3 UPDATEs + the COMMIT statement if s/he does not Quit from TD's default SQL Error dialog box.

ozmenbetul
Turkey
Posts: 284
Joined: 28 Aug 2019, 08:57
Location: Turkey

Re: Oracle transaction rollback

Post by ozmenbetul » 15 Jan 2013, 09:22

Thank you, Jeff. This helped a lot.

I had to make an addition : In order not to handle the sql error at the application actions level with SAM_SqlError,
I had to add a When SqlError statement before each sql statement.
What happens if you don't put a When SqlError there? Well the answer is, if you don't, the standard SqlWindows-Sql Error dialog box opens. If you click "Halt" instead of "Continue" there, then the transaction is committed, even if you have those rollback statements. Or if you have your own SAM_SqlError code at the application actions level, and do a SalQuit() there as a result of a "halt" option, the transaction is committed.


Now the new code is :
-----------------------------------------------------------------------------------------------------------------------------------
Set SqlDatabase = "DEMO"
Set SqlUser = "DEMO"
Set SqlPassword = "DEMO"
If NOT SqlConnect( hSqlTransaction )
Return FALSE
!
Call SqlSetParameter( hSqlTransaction, DBP_AUTOCOMMIT, FALSE, "" )
!
Call SqlCommit( hSqlTransaction )
!
When SqlError
Return FALSE
Set sSql = "UPDATE TABLE1 SET
COLUMN1 = :nColumn1Value
WHERE ID = :nID1"
If NOT SqlPrepareAndExecute( hSqlTransaction, sSql )
Call SqlPrepareAndExecute( hSqlTransaction, "ROLLBACK" )
Return FALSE
!
When SqlError
Return FALSE
Set sSql = "UPDATE TABLE2 SET
COLUMN2 = :nColumn2Value
WHERE IDt = :nID2"
If NOT SqlPrepareAndExecute( hSqlTransaction, sSql )
Call SqlPrepareAndExecute( hSqlTransaction, "ROLLBACK" )
Return FALSE
!
When SqlError
Return FALSE
Set sSql = "UPDATE TABLE3 SET
COLUMN3 = :nColumn3Value
WHERE ID = :nID3"
If NOT SqlPrepareAndExecute( hSqlTransaction, sSql )
Call SqlPrepareAndExecute( hSqlTransaction, "ROLLBACK" )
Return FALSE
!
Call SqlCommit( hSqlTransaction )
-----------------------------------------------------------------------------------------------------------------------------------

One more point : Instead of
-----------------------------------------------------------------------------
When SqlError
Return FALSE
Set sSql = "UPDATE TABLE1 SET
COLUMN1 = :nColumn1Value
WHERE ID = :nID1"
If NOT SqlPrepareAndExecute( hSqlTransaction, sSql )
Call SqlPrepareAndExecute( hSqlTransaction, "ROLLBACK" )
Return FALSE
-----------------------------------------------------------------------------

you can also code :
******************************************************************************
When SqlError
Call SqlPrepareAndExecute( hSqlTransaction, "ROLLBACK" )
Return FALSE
Set sSql = "UPDATE TABLE1 SET
COLUMN1 = :nColumn1Value
WHERE ID = :nID1"
Call SqlPrepareAndExecute( hSqlTransaction, sSql )
******************************************************************************

Best Regards,
Betul

Jeff Luther

Re: Oracle transaction rollback

Post by Jeff Luther » 15 Jan 2013, 23:35

What happens if you don't put a When SqlError there? Well the answer is, if you don't, the standard SqlWindows-Sql Error dialog box opens.
Yes... OK, a little more information time :)

TD manages and transmits a SQL Error msg. in 1 of 3 places:
1) LOCAL to the code: When SqlError. If you trap this locally and If your code returns TRUE/FALSE from this msg. then that is as far as error processing occurs and T/F is returned as the *return* to the Sql function that generated the error. (This is why "Call..." is wrong -- it 'throws away' or ignores that FALSE value.)
If, on the other hand, there's no When SqlError is coded locally, or there is a code path in the local When SqlError processing which has not Return <> statement, then TD continues to 2) ...

2) GLOBAL to the code: sends a SAM_SqlError to App. Actions section. Ditto, If your code returns TRUE/FALSE from this msg. then that is as far as error processing occurs and T/F is returned as the *return* for the Sql function that generated the error.
If, on the other hand, there's no global SAM_SqlError msg. trapped, or there is a code path in the global SAM_SqlError processing which has not Return <>, then TD continues to 3)...

3) TD DEFAULT SQL ERROR PROCESSING: this is what you report. The default Sql Error dlg. box is displayed.
Click "Continue" and TD returns FALSE to the Sql function that caused the error
Click "Halt" and TD terminates.
If you click "Halt" instead of "Continue" there, then the transaction is committed, even if you have those rollback statements
Based on what you write, I would have to see this to believe it. It sounds like TD is doing an implicit COMMIT internally, so that your later ROLLBACK call doesn't do anything because there's no transaction with any change(s) -- TD already did a COMMIT. I think this is wrong coding. Your appl. should never have an 'exit' point where it's unknown whether current transaction should have a commit or rollback because the Sql Error msg. 'fell through' to TD's default processing.

Technically, and in that proverbial 'perfect world,' your user should never see TD's default dlg. box. Seems to me that 2) above should be the final error-handling place in your code before the Sql function gets a Return FALSE.

maybe code like this:
1) above -- code local for specific non-fatal errors that might occur specific to the SQL code below the When. Like, non-unique value for INS/UPD SQL, etc. You catch and use wParam/lParam to get details about the error.

2) above -- if something less specific, more general, occurs let When code fall through to global SAM_SqlError processing and deal with any other errors there.

ozmenbetul
Turkey
Posts: 284
Joined: 28 Aug 2019, 08:57
Location: Turkey

Re: Oracle transaction rollback

Post by ozmenbetul » 16 Jan 2013, 07:37

Thank you, Jeff; very valuable discussion for me. I've known how TD managed Sql-Error messages; but I now realize that I haven't processed error messages detailed enough, up to now.

I'm not sure, but I think, it seems that, SalQuit() changed behaviour since TD 2.0.
It did not perform an implicit commit in TD 2.0, I guess. I guess, because I don't have TD 2.0 installed any more. This is what I remember.
Whether it did, or not; you're right, coding should be designed better to handle fatal database errors, and others seperately. So I'll review my code according to this.

My best,
Betul

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests