PowerBuilder – Database transactions using PFC n_tr

Posted on Thursday, August 19th, 2010 at 8:19 pm in

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:

ASE, SYC and SYJ Sybase Adaptive Server Enterprise
DIR Sybase DirectConnect
I10 Informix
IN9 Informix
ODBC (if driver and back-end DBMS support this feature)
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.

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

After datawindow updates or imbedded sql calls.

// update/insert/delete check
IF sqlca.sqlcode < 0 THEN
    ls_msg = sqlca.sqlerrtext
    messagebox('Database Error','Error inserting into table -> partMaster. ~n~r' + ls_msg)
    //return logic here

And finally committing the changes to the database.

// commit
IF sqlca.of_commit( ) < 0 THEN
    ls_msg = sqlca.sqlerrtext
    messagebox('Database Error','Error saving changes ~n~r' + ls_msg)
    //returns here
End IF
You might also be interested in