Transaction Management Extension for SQLAnywhere

What

• n_tr will replace your n_tr
• n_tr_SQLAnywhere is your new SQLAnywhere extension, inherited from n_tr
• u_dw and f_begin_end_tran_code are only here for you to copy code from

Why

This extension will make your database run smoothly, and your customers will be happier because of it. Not only will this prevent downtime for your database, but it will also ensure that your data will have more integrity.

The whole concept of client/server means sharing data among several users, making sure that everyone can share at the same time, and making sure that everyone plays well together!

And that is the purpose of transaction management... to make sure that everyone is sharing a common resource nicely. Contention and Consistency are the two key things we are interested in...

The communication between the Client and the Server is the most underestimated and misunderstood of all programming tasks in a multi-user Client Server environment. Handling the database traffic in a manner which maximizes concurrency and ensures consistency is critical to the success of any project. It is substantially more important than anything else a developer does inside PowerBuilder. Consistency: The ability to run all day without corrupting the database, losing work, or retrieving misleading data. This is a good thing. Concurrency: The ability for multiple users to use the same database at the same time and still get fast responses. This is also a good thing. by Frank Postle and Breck Carter, http://www.bcarter.com/syb1_d.htm

Also with this new extension, you get enhanced database error messaging and thorough Debug Spy messaging. The enhanced messages contain a lot of extra information about the error. This will help you, as well as your customers, to investigate and understand why a database error has occurred. You will know exactly what was happening so you can pinpoint the problem!

How

• Leave AUTOCOMMIT=TRUE at all times, except for the brief time when a transaction is in process in order to:

Prevent your database log from filling up (which then makes the DBA have to reboot the server)
Prevent contention among different users (preventing locks on your database which lead to deadlocks)
Prevent your users from losing their work because of database problems
Prevent you from having to code explicit COMMITs/ROLLBACKs everywhere after every retrieve, etc.

• Begin and End a transaction for your window's updates in order to:

Make sure that a set of related updates either all happen, or none happen
This in turn, will prevent your tables from getting out of sync
Keep your data valid, logical and reliable

• Last but not least, Never allow a modal window to open during a transaction in order to:

Not hold the database in suspense
Not hold up other users' updates
Not cause the database log file to fill up
Basically, Prevent your database from locking up

Scope

• Specifically for SQLAnywhere - Other databases have different needs and lend themselves to different approaches
• With the assumption that only one transaction object is needed - And that transaction object is hardcoded as SQLCA
• With the assumption that the transaction does not need to span multiple windows

When and Where

1. Begin the transaction (by setting AutoCommit to FALSE)
2. Do the Updates
3. End the transaction (by Committing or Rolling Back), then setting AutoCommit back to TRUE
4. Finally display any error message

Installation - Step by Step

1. Copy n_tr and n_tr_SQLAnywhere to your work PBL

2. Regenerate n_tr, then n_tr_SQLAnywhere, then your application object (since n_tr has changed)

3. In your application object, change the Variable Type for SQLCA from n_tr to n_tr_SQLAnywhere:

4. Copy the code in u_dw's DBError event into your u_dw's DBError event. Then specify to Override the ancestor script. Don't forget to do that.

Note: It is recommended to simply copy this event into your u_dw so that anything that you may have enhanced in your u_dw is not inadvertently wiped out.

////////////////////////////////////////////////////////////////////////////// // // NOTE: Ancestor is overridden!! // // NOTE: This script is overridden in order to handle update errors during // a transaction... // // During a transaction, the messagebox is not displayed here. // Instead the database error is captured so that it may be displayed // later after a COMMIT or ROLLBACK (and the transaction then ends). // // NOTE: This script is also overridden in order to provide the additional // information even when there is no transaction in progress. // ////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////// // // Event: dberror // // Description: // Display (or store) message that a database error has occurred // ////////////////////////////////////////////////////////////////////////////// // // Revision History // // Version // 5.0 Initial version // ////////////////////////////////////////////////////////////////////////////// string ls_message, ls_window_work = 'Unknown', ls_buffer_work = 'Unknown' string ls_msgparm[1] window lw_window_work // Prepare window work info this.of_GetParentWindow( lw_window_work ) if IsValid( lw_window_work ) then ls_window_work = & "in window titled ~r~n'" + lw_window_work.Title + "'~r~n" + & "(WindowObject is " + lw_window_work.ClassName() + ")" end if // Prepare buffer work info if buffer = Primary! then ls_buffer_work = "Primary (main one you see)" elseif buffer = Filter! then ls_buffer_work = "Filter (select menu option View/Filter to see it)" elseif buffer = Delete! then ls_buffer_work = "Delete (some row you were trying to delete)" end if ls_message = "A database error has occurred." + "~r~n~r~n" + & "DATABASE ERROR CODE: " + string(sqldbcode) + "~r~n~r~n" + & "DATABASE ERROR MESSAGE:~r~n" + sqlerrtext + "~r~n~r~n" + & "TRANSACTION OBJECT NAME:~r~n" + SQLCA.of_GetName() + "~r~n~r~n" + & "TRANSACTION IN PROGRESS:~r~n" + string(SQLCA.of_GetTransInProgress())+ "~r~n~r~n" + & "WHERE ERROR OCCURRED: " + "~r~n" + & "dberror event of " + "~r~n" + & "datawindow " + this.ClassName() + "~r~n" + & "(DataObject is " + this.DataObject + ")~r~n" + & "on row # " + string(row) + "~r~n" + & "in this buffer - " + ls_buffer_work + "~r~n" + & "which is on " + parent.ClassName() + "~r~n" + & + ls_window_work // Capture the error message (may or may not be displayed below based on if trans is in progress) SQLCA.of_SetError( ls_message ) if NOT SQLCA.of_GetTransInProgress() then //Transaction is *not* in Progress (safe to display error message now) SQLCA.of_DisplayError( "(same as above)" ) end if return 1 //Stop PowerBuilder from displaying its default message

 

5. Copy the code in f_begin_end_tran_code into each of your windows which are updateable. There is code for the pfc_BeginTran event as well as corresponding code for the pfc_EndTran event. Make sure you add them both - This is very important!

Note: It is recommended to put this code in the lowest descendant level so that the ClassName( ) function will report the window object's name. But if you would rather sacrifice that information in order to not have to put it in every window, then you could opt to put it in an ancestor. You will still have the title information. If you put it in an ancestor, you will probably encompass non-updateable windows too - But theoretically, those should never run through the save process... So choose which way works best for you.

Note: It is recommended to put this code into every updateable window, even if there is only one table updated. That is because otherwise if some rows update, but one fails, then the other rows need to be rolled back. This allows the user to correct the error and the try to save again... without getting the -3 "Row changed between retrieve and update" message.

/* //The following code should be copied into the pfc_begintran event for updateable windows: //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> // Begin a transaction if SQLCA.of_begin( ) <> 0 then SQLCA.of_DisplayError( "pfc_begintran event after" + "~r~n" + & "of_begin in" + "~r~n" + & "window titled ~r~n'" + this.Title + "'~r~n" + & "(WindowObject is " + this.ClassName() + ")" ) Return -1 //error end if Return 1 //success //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< //The following code should also be copied into the pfc_endtran event for updateable windows: //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> // End the transaction if SQLCA.of_end( ai_update_results ) <> 0 then SQLCA.of_DisplayError( "pfc_endtran event after" + "~r~n" + & "of_end in" + "~r~n" + & "window titled ~r~n'" + this.Title + "'~r~n" + & "(WindowObject is " + this.ClassName() + ")" ) Return -1 //error end if Return 1 //success //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< */

 

6. You may also wish to enhance your error message in your Application Manager's pfc_logon event to take advantage of the new and improved error messaging...

SQLCA.of_SetUser (as_userid, as_password) if SQLCA.of_Connect() >= 0 then return 1 else SQLCA.of_CaptureError( "pfc_logon event after" + "~r~n" + & "connect" + "~r~n" + & "(Object is " + this.ClassName() + ")" ) SQLCA.of_DisplayError( "(same as above)" ) return -1 end if

 

7. Test everything thoroughly and carefully. Use the Debug Spy Service to see what is going on. And when you are satisfied, then update your master source with the new n_tr, the new n_tr_SQLAnywhere, your modified application object, your modified u_dw, your modified updateable windows, and your modified application manager (if applicable).
   .

Congratulations! You are done!

Notes

1. This version can be used with any 5.0.0# release. (Just make sure that you regenerate all objects for newer releases since 5.0.00).

2. In 5.0.02, the PFC code was changed in pfc_w_master's pfc_Save event and in u_dw's DBError event to prevent any error response window from holding up a transaction. This extension does the same thing, only in a slightly different way. Just so that you know the difference...

This Extension	PFC 5.0.02
Error message is stored in the n_tr transaction object (as an instance variable there) (stored by u_dw's DBError event) Error message is populated with a lot of information, and a lot of spies are recorded	Error message is stored in the pfc_w_master window (as an instance variable there) (stored by u_dw's DBError event) Error message only contains basic information
Displays the error message in the window's pfc_EndTran event, after transaction has ended [after of_end( ) function call, based on return code from it]	Displays the error message in the window's pfc_DBError event. This event will execute after the pfc_EndTran event executes, if there was an error during the pfc_Update event and the window's error message instance variable is not an empty string. Since this extension does not populate the window's instance variable, it will always be an empty string in this extension and the pfc_DBError event will simply not execute.

Both store the error message, to be saved and then displayed after the transaction has been ended with a COMMIT or ROLLBACK. But this extension will also show much more detail about the error.

