Changes-Only Interfaces (part 2)
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.
begin-procedure Main
begin-select
EMPLID
NAME
ADDRESS1
ADDRESS2
CITY
STATE
POSTAL
do Process
FROM PS_PERSONAL_DATA
WHERE ...
ORDER BY NAME
end-select
end-procedure
begin-procedure Process
! 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
begin-select
'X'
let #Found = 1
FROM PS_X_INTERFACE
WHERE X_PROGRAM = 'X_XXXXXX'
AND X_KEY = $Key
AND X_DATA = $Data
end-select
! If exact same key and data were not found in the table,
! write the record
if not #Found
write 1 from $Key ',' $Data
end-if
! In any case, update the table with the latest data
begin-sql
DELETE FROM PS_X_INTERFACE
WHERE X_PROGRAM = 'X_XXXXXX'
AND X_KEY = $Key;
end-sql
begin-sql
INSERT INTO PS_X_INTERFACE
(X_PROGRAM
,X_KEY
,X_DATA
) VALUES
('X_XXXXXX'
,$Key
,$Data
);
end-sql
end-procedure
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.
Until next time...
 Email: kevin@sparkpath.com |
