Schultz’s PowerBuilder Notes

Datawindow Buffers


Original Buffer

This buffer holds the original data.  It is read only and invisible to user.  You can use it to restore data back to its original values, or you can even use it to do some validation of your own.  Strictly speaking, this is not a buffer.  Sybase does not call it one

How do I access values in the original buffer?

The GetItem() function has optional arguments to specify buffer and original value.  Note, there is no “original!” buffer to enter, instead you just set the original key word

LName = dw_employee.GetItemString(3, “emp_name”, Primary!, TRUE)

I don’t know if “Primary!” is required, it is probably the default.  TRUE refers to the original value.

Primary Buffer

This buffer is what is viewable.  It is the default buffer for Retrieve(), ImportFile() and InsertRow().  It is a Potential source of SQL Insert and Update statements

How do I know how many rows exist in the Primary buffer?

Li_cnt = dw_1.RowCount()

Filter Buffer

This buffer is for rows “filtered out” of Primary buffer by Filter().  The Datawindow painter can also have a filter designated.  It is Invisible to user.  This is a potential source of SQL Insert and Update statements.  Use the GetItemX or dot notation to get the values from this buffer.

How do I know the number of rows in the Filter Buffer?

Li_count = dw_1.FilteredCount()Filter( )

Filter()

You can filter on the server side with the WHERE clause.  Use this dialog to verify the syntax of a filter string you wish to build in script.  This can be a big time saver.

Example script:

String ls_filter 

Ls_filter = “State=’CA’ AND Status = ‘A’ AND Salary > 25000”
Dw_1.SetFilter(ls_filter)
Dw_1.Filter( )

To filter a DataWindow object with groups, call GroupCalc( ) after you call Filter( ). GroupCalc forces the DataWindow engine to recalculate the breaks in the grouping levels after you have added or modified rows in a DataWindow.

To have a “Specify Filter” dialog box appear, set a string variable to NULL, and pass it as the argument to SetFilter( ).  A subsequent Filter( ) will open the dialog box.

To find the current filter:

dw_1.Describe(“datawindow.table.filter”)

Or if you are using the PFC

dwcontrol.inv_filter.of_GetFilter ( )

By the way you can use this in a datawindow computed column as well – like Describe(“datawindow.table.filter”).  This is a great footer to put at the end of all your reports.

If you dw_1.SetFilter(ls_filter) and then dw_1.Filter(), any rows in the filter buffer are first moved back to the primary buffer before the new filter is applied

Update()

The filter buffer is included in the update.

Verifying that a filter expression is okay

To simulate the Verify function in the datawindow’s Verify button, use evaluate:

<string variable> = <datawindow>.describe(“Evaluate(<expression>, <rownum>)”)

Describe will return an “!” if any part of the expression is invalid.  Here is the code to Verify a filter string:

String ls_ret, ls_filter, ls_eval 

ls_filter = mle_filter.text
Ls_eval = “Evaluate(‘” + ls_filter + “’,0)”
Ls_ret = dw_1.Describe(ls_eval) 

If trim(ls_ret) = “false” then
   MessageBox(“Datawindow”, “Filter expression is OK”)
Else
   If trim(ls_ret) <> “!” then
      MessaeBox(“DataWiindow”, “Expression is not Valid”)
   End if
End if

Note: no messagebox is needed for ls_ret equaling “!” because the datawindow automatically displays this.  Because the row specified is 0, a valid filter expression is always going to return “false.”  Any ls_ret which is not false or “!” is a valid expression but not a valid filter expression.

Delete Buffer

Holds rows deleted using DeleteRow().  It is Invisible to the user.  Source of SQL Delete statements

Use GetItemX functions or dot notation to get the value

integer li_delete_cnt, li_row, li_del_row, ll_emp_id
string  ls_prov_cd

li_delete_cnt = idw_prov_selected.DeletedCount()
li_row = 0
li_del_row = 0
do while li_row < li_delete_cnt and li_del_row = 0
   li_row ++
   ll_emp_id     = idw_prov_selected.GetItemNumber(li_row, "emp_id",    Delete!, FALSE)
   ls_prov_cd    = idw_prov_selected.GetItemString(li_row, "prov_code", Delete!, FALSE)
   if as_prov_cd = ls_prov_cd and al_emp_id = ll_emp_id then
      li_del_row = li_row
   end if
