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.
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
Quote from: Daniele A. on September 02, 2016, 03:58:55 PM
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.*.
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:
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.
QuoteI'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
Quote from: Reuben B. on September 07, 2016, 03:38:12 AM
Similar techniques are available to allow you to use the fully featured xml package
Quote from: Reuben B. on September 07, 2016, 03:38:12 AM
Or if JSON is your thing, use the JSON methods in the util package
Quote from: Reuben B. on September 07, 2016, 03:38:12 AM
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!
Hi Matthew
i am also curious let me know which is the fastest method !
thanks a lot !
Quote from: Matthew F. on September 07, 2016, 02:35:20 PM
...
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.
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
Quote from: Leo S. on September 09, 2016, 02:47:46 PM
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.
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
Quote from: Sebastien F. on September 09, 2016, 05:58:45 PM
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!
Quote from: Daniele A. on September 02, 2016, 03:58:55 PM
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
Quote from: Matthew F. on September 13, 2016, 07:18:54 PM
Quote from: Daniele A. on September 02, 2016, 03:58:55 PM
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):
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.
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.
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
Hello,
I can confirm: there is a lack of information. base.TypeInfo.create does not distinguish empty strings from NULLs.
Trailing blanks are not significant in 4GL. "foo" == "foo " returns TRUE. Strings are right-trimmed by base.TypeInfo.create for that reason.
The attribute value should hold a single blank character (" "). That's the way how UNLOAD serializes CHAR and VARCHAR.
A new bug "base.TypeInfo.create() must distinguish empty strings form NULLs" should be created.
Hi everybody,
The bug is created as:
FGL#4372: base.TypeInfo.create() must distinguish empty strings from NULLs
Matthew, I let you follow up with your local support center about the priority and dates for a fix.
Thanks.
Sisa.
Quote from: Sisavanh S. on September 14, 2016, 03:29:53 PM
Hi everybody,
The bug is created as:
FGL#4372: base.TypeInfo.create() must distinguish empty strings from NULLs
Matthew, I let you follow up with your local support center about the priority and dates for a fix.
Thanks.
Sisa.
Thank you!