PowerBuilder – Database transactions using PFC n_tr
Here is a relatively painless way to handle transaction processing in your Powerbuilder application. This code assumes the use of the PFC but could be adapted without too much trouble.
This will work with the following database connections:
ADO.NET
ASE, SYC and SYJ Sybase Adaptive Server Enterprise
DIR Sybase DirectConnect
I10 Informix
IN9 Informix
JDB JDBC
ODBC (if driver and back-end DBMS support this feature)
OLE DB
SNC SQL Native Client for Microsoft SQL Server
The process involves the AutoCommit database preference. The Powerbuilder default is to set this to False which means that SQL statements are issued inside a transaction. Powerbuilder issues a BEGIN TRANSACTION statement at the start of a connection and issues another BEGIN TRANSACTION statement after each COMMIT or ROLLBACK statement. In our case the AutoCommit is set to True so we control the transactions.
In the n_tr object (or whatever your pfc based transaction object is called) make the following changes:
public function long of_begin ();
/* Issues a begin transaction by changing the autocommit flag from true to false. The assumption is that the initial connection is made with the autocommit flag set to true. Changing autocommit from true to false forces the start of a transaction. */ long ll_rc = -10 this.SQLCode = 0 this.SQLErrtext = '' if of_IsConnected() then this.autoCommit = false ll_rc = this.SQLCode end if return ll_rc
public function long of_end
/* The initial connection is made with autocommit = true. The of_start() method changes autocommit from true to false, which begins the transaction. Calling of_end() changes the autocommit from false back to true, which commits the transaction if it had not previously been rolled back by a call to of_rollback(). This method is called by both of_commit() and of_rollback and need not be called directly. */ long ll_rc = -10 if of_IsConnected() then this.autoCommit = true ll_rc = this.SQLCode end if return ll_rc
public function long of_commit
/* Copied method from ancestor and changed "commit using this" to a call to of_end(). The initial connection is made with autocommit set to true. Calling of_begin() changes autocommit to false, which issues a begin transaction statement. When of_commit() is called, the transaction is commited by of_end() by chaning autocommit back to true. */ long ll_rc = -10 string ls_name if of_IsConnected() then // If SQLSpy service is on, add to the history if IsValid (gnv_app) then if IsValid (gnv_app.inv_debug) then if IsValid (gnv_app.inv_debug.inv_sqlspy) then ls_name = this.is_Name if Len (ls_name) = 0 then ls_name = this.ClassName() end if gnv_app.inv_debug.inv_sqlspy.of_SQLSyntax ("Commit using " + ls_name ) end if end if end if // commit using this; // ll_rc = this.SQLCode ll_rc = this.of_end() end if return ll_rc
public function long of_rollback
/* Rollback the transaction the pfc way, then call of_end() to reset the autocommit flag back to true. (Note: This forces an un-needed begin tran and commit.) */ long ll_return // call ancestor method ll_return = super::of_rollback() // End the transaction. this.of_end() return ll_return
Within the application code you then use the following code when you want to save data to the database. Naturally you need to use your transaction name if you created one other than the global transaction object SQLCA
Prior to update(s) being issued.
//begin transaction IF (sqlca.of_begin( ) < 0) THEN messagebox('Database Error','Error starting transaction ~n~r' + sqlca.sqlerrtext) // return logic here END IF
After datawindow updates or imbedded sql calls.
// update/insert/delete check IF sqlca.sqlcode < 0 THEN ls_msg = sqlca.sqlerrtext sqlca.of_rollback() messagebox('Database Error','Error inserting into table -> partMaster. ~n~r' + ls_msg) //return logic here END IF
And finally committing the changes to the database.
// commit IF sqlca.of_commit( ) < 0 THEN ls_msg = sqlca.sqlerrtext sqlca.of_rollback() messagebox('Database Error','Error saving changes ~n~r' + ls_msg) //returns here End IFYou might also be interested in