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 IF
You might also be interested in
