1. Glenn Barber
  2. PowerBuilder
  3. Tuesday, 1 September 2020 19:37 PM UTC

Last week I illustrated a snapshot technique to capture copies of data sets in the database associated with a specific transaction.  Once we sorted out the stored procedure issue (needed to be closed) we have found that it works pretty well for capturing the state of a set of data in time.

The bigger problem now is how best to illustrate to a user in a report the differences between two complex sets of multiple rows that at times can have up to 100 individual columns and many rows

The goal here is to derive a report from two data stores which represent the same column set with data at different points of time.  We have time stamps for added and changed to help.

My question is whether anyone already had developed routines for comparing these sets that can identify rows added or deleted from the original set in the newer set and can highlight columns that have been changed?

Were there any reporting styles which helped to make the differences stand out?

If you know of any routines in the pfc or other libraries, or even third party tools, please suggest.

I know I can probably knock this out, but hate to do this if it had already been done before.

Sivaprakash BKR Accepted Answer Pending Moderation
  1. Monday, 21 September 2020 07:32 AM UTC
  2. PowerBuilder
  3. # 1

Have you tried Navicat Premium ?   It does list the difference in structures and data.  You can choose a version that matches your RDBMS also.

 

Comment
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Sunday, 20 September 2020 04:53 AM UTC
  2. PowerBuilder
  3. # 2

Hi Glenn, Happy to see you fix the global function issue. 

You may improve execution speed by front-loading certain global functions and user objects when app starts. More important back in PB4 days of slow disks and PB VM slow on loading inherited classes. These days becoming more relevant with slower network due to remote access via VPNs.

Example:

/* App instance variables */ PRIVATE PowerObject cache[]

/* Function on App object */ PUBLIC SUBROUTINE of_CacheClass(readonly string newClass)
try
   this.cache[UpperBound(this.cache) + 1] = create using newClass
catch (Throwable ex)
   // Fail in silence when class missing
end try

/* Somewhere in your code  */ EVENT Constructor_or_Open_somewhere()
                              app.of_CacheClass("gf_getCurrency")
                              app.of_CacheClass("gf_getFXRate")
                              app.of_CacheClass("gf_calcAmount")
                              app.of_CacheClass("gf_convertTemperature")
                              app.of_CacheClass("w_dialog_MustOpenQuick")
                              app.of_CacheClass("m_popup_dw_edit")

"CREATE" will load each specified class from disk into memory. Storing unused reference in variable that remains alive keeps object in memory. Any object class directly or indirectly inherited from PowerObject can be cached this way.

At one time I wrapped the cache as an NVO. It's of_AutoLoad function would scan all app's library list and cache every global function and every menu named m_popup***

/Michael

 

Comment
  1. Chris Pollach @Appeon
  2. Sunday, 20 September 2020 14:28 PM UTC
Hi Michael;

That loader impairment was fixed in PB 8.0 when Sybase rewrote the PB Object Loader.

However, in recent releases (2017 & 2019) where you have lots of PBL's in your Apps library list, there is a significant performance boost by arranging your PBL order in descending object activity list. So in Glenn's case, placing the global function in the first PBD could give it's execution better performance.

Note that in newer PB versions, the Object Class Loader caches objects in memory. So it has the same effect as what your trying to do in your example.

Regards ... Chris
  1. Helpful
  1. Michael Kramer
  2. Sunday, 20 September 2020 19:43 PM UTC
Hi Chris, you state that the class loader keeps a class in memory after last object or that class is garbage collected?

I assume class cache size has predefined upper limit and will garbage collect classes to reclaim cache space. Otherwise I see potential memory starvation. Correct?

Is class cache size configurable by some environment variable? or some API?

THX /M
  1. Helpful
  1. Chris Pollach @Appeon
  2. Sunday, 20 September 2020 20:20 PM UTC
Hi Michael;

No, the Class Loader keeps objects in memory as long as possible - even after garbage collection. What the loader does is keep a "usage count" on the various Classes and when the pool is full, purges the "least used" classes from the pool first.

Regards ... Chris
  1. Helpful
There are no comments made yet.
Glenn Barber Accepted Answer Pending Moderation
  1. Saturday, 5 September 2020 00:03 AM UTC
  2. PowerBuilder
  3. # 3

Below is an abbreviated SQL script from SQL Anywhere which compares two sets of rows from two snapshots - identifies Added Rows and Deleted Rows and lists the original and revised rows for those rows which were modified.  Im still testing this, but seems to do the job - then next step is flagging the individual rows and columns and highlighting Added Rows, Deleted Rows and Changed Columns in the output report.

---- Compare of Added, Changed and Deleted between current and prior sets of records for log_records sharing logid for the snapshot

---- Rows Deleted from prior set
SELECT 'D' as rowstatus, rowid, col1, col2, datechanged, userchanged, dateadded, useradded
FROM log_record where logid = :logid_prior
AND rowid NOT IN (SELECT rowid FROM log_record where logid = :logid_current )

UNION
----- Rows Added in current set
SELECT 'A' as rowstatus, rowid, col1, col2, datechanged, userchanged, dateadded, useradded
FROM log_record where logid = :logid_current
AND rowid NOT IN (SELECT rowid FROM log_record where logid = :logid_prior )

UNION
-- Row Changes since the prior set
(SELECT 'C1' as rowstatus, rowid, col1, col2, datechanged, userchanged, dateadded, useradded
FROM log_record where logid = :logid_current
AND rowid in (SELECT rowid from log_record where logid = :logid_prior)

EXCEPT

SELECT 'C1' as rowstatus, rowid, col1, col2, datechanged, userchanged, dateadded, useradded
FROM log_record where logid = :logid_prior)

UNION
--- Original Rows from Prior that we changed
(SELECT 'C0' as rowstatus, rowid, col1, col2, datechanged, userchanged, dateadded, useradded
FROM log_record where logid = :logid_prior
AND rowid in (SELECT rowid from log_record where logid = :logid_current)

EXCEPT

SELECT 'C0' as rowstatus, rowid, col1, col2, datechanged, userchanged, dateadded, useradded
FROM log_record where logid = :logid_current)

ORDER by rowid, rowstatus

Comment
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Thursday, 3 September 2020 22:24 PM UTC
  2. PowerBuilder
  3. # 4

For inspiration - what I did for semi-automated approval process with visual diff. Approved documents are invoice-like in structure and data complexity.

Automated diff in database used MINUS set operator in stored procedures.

Manual approve/reject on screen in PB app leveraging DataWindows and computed expressions. Visually, side-by-side comparison of current document vs. "prior month's document". Each data difference highlighted in both "current DW" and "prior DW". user quote: Feels like unexpected data shouts at me: "Look here, I differ!"

Techniques at play:

  • Same DataWindow object displays data for both current DW and prior DW.
  • Retrieval arguments include color-set to use
  • Current DW uses "full" colors, while Prior DW uses "disabled" colors
    Helps user focus on data to be approved.
  • SELECT retrieves both displayed and compared data. Sort order guarantees odd rows are displayed while even rows are hidden - - but accessible to DW expressions!
  • Constructor event configures the DW objects - like code to highlight differences.
  • Highlight uses expression on background color. See code snippet.
  • Columns referenced using "#3" for "column 3" instead of column names.
  • Expression uses placeholders instead of hard to read string concatenation.
  • Concatenates DW commands into one long text.
    • Faster to execute on dw.Modify than multiple.
    • Uses line-break instead of space as command separator
    • Any syntax error will report as "Error in line 12, column 345"
      Line-breaks imply that line 12 is command for column 12.
    • Save DW command to clipboard for debug purposes
  • "REJECTED" stamp in big, bold, red created as background text across full DW when document has status = rejected.
    • Uses Font.Escapement to angle the text from bottom-left to top-right.

Other DWs on window present document metadata, audit-trail, etc.

HTH /Michael

 

// Generated code (EXAMPLE)
//       #3.Background.Color="-1~tIF( #3 = #3[1], -1, 12318973)~r~n"'
//       Adds highlight to column 3 using BBF8FD as backcolor
string dwCmd, dwStatus, backLight
long   i, colCount, highlight

highlight = this.GetItemDecimal(1, 'hightlight')
backLight = '%COL.Background.Color="-1~tIF( %COL = %COL[1], -1, %WARN)~r~n"'
backLight = inv_string.ReplaceAll(BackLight, '%WARN', string(highlight))

colCount = this.object.DataWindow.Column.Count
for i = 1 to colCount
   dwCmd += inv_string.ReplaceAll(BackLight, '%COL', ('#' + string(i)))
next

dwStatus = this.Modify(dwCmd)
if dwStatus <> '' then
   // NOTE: Don't publish tech details to clipboard in PROD!
   Clipboard(dwStatus + '~r~n~r~n' + dwCmd)
end if
Comment
  1. Glenn Barber
  2. Sunday, 20 September 2020 02:16 AM UTC
I think it turns out that because its a global function Im calling, and its only referenced in a datawindow, that its isn't included in the build - it doesnt like adding it to the pbr - so I'm going to put a call in the app open to see if that does it.
  1. Helpful
  1. Glenn Barber
  2. Sunday, 20 September 2020 02:20 AM UTC
Yup - a dummy reference to the global function in the app open fixes it.
  1. Helpful
  1. Chris Pollach @Appeon
  2. Sunday, 20 September 2020 14:20 PM UTC
Hi Glenn;

Sounds like you did not compile the App with PBD's. That will fix the missing function issue.

Regards... Chris
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 3 September 2020 20:16 PM UTC
  2. PowerBuilder
  3. # 5

Hi Glenn;

  Have you thought about writing each DWO primary buffer to a DB Table and then use either the UNION or INTERSECT DML command to look for differences between TableA and TableB sets?

Example: https://www.sqlshack.com/compare-tables-sql-server

Note: most DBMS have the same or similar DML commands (like the SS example above) to accomplish the same.

Food for thought. HTH

Regards ... Chris

Comment
  1. Glenn Barber
  2. Friday, 4 September 2020 22:37 PM UTC
Now , if only I could use the dwitemstatus to directly color cells in a grid. I'd like the compare module that runs through the output to set a status in cells on the rows to identify which rows and cells were modified such that this data can not only handle the presentation in the datawindow but also can be read by the program that exports to Excel which can color appropriately. I can do this through SetItemStaus on the column or row - but unfortunately we have an Is_RowModified method supported in the dw expressions but not is_columnmodified - do you know of a way?
  1. Helpful
  1. Chris Pollach @Appeon
  2. Friday, 4 September 2020 23:20 PM UTC
Hi Glenn;

For colouring cells ... What about using DW "expressions" for setting text or background colours?

Regards ... Chris
  1. Helpful
  1. Glenn Barber
  2. Friday, 4 September 2020 23:27 PM UTC
I did a separate post on this - yes I would consider this - but such expressions can be challenging - and similar to read-out when testing.. I was hoping for an attribute like the dwitemstatus that could be set, tested and referenced in a dw expression - however there is no IsColumnModified supported in dw expressions - something missing (and requested for years).
  1. Helpful
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Wednesday, 2 September 2020 13:52 PM UTC
  2. PowerBuilder
  3. # 6

Glenn -

>>> I supposing I would first have to isolate added and deleted rows then display rows that were changed

   Here's a code block that I wrote to go through the different columns in a DW.


//*********************************************************************

ll_rowcount = dw_1.RowCount()
ll_max1     = dw_1.Object.DataWindow.Column.Count
ll_max2     = dw_2.Object.DataWindow.Column.Count

IF (ll_max1 = ll_max2) THEN
    ll_max = ll_max1
ELSEIF (ll_max1 > ll_max2) THEN
    ll_max = ll_max1
ELSE
    ll_max = ll_max
END IF

// Check each row
FOR ll_row = 1 TO ll_rowcount
    
    // Get the status of the row
    ldwi_status = this.GetItemStatus (ll_row, 0, Primary!)
    IF (ldwi_status = new!) OR (ldwi_status = newmodified!) THEN        
        

        
        // Check each column in the row    
        FOR ll_idx = ll_start TO ll_max
            
            // Get the name of the column
            ls_column = dw_1.Describe("#" + string (ll_idx) + ".Name")
                        
            // Get the data type of the column
            ls_coltype = Left (this.Describe (ls_column + ".Coltype"), 4)
        
            IF (ls_coltype = "char") THEN            //  CHARACTER DATATYPE
                ls_data = this.getitemstring (ll_row, ls_column)
                
            ELSEIF (ls_coltype = "date") THEN
                ls_data = string (this.getitemDateTime (ll_row, ls_column))
                
            ELSE
                ls_data = string (this.getitemnumber (ll_row, ls_column))             
            END IF
            
            IF (IsNull (ls_data)) THEN ls_data = ""
    
        NEXT     // For each column
      
        
    END IF  // If row status = New! or NewModified!
    
NEXT    // For each row


//*********************************************************************


Hope this helps,

Olan

Comment
  1. Glenn Barber
  2. Wednesday, 2 September 2020 18:01 PM UTC
What I see here is running through the set of columns for a row, extracting the name and converting the data to a string. However I don't see any comparison tracking - although I see you using this and dw_1 in the code - perhaps interchangeably. In my case both identical sets and datawindows would be retrieved into a datastore and I would need to look at date added and date changed timestamps as well as the id keys of the rows in the row sets. I then first I identify rows added or deleted - then perform the comparison process on matched rows that changed (somewhat like your code above) - the challenge is then flagging the columns that have changed in a dynamic comparison report of multiple sets of rows.. The question is whether to filter out any rows and columns that have not changed in the entire set if we have rows added and deleted and eliminating from the report any non critical columns that have no data..
  1. Helpful
  1. Olan Knight
  2. Thursday, 3 September 2020 14:49 PM UTC
Yes. The basics of getting the data are listed in the example above. What you DO with that data is something that you will need to code. For example, comparing ds_1.column_1 to dw_2.column_2, then adding the two values to dw_3 if they differ.



If a row exists in ds_1 and not in ds_2, then the r ow has been deleted fro ds_2. And yes, to track row deletions you need to h ave a row identifier in place; i.e. a column marked ROW_NUMBER that is not updateable once set.
  1. Helpful
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Tuesday, 1 September 2020 22:05 PM UTC
  2. PowerBuilder
  3. # 7

Glenn -

   The easiest presentation for the users is to simply display the data DIFFERENCES.

   Personally, I'd write a comparison function, pass in the two DW objects, and write generic code to do the following for each column in both DWs:
          1.  Get the column type
          2.  Get the column name and value
          3.  Compare dw_1.data to dw_2.data
          4.  Convert the data to string if it differs
          
   The presentation could be a simple report:

        Column Name        Dw_1 Value     Dw_2 Value
          Order Count           14558            14595


   The beauty of this presentation is that it's simple, clear, and can be as long or as short as the data requires with no change. The downside is that it requires a small bit of coding (which really is pretty simple).


Good Luck,

Olan

Comment
  1. Glenn Barber
  2. Tuesday, 1 September 2020 22:25 PM UTC
I supposing I would first have to isolate added and deleted rows then display rows that were changed, then highlight what was was changed in the presentation - filtering unchanged data that was not required for understanding. Not impossible code to write - or to generalize - however I was looking to see if someone had already done it - in the end, each set of net changes for each of the affect tables would have to be packaged in some report
  1. Helpful
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Tuesday, 1 September 2020 20:25 PM UTC
  2. PowerBuilder
  3. # 8

Greetings, Glenn -

One possible way to approach this would be to use an existing tool, such as the Workbook Compare tool for Excel. Presumably, you are able to export each of the two sets of data to an Excel Workbook. Here is a URL that describes this comparison tool:

https://support.microsoft.com/en-us/office/compare-two-versions-of-a-workbook-by-using-spreadsheet-compare-0e1627fd-ce14-4c33-9ab1-8ea82c6a5a7e

I've used this manually on occasion in the past when a text-based "diff" comparison tool was not capable of providing adequate information about the differences.

Please note the disclaimer on the web page that this utility is available only in select versions and flavors of Excel.

Regards, John

Comment
  1. Glenn Barber
  2. Tuesday, 1 September 2020 20:40 PM UTC
Thanks - this is an interesting suggestion as we already planned to export the results to a spreadsheet. Ive looked at excel compare which we use for comparing calculation results during testing - but didnt think the presentation would be too helpful. It may be that eliminating columns and rows unaffected beforehand (except for key fields) may simplify the presentation. Another complication is that there are going to be multiple sets of data - likely each on its own worksheet
  1. Helpful
  1. Glenn Barber
  2. Tuesday, 1 September 2020 21:03 PM UTC
BTW - I found this app (Spreadsheet compare 2016) under microsoft tools on the server where we run our applications. Unfortunately it crashes when trying to start it...
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.