PowerScript Reference
PowerScript Functions

Description

Indicates the merge mode starts. The database operations after the StartMerge label will not be executed until the EndMerge label is called. Using the merge mode, multiple database requests are consolidated into one request, so as to reduce the number of server calls. Make sure to read the Usage section below in order to use these label functions properly.

This function takes effect only when the application is deployed via the PowerServer project (it will be ignored when the application is deployed via the PowerClient project or the Application project).

Applies to

PowerServerLabel object

Syntax

objectname.StartMerge ( { integer stopmode } )

Return value

Integer.

Returns 1 if it succeeds and -1 if an error occurs.

Usage

The StartMerge label must be used together with the EndMerge label, which means, StartMerge and EndMerge must be used in pairs. The StartMerge/EndMerge pair (called merge labels) should not contain any other merge labels.

The merge labels enable requests to be completed in a much shorter period of time (compared to the unmerged mode).

The merge labels should only be used to merge the following operations:

For merge labels to work with the SQL Server database, make sure the "Multiple Active Result Sets" setting (in the Advanced dialog) is set to True (the default is False).

The merge labels cannot work with the Crosstab DataWindow.

The merge labels cannot work with the DataWindow containing nested reports.

The merge labels cannot work with the DataWindow that modifies the SQL statement in the RetrieveStart or UpdateStart event. For example, the following statement cannot be included in the merge label.

//RetrieveStart
this.Modify("DataWindow.Table.Select='select...'")

The merge labels should only be used to merge independent requests. Independent requests means the execution of one request does not rely on the execution result of another request in the same merged request, or one request does not use the return value of another request in the same merged request.

For example,

Incorrect usage (of using the execution result of another request):

nvoPowerServer.StartMerge()
dw_1.Retrieve()
dw_2.Retrieve()
if dw_1.rowcount() > 0 then //dw_1.Retrieve() is not executed until EndMerge is called
 …
end if
nvoPowerServer.EndMerge()

Correct usage:

nvoPowerServer.StartMerge()
dw_1.Retrieve()
dw_2.Retrieve()
nvoPowerServer.EndMerge()
if dw_1.rowcount() > 0 then 
 …
end if

Incorrect usage (of using the return value of another request):

The original code:

functionA():
dw_1.retrieve()
return dw_1.rowcount()

functionB():
dw_2.retrieve()

Incorrect usage:

nvoPowerServer.StartMerge ()
ll_id = functionA() //ll_id is assigned with an incorrect value
functionB() 
nvoPowerServer.EndMerge ()
if ll_id > 0 then
 …
end if

Correct usage:

nvoPowerServer.StartMerge ()
dw_1.retrieve()
functionB() 
nvoPowerServer.EndMerge ()
if dw_1.rowcount() > 0 then
 …
end if

The merge labels cannot work with the Retrieve statements in the same DataWindow or DataStore. For example,

//this is an incorrect example
nvoPowerServer.StartMerge ()
dw_1.retrieve()
dw_1.retrieve()
nvoPowerServer.EndMerge ()

The merge labels cannot work with the Fetch Cursor statement, but you can place the Open Cursor statement in the merge label. For example,

//this is a correct example
DECLARE lcur_cursor1 CURSOR FOR  
 SELECT column1 FROM table1 ;
DECLARE lcur_cursor2 CURSOR FOR  
 SELECT column2 FROM table2 ;

lpl_label.startmerge( )
open lcur_cursor1;
open lcur_cursor2;
lpl_label.endmerge( )

if lpl_label.results[1].sqlcode = 0 then
 fetch lcur_cursor1 into :ls_column1[ll_column1_num];
 ll_column1_num ++
 do while sqlca.sqlcode = 0 
  fetch lcur_cursor1 into :ls_column1[ll_column1_num];
  ll_column1_num ++
 loop
end if

