BUFFER_COMPARE question

[StuartT]

I hope someone can help me out, I am trying to do a buffer compare between 2 database tables but excluding a couple of fields.

What is the correct syntax?
I can get it to work by hard coding the fields such as:
buffer-compare table1 except table1.field1 table1.field2 to table2
but i want to have the exclusions in a variable of some sort and do something like:
buffer-compare table1 except vfields to table2
where vfields = "table1.field1 table1.field2"

Can anyone give me the correct syntax as OEM help only gives the syntax and it is not very clear.

 

[GregTomkins]

I think for this you would need a dynamic buffer, and to use the BUFFER-COMPARE *method*, which is similar to but not the same as the BUFFER-COMPARE *statement*. Here is syntax which I verified works on my DB:

DEF VAR h AS HANDLE.
h = BUFFER mfclac:HANDLE.
h:FIND-FIRST().
DEF VAR i AS HANDLE.
i = BUFFER mfcl:HANDLE.
i:FIND-FIRST().
DISP h:BUFFER-COMPARE(i,"foo").

mfclac -> replace with first table name
mfcl -> replace with second table name
replace FIND's with logic to find the records you really want
foo -> replace with list of fields you want to exclude
replace DISP with logic based on what you want to do based on the result of the comparison

BTW the 2 'tables' mentioned above would normally actually be buffers into the same DB table (though I didn't in my example). Confusing things can happen, in my experience, when you try to do BUFFER-COMPARE between buffers representing two different tables.

 

[StuartT]

I was hoping to keep this as simple as possible as I am going to be updating an include file that is run for all our auditing to add in the possibility of doing an exclusion without having to redo all our existing triggers as it is for one particular anomaly.

so i was going to add &vexclude="'field1 field2'" into the call of the include and within the include i was going to relpace:

BUFFER-COMPARE {&tablename} TO oldtable SAVE vFieldList.
with
BUFFER-COMPARE {&tablename} TO oldtable SAVE vFieldList.
if {&vexclude} <> "" then
BUFFER-COMPARE {&tablename} except {&vexclude} TO oldtable SAVE vFieldList.

Which by looking at the syntax should be very straightforward as it states "You can specify a list of fields to exclude" but it appears that this list must be explicit withing the buffer-compare statement and not as a variable containing the list.

 

[GregTomkins]

Right. That's the fundamental difference between "dynamic stuff" and "static stuff". Static (the statement version) is simpler, and might perform better (though I have my doubts that that really matters...) but it generally requires that all table and field names be hard-coded, which often is not what you want.

 

[GregTomkins]

Actually - sorry - I just clued into the include file aspect of your question. I despise includes and avoid them 99% of the time so I am a bit fuzzy on this but I THINK by using them, your idea should work.

From what evil bits of syntax I remember, I think your problem is not the BUFFER-COMPARE; that should work; the problem is that 'if {&vexclude} <> ""' would expand out to 'if fielda fieldb <> ""' which is invalid syntax. Maybe you could put {&vexclude} in quotes so that it would expand out to 'if "fielda fieldb" <> ""', but I didn't try this theory out.

 

[StuartT]

very true, however the buffer-compare statement allows you to pass the table name as a variable passed into an include, so why not the list of fields as well

 

[StuartT]

Actually - sorry - I just clued into the include file aspect of your question. I despise includes and avoid them 99% of the time so I am a bit fuzzy on this but I THINK by using them, your idea should work.

From what evil bits of syntax I remember, I think your problem is not the BUFFER-COMPARE; that should work; the problem is that 'if {&vexclude} <> ""' would expand out to 'if fielda fieldb <> ""' which is invalid syntax. Maybe you could put {&vexclude} in quotes so that it would expand out to 'if "fielda fieldb" <> ""', but I didn't try this theory out.

i hadn't tried the if part yet, i was simply running the call to an include and trying to get the buffer-compare with the except to work first

 

[GregTomkins]

I think you may be confusing the concept of 'variable'. (&vexclude} is a preprocessor argument that must expand to a constant, not a variable.

 

[StuartT]

I think you may be confusing the concept of 'variable'. (&vexclude} is a preprocessor argument that must expand to a constant, not a variable.

No missunderstanding, I am going to be setting {&vexclude} in the call to the include, so it will be a constant value within the include.

I can work around fairly easily though by doing the buffer-compare with no excludes to get a list of fields that have changed then reconstructing the list excluding the ones passed in {&vexclude}