Tech Talk

LIGHTLY TECHNICAL OBSERVATIONS ON PEOPLESOFT, SQR, AND RELATED AND UNRELATED TOPICS

Archives

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.