if lpl_label.results[2].sqlcode = 0 then
 fetch lcur_cursor2 into :ls_column1[ll_column2_num];
 ll_column2_num ++
 do while sqlca.sqlcode = 0 
  fetch lcur_cursor2 into :ls_column1[ll_column2_num];
  ll_column2_num ++
 loop
end if

The merge labels should not include the GOTO statement, because GOTO statement may cause the EndMerge label not executed. Besides that GOTO statement does not support "try catch" for catching the exception.

The merge labels should not include the Connect, Disconnect, Commit, and Rollback statements. For example,

Incorrect usage:

lpl_label.startmerge( )
delete from table1 where column1 = 1;
delete from table2 where column2 = 1;
commit;
lpl_label.endmerge( )

Correct usage:

lpl_label.startmerge( )
delete from table1 where column1 = 1;
delete from table2 where column2 = 1;
lpl_label.endmerge( )
commit;

It is recommended to test SQLCode in the returned results array and ensure all server calls succeeded before sending explicit COMMIT. For example,

lnv_PowerServerLabel.StartMerge(1) 
  dw_1.Update()
  dw_2.Update()
lnv_PowerServerLabel.EndMerge()
If lnv_PowerServerLabel.Results[1].SQLCode = 0 And lnv_PowerServerLabel.Results[2].SQLCode = 0 Then
  COMMIT;
Else
  ROLLBACK;
End If

If the merge labels include the Destroy statement, make sure the other requests within the merge labels do not work with the destroyed object, otherwise, EndMerge label may throw the null object error.

The requests in the DataWindow event cannot be merged if the event is already placed within the merge labels. For example,

The Retrieve statements in the RowFocusChanged event cannot be merged.

//following is pseudocode
nvoPowerServer.StartMerge ()
RowFocusChanged_event 
...
nvoPowerServer.EndMerge ()

You should move the event out of the merge labels, and then place the labels into the event to merge the requests in the event.

//RowFocusChanged_event
nvoPowerServer.StartMerge ()
Retrieve1
Retrieve2
...
nvoPowerServer.EndMerge ()

The merge labels cannot work with DDDW.Reteive triggered by InsertRow and ShareData. You can modify the scripts: 1) Disable auto-retrieve for the DDDW column; 2) Get data from the DDDW column to DataWindowChild via GetChild and set the transaction object; 3) Place the DataWindowChild retrieve statements to the merge labels. For example:

Datawindowchild ldwc_1,ldwc_2

dw_1.GetChild( "col1", ldwc_1 )
ldwc_1.SetTransObject(SQLCA)

dw_1.GetChild( "col2", ldwc_2 )
ldwc_2.SetTransObject(SQLCA)

gnv_PowerServerLabel.StartMerge()
ldwc_1.Retrieve()
ldwc_2.Retrieve()
gnv_PowerServerLabel.EndMerge()

If the DataWindow or embedded SQLs does not support the merge labels, the following error will occur.

PowerServerLabel Results[] property may return the null object error when executed in the traditional C/S application. For example,

gnv_Label.StartMerge()
open cur_lv_1 ;
open cur_lv_2 ;
open cur_lv_10 ;
open cur_lv_20 ;
gnv_Label.EndMerge()

If (Not isPowerServerapp()) Or (UpperBound(gnv_Label.results) > 0 and gnv_Label.results[1].SQLCode = 0 ) Then //object is null
                fetch cur_lv_1 into :ls_user_nm,:ls_user_no;
                do while sqlca.sqlcode = 0 
                                ll_index=tab_1.page1.lv_1.additem(ls_user_nm,2)
                                tab_1.page1.lv_1.getitem(ll_index,ltvi_view)
                                ltvi_view.data = ls_user_no
                                tab_1.page1.lv_1.setitem(ll_index,ltvi_view)
                                fetch cur_lv_1 into :ls_user_nm,:ls_user_no;
                loop
End If
close cur_lv_1;

