*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
|
|
Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
|
Breck Carter
Last modified: December 23, 1996
mail to: bcarter@bcarter.com
[Home]
How can I get control over the pipeline object at runtime? I want to transfer several tables as part of one transaction and either commit or rollback all the changes.
The pipeline object was the most popular new feature of PowerBuilder 4 before PowerBuilder 4 was released. When folks actually tried to use it in production applications, however, its shortcomings were plain for all to see. Most of the problems had a single cause: The pipeline just didn't have enough methods and properties to give applications control over its runtime behaviour. It made a handy ad-hoc tool for programmers and power-users but it just wasn't ready for mission critical development.
The good news is that PowerBuilder applications were transferring data between different databases long before the pipeline object was invented, and long before Sybase brought us Replication Server or publish-subscribe in SQL Anywhere. The technique involves the ubiquitous DataWindow and, for a straight table copy, the following steps:
The delete-and-insert-all-rows technique is handy when all you want to do is "ram a table into the target database." Other more subtle approaches are possible where different rows are updated, inserted and deleted by making different calls to SetItemStatus(). However, the point here is that the DataWindow gives you more control than the pipeline over the way a transfer is done.
Figure 1 shows a window that implements the delete-and-insert-all-rows technique. The drop-down list box lets you pick which table is going to be transferred, and the two buttons retrieve from database 1 and update database 2.
Figure 1: "Transfer A Whole Table" Window
Figure 2 shows how the program connects to the two databases, and how it uses Wait_For_Commit to reduce the frequency of "Catch 22" referential integrity problems when deleting and re-inserting rows in parent tables.
Figure 2: Application Open Event
// Connect to source database.
gtr_source = create transaction;
gtr_source.DBMS = "ODB"
gtr_source.DBParm = "DisableBind=1,ConnectString='DSN=UrlOld;UID=DBA;PWD=SQL'"
connect using gtr_source;
if gtr_source.SQLCode <> 0 then
MessageBox ( "Error", "Source connect failed" )
halt
end if
// Connect to target database.
gtr_target = create transaction;
gtr_target.DBMS = "ODB"
gtr_target.DBParm = "DisableBind=1,ConnectString='DSN=UrlIndex;UID=DBA;PWD=SQL'"
connect using gtr_target;
if gtr_target.SQLCode <> 0 then
MessageBox ( "Error", "Target connect failed" )
halt close
end if
// Postpone referential integrity checking until commit point.
execute immediate "set temporary option wait_for_commit = on" using gtr_target;
if gtr_target.SQLCode <> 0 then
MessageBox ( "Error", "Target set option failed" )
halt close
end if
open ( w_transfer_table)
|
Figure 3 contains the code to connect the DataWindow to database 1 and retrieve all the rows.
Figure 3: "Get From Source" Clicked Event
integer li_RC
long ll_row_count
// Connect DataWindow to the source database.
li_RC = parent.dw_transfer.SetTransObject ( gtr_source )
if li_RC <> 1 then
MessageBox ( "Error", "SetTransObject dw_transfer failed" )
halt
end if
// Retrieve all rows from the source table.
ll_row_count = parent.dw_transfer.retrieve()
if ll_row_count < 0 then
MessageBox ( "Error", "Retrieve dw_transfer failed" )
halt
end if
|
Figure 4 contains most of the code to do the transfer:
Figure 4: "Send To Target" Clicked Event
integer li_RC
long ll_row
string ls_SQL
// Switch DataWindow to the target database.
li_RC = parent.dw_transfer.SetTransObject ( gtr_target )
if li_RC <> 1 then
MessageBox ( "Error", "SetTransObject dw_transfer failed" )
halt
end if
// Start a database transaction on the target.
execute immediate "begin transaction" using gtr_target;
if gtr_target.SQLCode <> 0 then
rollback using gtr_target;
MessageBox ( "Error", "Target begin transaction failed" )
halt close
end if
// Delete all rows from the target table.
// Note that referential integrity checking will be
// postponed until the commit.
ls_SQL = "delete from " + parent.ddlb_table_name.text
execute immediate :ls_SQL using gtr_target;
if gtr_target.SQLCode <> 0 then
rollback using gtr_target;
MessageBox ( "Error", "Delete detail_document failed" )
halt close
end if
// Mark all the rows in the DataWindow as "New - To Be Inserted".
for ll_row = 1 to parent.dw_transfer.RowCount()
li_RC = parent.dw_transfer.SetItemStatus &
( ll_row, &
0, &
primary!, &
NewModified! )
if li_RC <> 1 then
rollback using gtr_target;
MessageBox ( "Error", "SetItemStatus dw_transfer failed" )
halt close
end if
next
// Insert all the rows in the target table.
li_RC = parent.dw_transfer.update()
if li_RC <> 1 then
rollback using gtr_target;
MessageBox ( "Error", "Update dw_transfer failed" )
halt close
end if
// End the database transaction.
commit using gtr_target;
if gtr_target.SQLCode <> 0 then
rollback using gtr_target;
MessageBox ( "Error", "Target commit failed" )
halt close
end if
|
The code in Figures 5 and 6 is icing on the cake. Only one window and one DataWindow control are required for multiple tables as long as there is a separate DataWindow object provided for each table.
Figure 5: DropDownListBox SelectionChanged Event
// Change the DataWindow.DataObject. parent.dw_transfer.reset() parent.dw_transfer.DataObject = "d_" + this.text parent.title = "Transfer " + this.text + " Table" |
Figure 6: Window Open Event
// Force a value into the DataWindow.DataObject. this.ddlb_table_name.PostEvent ( SelectionChanged! ) |
The code as shown doesn't do much more than the pipeline does automatically and for much less effort. However, it's all in PowerScript instead of buried within the PowerBuilder runtime. You're free to make changes like transferring more than one table within the scope of one commit or rollback.