1. Sivaprakash BKR
  2. PowerBuilder
  3. Saturday, 19 January 2019

Hello,

Using PB 2017 R3, PostgreSQL 11

Need to rollback on database disconnect command.

Updating a sequence of dw within a database transactions.  If, in the middle of the update, some error erupts my application gets closed after disconnecting the database.   And this disconnection seems to commit the data, instead of rollback.   

This commit occurs only when there is an error in program, does not happen when database returns any error. My pseudo code is:

*********************************************

sqlca.autocommit(False)
ll_rc = dw_1.update(true, false)
If ll_rc = 1 Then
//   Some settings
      dw_2.SetItem(ll_row, 'field', ll_field)
      dw_2.SetItem(ll_row, 'field2', ls_field)
       ll_rc = dw_2.update(True, false)
End if

Sqlca.autocommit(True)

************************************************

If some typo errors in SetItem statement, system error event is called which disconnects the database and closes the application.   And dw_1. data gets updated.

Is there any settings that I could avoid this database update ?

Happiness Always
BKR Sivaprakash

 

 

Accepted Answer
Sivaprakash BKR Accepted Answer Pending Moderation
0
Votes
Undo

Thanks Roland Smith, Miguel Leeuwe, Olan Knight.

IN between Sqlca.Autocommit = False and sqlca.Autocommit = True, everything is handled including commit and rollbacks.  While trying to shorten the code, some important lines have been missed.  In real code, everything is handled perfectly.  

It rollbacks, in the event of some db errors, but not with some programming errors.  

**************************************************

Event : ue_saveprocess   // This event starts db transaction and transfer control to event ue_save which takes care of assigning and updating the dws.

SQLCA.of_set_autocommit(False)

ll_rc = Event ue_save(ls_msg)

If ll_rc = 1 Then
    ll_rc = SQLCA.of_commit()
    If ll_rc = -1 Then
        SQLCA.of_rollback()
        Return False
     End If
     Event ue_resetupdate()
Else
      SQLCA.of_rollback()
      If IsNull(ls_msg) or Trim(ls_msg) = '' Then ls_msg = 'Save Failed in Commit'
      io_message.MessageBox('Save Failed', ls_msg)
End If
SQLCA.of_set_autocommit(True)

********************************************

Event :  ue_save    // This event performs all required updates that's need to be done in transaction, including generation of unique numbers.   NOT shown here for simplicity

ll_rc = dw_header.Update(True, False)                                       // Main dw update
If ll_rc <> 1 or ib_save = False Then
     as_msg = 'Save Failed in Entry'
     Return ll_rc
End If

ll_rc = Event ue_save_account_header(ls_msg)                          // Few more updates
If ll_rc <> 1 or ib_save = False Then
     as_msg = ls_msg
     Return ll_rc
End If

ll_rc = Event ue_save_tab_dw(ls_msg)                                     // update of dw's in Tabs
If ll_rc <> 1 or ib_save = False Then
    as_msg = ls_msg
    Return ll_rc
End If

********************************************

SQLCA is a non-visual object [ code copied from STD framework ]

In the event of some system error from event ue_save, the control doesn't go back to event ue_saveprocess, instead calls the systemerror event, which is again a non-visual object [ from STD framework ].  

Solution:

As pointed out by Roland Smith, I just introduced a Rollback command in the non-visual object, before disconnecting the database.  That solved this issue.

 

Comment
Hi Sivaprakash;

FYI: the next release of both of the STD frameworks will have this feature as part of the master transaction object.

Regards ... Chris
  1. Chris Pollach
  2. Tuesday, 22 January 2019
Thanks Chris Pollach. STD frameworks evolves all the time.
  1. Sivaprakash BKR
  2. Tuesday, 22 January 2019
Thanks Sivaprakash - I try to do this 3-4 times per year!



Note: I have a big release for PB2017R3 coming out soon when PB2017R3 MR01 is released from Appeon. This will be the last R3 release of the frameworks. From there, I will be moving the Integrated framework to MR01. This will have dual support for the TE & TX controls that support the RTE Control and RT DW features of PB.
  1. Chris Pollach
  2. Tuesday, 22 January 2019
There are no comments made yet.
  1. Monday, 21 January 2019
  2. PowerBuilder
  3. # Permalink
Andreas Mykonios Accepted Answer Pending Moderation
0
Votes
Undo

Hi.

Powerbuilder has a database parameter called CommitOnDisconnect.

If you search it in help file you will see that it's default value is set to commit on disconnect. But it can be changed to do a rollback instead of a commit.

From the help file:

Default value

CommitOnDisconnect='Yes'

Usage

Set CommitOnDisconnect to No if you want PowerBuilder to roll back uncommitted database updates (instead of automatically committing them when you disconnect from the database).

Attention: this parameter must be set before connecting to the database. Cannot be changed while connected to db. Also, it will rollback whenever the connection close, not only if an error occurs. So it must be carefully used. Personally I had one case where I did needed to set that parameter to rollback on disconnection. Of course this rollback affects only uncommitted transactions (nested or not)... To change the behavior I did the following setting:

SQLCA.DBParm="CommitOnDisconnect='No'"

Andreas.

Comment
Thanks Andreas Mykonios.

My first search was for this param. Now I think it will be better to rollback only if some error occurs, and to commit on normal disconnect. And the rollback code in system error event does the same, which takes care of the warning you mention.
  1. Sivaprakash BKR
  2. Tuesday, 22 January 2019
The problem with the "CommitOnDisconnect" feature is:

1) Does not work across all DB clients

2) Does not work for PowerServer Web / Mobile Apps.

Handling this at the SQLCA level works all around.

Just my $0.02

  1. Chris Pollach
  2. Tuesday, 22 January 2019
There are no comments made yet.
  1. Monday, 21 January 2019
  2. PowerBuilder
  3. # 1
Olan Knight Accepted Answer Pending Moderation
0
Votes
Undo

In theory, setting AUTOCOMMIT = FALSE should handle the problem for you.

However, depending on which version of SqlAnywhere you are using, that's not the case. For example:
http://codeverge.com/sybase.powerbuilder.database/autocommit-false-problem-with-as/824570

The problem is as you have stated: once the application is no longer connected to the database, you have no control over how the database handles the last pending transaction.

Check with the DBA to see if there is a way to default ROLLBACKs on pending transactions rather than COMMITs.


Good Luck,

Olan

Comment
PowerBuilder will automatically perform a COMMIT when the DISCONNECT command is called.



As I mentioned earlier, there is no ROLLBACK command in the code snippet.
  1. Roland Smith
  2. Sunday, 20 January 2019
There are no comments made yet.
  1. Sunday, 20 January 2019
  2. PowerBuilder
  3. # 2
Miguel Leeuwe Accepted Answer Pending Moderation
0
Votes
Undo

that statement:

sqlca.autocommit(true) // or false

 

shouldn't that be:

 

sqlca.autocommit = true // or false

 

?

 

regards

 

 

Comment
There are no comments made yet.
  1. Sunday, 20 January 2019
  2. PowerBuilder
  3. # 3
Roland Smith Accepted Answer Pending Moderation
0
Votes
Undo

You need to have a ROLLBACK command, it isn't going to do that automatically. After the outer IF statement, try adding this:

If li_rc < 0 Then

   ROLLBACK;

End If

Comment
There are no comments made yet.
  1. Saturday, 19 January 2019
  2. PowerBuilder
  3. # 4
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.