|Changes-Only Interfaces (part 2)||KEVIN RESCHENBERG|
In last week's item I discussed changes-only interfaces and
suggested using a custom table to detect changes. I also mentioned that one table could be shared by
all of your changes-only interfaces. Today we'll get into the implementation details.
Since each interface sends a different set of fields, how can we design a shared table that won't require
a lot of fields and continual maintenance? Well, we could start with the simplest type of table there is:
a table containing only one column. Let's call that field X_DATA (where X stands for the prefix you use
for all of your custom objects).
The trick is simply to build a "proposed" interface record in one string variable and then to check the
table to see if the exact same string was sent on a previous file. If the string is in
the table, we don't send it again. If it's not there, we write it to the file and also write it into the table
so that it won't be sent again in the future. That's all there is to it.
It's easy to send a full file whenever it's necessary to refresh all of the data. Just clear the table. The
logic is the same. The program will not find any of the "proposed" records on the file and will send everything.
With a few changes we can make this table even more useful.
First, we should add a program identification column to the table. Call it X_PROGRAM and fill it with the name
of the interface program. This permits sharing of the table among all of the interface programs.
Last week I mentioned that using this table will also support interfaces that require an "add/change/delete"
indicator. To make this possible, we need to add another column to the table. Let's call this field X_KEY.
What goes into that field depends on the interface requirements. We need to determine what identifies a
particular row on the interface file. For example, suppose we are writing an interface that sends employee
names and addresses. The "key" for this file might be the employee ID, even if that field does not even appear
on the file.
How does having a key help? With the X_KEY and X_DATA strings built, we follow this logic:
- If our key and data exactly match a row in the table, there is no change and nothing is written to the file.
- If our key string is not found on the table, this record is an "add".
- If our key string is found, but the data is different, this record is a "change".
- If a key exists on the table but not among the "proposed" record keys, it represents a "delete".
In practice, detecting deletes will require some additional coding. We might put another column on the table—a
one-character field indicating whether the row was matched with a file record. With that in place, detecting
deletes becomes an easy matter.
Here is an SQR example of the simplest type of changes-only interface. This one reports all additions and
changes (but not deletes) in a .CSV-format file. It does not report what type they are—that is, it does not have an "add/change/delete" indicator.
That functionality requires only a small extra coding effort, as described above.
ORDER BY NAME
! Build the proposed record
let $Key = &emplid
let $Data = '"' || rtrim(&name, ' ') || '"'
|| ',"' || rtrim(&address1, ' ') || '"'
|| ',"' || rtrim(&address2, ' ') || '"'
|| ',"' || rtrim(&city, ' ') || '"'
|| ',"' || rtrim(&state, ' ') || '"'
|| ',"' || rtrim(&postal, ' ') || '"'
! Try to find it in the table
let #Found = 0
let #Found = 1
WHERE X_PROGRAM = 'X_XXXXXX'
AND X_KEY = $Key
AND X_DATA = $Data
! If exact same key and data were not found in the table,
! write the record
if not #Found
write 1 from $Key ',' $Data
! In any case, update the table with the latest data
DELETE FROM PS_X_INTERFACE
WHERE X_PROGRAM = 'X_XXXXXX'
AND X_KEY = $Key;
INSERT INTO PS_X_INTERFACE
For the X_DATA column, use a 250-character field. If you need more space than that, define
additional fields and split your data string as needed. Avoid "long"-type fields.
They are more trouble than they're worth in this case.