You can work around this error by rewriting the code as below:

If isPowerServerapp() Then 
                If UpperBound(gnv_Label.results) > 0 and gnv_Label.results[1].SQLCode = 0  Then
                                fetch cur_lv_1 into :ls_user_nm,:ls_user_no;
                                do while sqlca.sqlcode = 0 
                                                ll_index=tab_1.page1.lv_1.additem(ls_user_nm,3)
                                                tab_1.page1.lv_1.getitem(ll_index,ltvi_view)
                                                ltvi_view.data = ls_user_no
                                                tab_1.page1.lv_1.setitem(ll_index,ltvi_view)
                                                fetch cur_lv_1 into :ls_user_nm,:ls_user_no;
                                loop
                End If
Else
                fetch cur_lv_1 into :ls_user_nm,:ls_user_no;
                do while sqlca.sqlcode = 0 
                                ll_index=tab_1.page1.lv_1.additem(ls_user_nm,3)
                                tab_1.page1.lv_1.getitem(ll_index,ltvi_view)
                                ltvi_view.data = ls_user_no
                                tab_1.page1.lv_1.setitem(ll_index,ltvi_view)
                                fetch cur_lv_1 into :ls_user_nm,:ls_user_no;
                loop
End If

Examples

In this example, four requests are merged and sent in one call.

gnv_PowerServerLabel.StartMerge()
Open cur_dwstyle;
lds_1.Retrieve(ll_id)
dw_1.Retireve(ll_id)
SELECTBLOB qa_img INTO :lbb_temp FROM TableBlob WHERE id=3;
gnv_PowerServerLabel.EndMerge()

Here is another example:

PowerServerLabel gnv_PowerServerLabel
gnv_PowerServerLabel = create PowerServerLabel
String ls_sql

Declare cur_dwstyle Cursor FOR
SELECT Top 10 id, Fname, Lname, sex, costs
FROM t_dwstyle_table ;

gnv_PowerServerLabel.StartMerge()
lds_1.Retrieve(ll_id)
dw_1.Retireve(ll_id)
SELECT next_id INTO: ll_id_max FROM TableA;
UPDATE TableA SET next_id = isnull(next_id,0) + 1;
ls_sql = "UPDATE Qa_TableA set qa_varchar = 'appeon' WHERE id = 6"
Execute Immediate :ls_sql;
Open cur_dwstyle;
gnv_PowerServerLabel.EndMerge()

//gnv_PowerServerLabel.Results = 5
//gnv_PowerServerLabel.Results[1] => lds_1.Retrieve(ll_id)
//gnv_PowerServerLabel.Results[2] => dw_1.Retireve(ll_id)
//gnv_PowerServerLabel.Results[3] => SELECT next_id INTO: ll_id_max FROM TableA;
//gnv_PowerServerLabel.Results[4] => UPDATE TableA SET next_id = isnull(next_id,0) + 1;
//gnv_PowerServerLabel.Results[5] => Execute Immediate :ls_sql;
//gnv_PowerServerLabel.Results[6] => Open cur_dwstyle;

If gnv_PowerServerLabel.Results[4].SQLCode = 0 and gnv_PowerServerLabel.Results[5].SQLCode = 0 Then
 COMMIT;
Else
 ROLLBACK;
End If

If gnv_PowerServerLabel.Results[6].SQLCode = 0 Then
 FETCH cur_dwstyle INTO :li_id, :ls_Fname, :ls_Lname, :ls_Sex, :ldb_costs;

 DO WHILE SQLCA.sqlcode = 0
  // Fetch the next row from the result set.
  FETCH cur_dwstyle INTO :li_id, :ls_Fname, :ls_Lname, :ls_Sex, :ldb_costs;
 LOOP
End If

Close cur_dwstyle;

If IsValid ( gnv_PowerServerLabel ) Then Destroy ( gnv_PowerServerLabel )

See also

EndMerge