loop

Return li_del_row

How do I know the number of rows in the Delete Filter Buffer?

Li_cnt = dw_1.DeletedCount()

About these ads

May 9, 2008 - Posted by | 2. Datawindows | , , , , , ,

9 Comments »

  1. Having trouble with DeletedCount(). It seems that

    number rows = 1
    dw.DeleteRow(1)
    number rows = 0
    integer it
    it = deletedCount()

    strangely, it = 0.
    I am not sure why, may be the data in dw is filtered

    Comment by Anna | June 4, 2009 | Reply

    • Yes, I have seen this PB bug several years ago. For some reason, the data in the Delete! buffer was discarded. I do not recall what caused it or what I did to solve the problem – sorry.

      Maybe somebody out in Internetland who knows the answer will post the solution.

      Comment by rick130 | June 5, 2009 | Reply

      • I had come accross this issue just recently – DeleteRow() did put the rows in Delete Buffer but I was not able to fetch them from Delete Buffer (not get the count using DeletedCount())
        The tweak that we made was used RowsMove() function and moved the rows from Primary Buffer to Delete Buffer.
        Surprisingly, now we were able to find the rows in Delete Buffer and access them as well.

        Comment by Animish | January 15, 2013

  2. After many tests, could not find the problem with DeletedCount().

    I fixed the problem by moving the rows
    from primary to delete buffer using rowsmove instead of DeleteRow

    I am using PB801 and I tried changing Update Properties in Row menu with no sucess

    Comment by Anna | June 9, 2009 | Reply

  3. Usually such behaviour can be experienced when trying to issue DeleteRow() for a newly inserted row with status of New! or NewModified! – they’re are not supposed to be moved into the Delete! buffer, because they shouldn’t issue any SQL statement on the next Update().

    Check if this is your case. Use GetItemStatus() with colindex=0 to obtain the status of an entire DW row.

    Great site! Thanks for the hints!

    Comment by Ivaylo Ivanov | September 2, 2009 | Reply

  4. One little addition to your description for the Original buffer:

    You can indirectly modify the values in this buffer by performing SetItem for the column and then SetItemStatus to NotModified! which transfers Primary! buffer value into the Original one.

    Note that such change would reflect the contents of the Where clauses of the issued SQL Update/Delete statements!

    Comment by Ivaylo Ivanov | September 2, 2009 | Reply

    • These comment gave me the “Aha!” moment when I was sure that dot notation wasn’t working the way documentation told me it should work. I was in a situation where I have a function that sets a value in the filter buffer, but because the row had a status of NotModified!, it was back-filling the Original value so that it was the same value as the Current value in the buffer. In other words, if you don’t want to change the Original value while making the new assignment, you must change the status to DataModified! first.

      Example:

      Operation: ldw_Target.object.data.filter[al_Row,ll_targetCol] = 1

      Precondition: NotModified!
      -This would change the value in both the Original Value and the Current value
      Any la_value
      la_value = ldw_Target.object.data.filter.Original[al_Row,ll_targetCol] //la_value becomes 1
      la_value = ldw_Target.object.data.filter.Current[al_Row,ll_targetCol] //la_value becomes 1

      Precondition: DataModified!
      -This would change only the current value, while original value would remain unchanged
      la_value = ldw_Target.object.data.filter.Original[al_Row,ll_targetCol] //la_value becomes 0
      la_value = ldw_Target.object.data.filter.Current[al_Row,ll_targetCol] //la_value becomes 1

      Comment by Dan | November 13, 2012 | Reply

  5. Hi, I’m a bit confused regarding how Insert or Update scripts are issued for the data in Filter buffer. When we filter, we just hide few rows from the user, we do not actually delete them from the database, right?

    Comment by kiran chalapaka | July 9, 2012 | Reply

    • Here is an example Kiran:

      Suppose you modify a row in a datawindow then apply a filter to the dw which moves this modified row to the filter buffer. If you then save the datawindow, the modified row in the filter creates the necessary update SQL to make the change to the database.

      Comment by rick130 | July 15, 2012 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: