next »

[Matthew F.]

I'm working on a function to do field-by-field comparisons between two records that have identical definitions. Table A has the current data. Table B is a temporary table that contains records having the same keys as records in Table A. The records in Table B will be used to update records in Table A.

We need to identify which fields have different values in the two records, in order to be able to insert an entry about that change in the audit log table.

A stored procedure could be used to do the comparisons, apply the updates, and insert the audit table entries. However, I'm curious to know if the field-by-field comparisons can be done in BDL without having to refer to the fields by name.

The reason I'd prefer not to refer to the fields by name is that such code would need to be maintained as fields are added/dropped/renamed in the database.

We do already have a means of selecting the column names and column numbers from the database, and then iterating over that result set in a FOREACH loop. If it were possible in BDL to refer to a field by its column number rather than the column name, that would allow us to iterate over the columns and compare the values in the corresponding fields in record A and record B.

I'm open to other approaches. The main goal is just to write code that doesn't have to be maintained as database columns are added/dropped/renamed.

Thank you.

[Daniele A.]

Hi Matthew,

if both records have the same structure you could use the function base.TypeInfo.create(r_rec)
once you have created two objects of type om.domnode you can do iteration with his methods
and do what you want (comparison and so on ...)

bye
Daniele

[Matthew F.]

Hi Matthew,

if both records have the same structure you could use the function base.TypeInfo.create(r_rec)
once you have created two objects of type om.domnode you can do iteration with his methods
and do what you want (comparison and so on ...)

bye
Daniele

Thank you for your reply! That's very interesting.

Yes, both records will always have identical structures.

Here is some code that appears to work. In our actual program, the records would be defined LIKE table1.*.

Code:

FUNCTION COMPARE_RECORDS()

   DEFINE n1, n2                    om.DomNode
   DEFINE child_node1, child_node2  om.DomNode
   DEFINE name1, name2              STRING
   DEFINE value1, value2            STRING
   DEFINE i, i_max                  INTEGER

   DEFINE r1, r2 RECORD
      key       INTEGER,
      firstname CHAR(20),
      lastname  CHAR(20),
      birthdate DATE
   END RECORD

   LET r1.key       = 123
   LET r1.firstname = "Grace"
   LET r1.lastname  = "Hopper"
   LET r1.birthdate = "12/9/2006"
   LET n1 = base.TypeInfo.create(r1)

   LET r2.key = 123
   LET r2.firstname = "Grace"
   LET r2.lastname  = "Hopper"
   LET r2.birthdate = "12/9/1906"
   LET n2 = base.TypeInfo.create(r2)

   DISPLAY "RECORD 1:\n", n1.toString()
   DISPLAY "RECORD 2:\n", n2.toString()

   LET i_max = n1.getChildCount()

   FOR i = 1 TO i_max
      LET child_node1 = n1.getChildByIndex(i)
      LET name1  = child_node1.getAttribute("name")
      LET value1 = child_node1.getAttribute("value")

      LET child_node2 = n2.getChildByIndex(i)
      LET name2  = child_node2.getAttribute("name")
      LET value2 = child_node2.getAttribute("value")

      IF value1 != value2 THEN
         DISPLAY "DIFFERENCE FOUND"
         DISPLAY "   record 1: ", name1, " = '", value1, "'"
         DISPLAY "   record 2: ", name2, " = '", value2, "'"
      END IF
   END FOR

END FUNCTION

The output is:

Code:

RECORD 1:
<?xml version='1.0' encoding='windows-1252'?>
<Record>
  <Field type="INTEGER" value="123" name="key"/>
  <Field type="CHAR(20)" value="Grace" name="firstname"/>
  <Field type="CHAR(20)" value="Hopper" name="lastname"/>
  <Field type="DATE" value="12/09/2006" name="birthdate"/>
</Record>

RECORD 2:
<?xml version='1.0' encoding='windows-1252'?>
<Record>
  <Field type="INTEGER" value="123" name="key"/>
  <Field type="CHAR(20)" value="Grace" name="firstname"/>
  <Field type="CHAR(20)" value="Hopper" name="lastname"/>
  <Field type="DATE" value="12/09/1906" name="birthdate"/>
</Record>

DIFFERENCE FOUND
   record 1: birthdate = '12/09/2006'
   record 2: birthdate = '12/09/1906'

If there any alternative approaches, I'd be interested to hear about those as well. As a new BDL developer, I'm still getting acquainted with what's in the BDL toolkit.

Thank you.

[Reuben B.]

I'm open to other approaches. The main goal is just to write code that doesn't have to be maintained as database columns are added/dropped/renamed.

base.TypeInfo as Daniele pointed out, allows you to then use the om library methods

Similar techniques are available to allow you to use the fully featured xml package http://4js.com/online_documentation/fjs-fgl-manual-html/index.html#c_gws_XML_Library_001.html (om is a subset of xml) via http://4js.com/online_documentation/fjs-fgl-manual-html/index.html#c_gws_XmlSerializer_VariableToDom.html

Or if JSON is your thing, use the JSON methods in the util package http://4js.com/online_documentation/fjs-fgl-manual-html/index.html#c_fgl_Class_util.html via http://4js.com/online_documentation/fjs-fgl-manual-html/index.html#c_fgl_ext_util_JSON_stringify.html or http://4js.com/online_documentation/fjs-fgl-manual-html/index.html#c_fgl_ext_util_JSONObject_fromFGL.html

Otherwise in more general if you want to write code that does not have to be maintained as columns added/dropped, you can use the new dynamic dialog methods http://4js.com/online_documentation/fjs-fgl-manual-html/index.html#c_fgl_prog_dialogs_dynamic.html

Hope that helps,

Reuben

 

[Matthew F.]

Similar techniques are available to allow you to use the fully featured xml package

Or if JSON is your thing, use the JSON methods in the util package

Otherwise in more general if you want to write code that does not have to be maintained as columns added/dropped, you can use the new dynamic dialog methods http://4js.com/online_documentation/fjs-fgl-manual-html/index.html#c_fgl_prog_dialogs_dynamic.html

That was all good information, and it was very helpful of you to provide the URLs.

I'm curious to see what the relative performance might be in our use case if we used util.JSONObject instead of om.DomNode. I'll have to run some tests.

Thank you!

[Daniele A.]

Hi Matthew
i am also  curious let me know which is the  fastest method !
thanks a lot !

[Reuben B.]

...

That was all good information, and it was very helpful of you to provide the URLs.

I'm curious to see what the relative performance might be in our use case if we used util.JSONObject instead of om.DomNode. I'll have to run some tests.

Thank you!

... I'll make sure you are aware of the profiler http://4js.com/online_documentation/fjs-fgl-manual-html/#c_fgl_profiler_001.html which is a handy tool to use for these sort of tests.

[Leo S.]

Coming a bit late, but nothing prevents you from write a little function 4GL source code generator which does create this wanted function for you.
This will give you the smallest run time overhead, 'just' make sure you re generate the function(s) in case of database updates.
Regards, Leo

[Matthew F.]

Coming a bit late, but nothing prevents you from write a little function 4GL source code generator which does create this wanted function for you.
This will give you the smallest run time overhead, 'just' make sure you re generate the function(s) in case of database updates.
Regards, Leo

Thanks, Leo. That's an interesting idea. I may try that once my handwritten code is fully tested.

[Sebastien F.]

Hi Matthew,

If I would have to do data change auditing, I would implemented this the database server with INSERT / UPDATE / DELETE triggers...
This way, ANY DB client program modifying the database table would be tracked, as the process is centralized in the database server.
If you want to track each column value change in your auditing records, you will have to update the triggers when source table columns change.

If you still want to implement this in Genero BDL, maybe you can give a try to base.SqlHandle(), which allows you to get the description of column names and types for a given SELECT.
You could then build dynamic SQL from the columns and values, to produce auditing records in your log table (or file?).
Code written with base.SqlHandle() will be fully generic, so the database schema changes would be taken into account implicitly.

http://4js.com/online_documentation/fjs-fgl-manual-html/#c_fgl_ClassSqlHandle.html

Seb

[Matthew F.]

Hi Matthew,

If I would have to do data change auditing, I would implemented this the database server with INSERT / UPDATE / DELETE triggers...
This way, ANY DB client program modifying the database table would be tracked, as the process is centralized in the database server.
If you want to track each column value change in your auditing records, you will have to update the triggers when source table columns change.

If you still want to implement this in Genero BDL, maybe you can give a try to base.SqlHandle(), which allows you to get the description of column names and types for a given SELECT.
You could then build dynamic SQL from the columns and values, to produce auditing records in your log table (or file?).
Code written with base.SqlHandle() will be fully generic, so the database schema changes would be taken into account implicitly.

http://4js.com/online_documentation/fjs-fgl-manual-html/#c_fgl_ClassSqlHandle.html

Seb

That sounds like great advice. Thank you!

[Matthew F.]

Hi Matthew,

if both records have the same structure you could use the function base.TypeInfo.create(r_rec)
once you have created two objects of type om.domnode you can do iteration with his methods
and do what you want (comparison and so on ...)

bye
Daniele

Thank you again for your suggestion to use om.DomNode objects. Here's a follow up reply after some further testing.

Unfortunately, field values of NULL in a record are represented with value="" in the om.DomNode's attributes. That will lead to incorrect results if we compare records where fieldX = "" in record1, and fieldX IS NULL in record2.

The following code will show that the records are different:

let record1 = record2
let record1.fieldX = ""
let record2.fielxX = NULL

if (record1 != record2) then ....

However, if we then create DomNodes from these records and iterate over the fields, and compare their value attributes, no fields will be found to have different values.

I think Sebastien F's recommendation to do change auditing on the database is a good alternative, but I just wanted to report back about how NULL values are represented by om.DomNode.

Best regards,

 - Matthew

[Matthew F.]

Hi Matthew,

if both records have the same structure you could use the function base.TypeInfo.create(r_rec)
once you have created two objects of type om.domnode you can do iteration with his methods
and do what you want (comparison and so on ...)

bye
Daniele

Thank you again for your suggestion to use om.DomNode objects. Here's a follow up reply after some further testing.

Unfortunately, field values of NULL in a record are represented with value="" in the om.DomNode's attributes. That will lead to incorrect results if we compare records where fieldX = "" in record1, and fieldX IS NULL in record2.

The following code will show that the records are different:

let record1 = record2
let record1.fieldX = ""
let record2.fielxX = NULL

if (record1 != record2) then ....

However, if we then create DomNodes from these records and iterate over the fields, and compare their value attributes, no fields will be found to have different values.

I think Sebastien F's recommendation to do change auditing on the database is a good alternative, but I just wanted to report back about how NULL values are represented by om.DomNode.

Best regards,

 - Matthew

Please disregard. My actual code for the comparison was incorrect.

Records with nullable fields cannot be tested for simple equality/inequality.

In a field by field comparison using DomNodes, a field with a NULL value in the record will not have a value attribute in the DomNode's Field element. The call to getAttribute for that Field element will return NULL. Presumably that result can then be compared with code like the following (not yet tested, but I'll report the result shortly):

Code:

   let node1 = base.TypeInfo.create(old_record)
   let node2 = base.TypeInfo.create(new_record)

   let i_max = node1.getChildCount()

   for i = 1 to i_max
      let child_node1 = node1.getChildByIndex(i)
      let col_type    = child_node1.getAttribute("type")
      let col_name    = child_node1.getAttribute("name")
      let old_value   = child_node1.getAttribute("value")

      let child_node2 = node2.getChildByIndex(i)
      let new_value   = child_node2.getAttribute("value")

      if (old_value is null and new_value is not null)
      or (new_value is null and old_value is not null)
      or (new_value != old_value) then
         display "Values differ for field ", col_name
      end if
   end for

Sorry for causing confusion.

[Matthew F.]

Man, this in not my day. I shouldn't touch code or post on forums when I didn't get enough sleep the night before.

So in fact the value attribute is omitted under the following conditions:

- when the value is NULL
- when the value is an empty string

So there still isn't sufficient information to distinguish when fields have changed if a NULL or empty string is involved.

I'll try using the SqlHandle class or field changed triggers on the database itself.

[Reuben B.]

Hi Matthew,

You raise an interesting issue. " ", "", and NULL are always interesting cases.  I've created a support call to verify that we have the expected behaviour for these with serialization to om, xml, and JSON.

Certainly from my pre-4Js days, we had a standard in our 4gl application to avoid situations where "", and " " would end up in the database, and so avoided these types of issues.

Reuben