3. There was a bug in 5.0.00 that was corrected in 5.0.01 in pfc_n_tr's of_IsConnected( ) function. Within that function, the return codes from the DBHandle( ) function which are negative values should be considered as good return codes that mean that you are connected to the database. The version of n_tr that comes with this extension has the bug fix in it.

4. This extension also incorporates the setting of the User ID and password in DbParm's ConnectString (DbParm=ConnectString='DSN=PFC;UID=dba;PWD=sql'), as required by SQLAnywhere. (This code was copied from the PFC 5.0.02 change of the pfc_n_tr's of_SetUser( ) function.) In addition, options are set within DbParm's ConnectOption (ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT') in order to suppress the default logon window that the database engine would otherwise popup on a connect failure. These changes are included in n_tr_SQLAnywhere's of_SetUser( ) function.

5. Much to my dismay, I was unable to do one aspect of this extension the way that I wished. (This is what has taken me so long to turn in this extension.) I was still hoping to find a way to use...

ll_rc = this.of_Execute( "BEGIN TRANSACTION" )

...and leave AutoCommit = TRUE all of the time. However, SQLAnywhere run via ODBC has a problem with nested transactions. SQLAnywhere itself can nest transactions, and it even works and gives good return codes, always! However the problem comes into play with the ODBC layer, not SQLAnywhere itself... So when you are running SQLAnywhere, through ODBC, it does not work. Here's what happens. When PowerBuilder's AutoCommit = TRUE, ODBC issues a COMMIT to the database, no matter what - whether a nested transaction has been started or not. So SQLAnywhere nested transactions will only work if you are connected directly to SQLAnywhere (not going through ODBC), using ISQL for instance. And that doesn't do us any good.

So I had to settle for flipping AutoCommit = FALSE to begin a transaction, and then flip AutoCommit = TRUE when the transaction ends.

I have reported this to Powersoft (WebExpress Issue # 459861), and they are working on a solution. I believe that they need a parameter for ODBC to tell it not to always COMMIT, without having to resort to setting PowerBuilder's AutoCommit = FALSE.

If/when they come up with a way to allow us to nest SQLAnywhere transactions using ODBC, it will only require a few simple changes to switch over to this superior way:

1. Make sure that the new ODBC parameter is set in n_tr_SQLAnywhere's of_SetUser( ) function.
2. Change n_tr_SQLAnywhere's of_Begin( ) function to call ll_rc = this.of_Execute( "BEGIN TRANSACTION" ) instead of setting AutoCommit = FALSE. (Adding in passing of return code.)
3. Remove the no-longer-needed AutoCommit = TRUE from n_tr_SQLAnywhere's of_End( ) function.
4. In n_tr_SQLAnywhere's of_End( ) function, change each of_Commit() to of_Execute( "COMMIT TRANSACTION" ). And change each of_Rollback() to of_Execute( "ROLLBACK TRANSACTION" ).

6. Looking into the future... There are some major enhancements to the Save Process of the PFC 6.0 Release. The changes were designed with care to provide total upwards compatibility. But there are several features which that you may wish to take advantage of. See http://www.pfccheatsheet.com/pfcwm60.htm for details. Read about the new "Logical Unit of Work" (LUW) Service and the new architecture of "Self-Updating Objects" (SUO) there. These new services allow you to remove your transaction updates from being tied to a window.

Further Reading on Transaction Management

1. Connecting to Your Database, pp. 395-403, (PB 5.0 hardcopy books/Online Reference doc on CD, too)
2. Sybase SQL Anywhere User's Guide, pp. 211-230, 428-430, (PB 5.0 hardcopy books/Online Reference doc on CD, too)
3. PowerBuilder 5 Unleashed, Second Edition, SAMS Publ, pp. 138-139, http://www.pfccheatsheet.com/Trans98a.jpg , http://www.pfccheatsheet.com/Trans98b.jpg
4. PowerBuilder 5.0, Secrets of the PowerBuilder Masters, SYS-CON Publications, pp. 361- 378
5. "Transaction Management With Sybase - Taking Control", November 1994 issue of PowerBuilder Developer's
   Journal, by Frank Postle and Breck Carter, http://www.bcarter.com/syb1_d.htm

Your Feedback is Valued and Appreciated

If you have any suggestions, questions or problems, please feel free to drop me a line at mailto:"Sharon Weinstrom Buntz" <sbuntz@texas.net>. Also, if you make me aware that you will be using this extension, then I will keep you abreast of any news or future developments. Looking forward to hearing from you.

Thank you! ~Sharon

Interested in contributing material to this web site? Send an email to submit@pfcguide.com, but don't forget to check the contribution guidelines first.

For information or suggestions about this web site please contact webmaster@pfcguide.com

PFCGuide is sponsored by Dynamic Technology Group.
The information on this site is provided for advice only and not to be considered a contract or a liability against Dynamic Technology Group.

Last Revised: February 15, 2004 03:58 AM.