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.

Tip 69: Eschewing Annoyance

(Yes, this really is "Tip Number 69" even though the previous one was called "Number 57". That's because over the past few months 11 tips were put in files that didn't follow the "tipxxx.htm" naming convention. Since the very first file was one of the misnamed, everything's been screwed up since Day One: Even tip001.htm is really Number 2. Maybe all the old files will get renamed someday, maybe they won't.
But starting today new ones are gonna be numbered correctly.)

How do I get rid of the "Specify Retrieval Arguments" dialog box for a DropDownDataWindow that uses a retrieval argument? The script has all the necessary code to retrieve the DDDW before the main DataWindow is retrieved. The Powersoft Faxline #2307 has the breathtaking title "Suppressing Retrieval Arguments on Datawindows Which Include Drop Down DataWindows While Performing An InsertRow Or Retrieve." In that document you can read all about calling GetChild() and Retrieve() ahead of time for DDDWs that use retrieval arguments. That logic usually gets rid of the dialog box shown in Figure 1, but sometimes not.

Figure 1: The Annoying Dialog Box

The "sometimes not" happens when the DDDW still contains no rows when the main DataWindow is retrieved. In other words, if the DDDW is empty, even if it's supposed to be empty, the annoying dialog box of Figure 1 will appear. This situation can be really confusing, especially if there are several DDDWs with retrieval arguments or if the main DataWindow also has retrieval arguments: It's hard to tell which DataWindow the dialog box applies to, and it's easy to spend a long time debugging the wrong code. The ugly Brute Force solution works like this: Call GetChild and retrieve the DDDW. Check if the DDDW is still empty. If the DDDW is empty, add a dummy row with InsertRow. Retrieve the main DataWindow. If the DDDW was empty, get rid of the dummy row. Figure 2 shows the PowerScript version complete with error checking (which was what the REAL Tip Number One was all about.)

Figure 2: The cb_Retrieve.clicked Script

integer li_RC long ll_row_count DataWindowChild dwc_table2 boolean lb_dwc_table2_is_empty long ll_row // Get addressability to the DDDW. li_RC = parent.dw_table1_a.GetChild ( "column1", dwc_table2 ) if li_RC <> 1 then MessageBox ( "Error", "GetChild failed" ) halt close end if // Fill the DDDW. li_RC = dwc_table2.SetTransObject ( SQLCA ) if li_RC <> 1 then MessageBox ( "Error", "SetTransObject dwc_table2 failed" ) halt close end if ll_row_count = dwc_table2.retrieve ( sle_retrieval_argument.text ) if ll_row_count < 0 then MessageBox ( "Error", "Retrieve dwc_table2 failed" ) halt close end if // Check to see if the DDDW is still empty. if dwc_table2.RowCount() = 0 then lb_dwc_table2_is_empty = true else lb_dwc_table2_is_empty = false end if // Temporarily put something in the DDDW, if necessary // to prevent a "Specify Retrieval Arguments" dialog box // from appearing when the main DW is retrieved. if lb_dwc_table2_is_empty then ll_row = dwc_table2.InsertRow ( 0 ) if ll_row <> 1 then MessageBox ( "Error", "InsertRow dwc_table2 failed" ) halt close end if end if // Load the main DataWindow. li_RC = parent.dw_table1_a.SetTransObject ( SQLCA ) if li_RC <> 1 then MessageBox ( "Error", "SetTransObject dw_table1_a failed" ) halt close end if ll_row_count = parent.dw_table1_a.retrieve() if ll_row_count < 0 then MessageBox ( "Error", "Retrieve dw_table1_a failed" ) halt close end if // Set the DDDW back to empty again, if necessary. if lb_dwc_table2_is_empty then li_RC = dwc_table2.reset(); if li_RC <> 1 then MessageBox ( "Error", "Reset dwc_table2 failed" ) halt close end if end if

The same annoying dialog box will appear if you call InsertRow() on the main DataWindow instead of Retrieve() (if SetTransObject() was called first for the main DataWindow, that is.) Figure 3 shows how the same technique can be used to solve the InsertRow problem.

Figure 3: The cb_InsertRow.clicked Script

integer li_RC DataWindowChild dwc_table2 boolean lb_dwc_table2_is_empty long ll_row // Get addressability to the DDDW. li_RC = parent.dw_table1_a.GetChild ( "column1", dwc_table2 ) if li_RC <> 1 then MessageBox ( "Error", "GetChild failed" ) halt close end if // Check to see if the DDDW is empty. if dwc_table2.RowCount() = 0 then lb_dwc_table2_is_empty = true else lb_dwc_table2_is_empty = false end if // Temporarily put something in the DDDW, if necessary // to prevent a "Specify Retrieval Arguments" dialog box // from appearing when InsertRow is called for the main DW. if lb_dwc_table2_is_empty then ll_row = dwc_table2.InsertRow ( 0 ) if ll_row <> 1 then MessageBox ( "Error", "InsertRow dwc_table2 failed" ) halt close end if end if // Insert a new row into the main DW. li_RC = parent.dw_table1_a.SetTransObject ( SQLCA ) if li_RC <> 1 then MessageBox ( "Error", "SetTransObject dw_table1_a failed" ) halt close end if ll_row = parent.dw_table1_a.InsertRow ( 0 ) if ll_row < 1 then MessageBox ( "Error", "InsertRow dw_table1_a failed" ) halt close end if // Set the DDDW back to empty again, if necessary. if lb_dwc_table2_is_empty then li_RC = dwc_table2.reset(); if li_RC <> 1 then MessageBox ( "Error", "Reset dwc_table2 failed" ) halt close end if end if