Using Record Objects to perform inserts and updates

When coding PeopleSoft applications you often need to insert or update a table. There are really two approaches:

1. Use a SQLExec to perform the action
2. Use a Record Object to perform the action

Both of these approaches work just fine. However, using the second method is optimal.

Let’s say we have a record called Z_HEADER that has the following structure.

Z_HEADER
EMPLID
LASTUPDDTTM
DEPTID

If you wanted to do an insert on the record using SQLEXEC it would look something like this.

  local string &s;
  local string &someEmplid, &someDeptid;
  &someEmplid = "123";
  &someDeptid = "xyz";
  &s = "INSERT INTO PS_Z_HEADER (EMPLID, LASTUPDDTTM, DEPTID) values :1, %currentdatein, :2";
  SQLEXEC(&s, &someEmplid, &someDeptid);

If you wanted to do an insert using a record object it would look something like this.

  local record &recHdr = Createrecord(record.Z_HEADER);

  &recHdr.EMPLID.value = "123";
  &recHdr.DEPTID.value = "xyz";
  &recHdr.LASTUPDDTTM.value = %datetime;

  if &recHdr.insert() then
    /* everything was fine - record inserted */
  else
    /* could not be inserted - probably a duplicate */
  end-if;

Why is the record approach better?

If another developer comes along and adds a new field like MANAGER_ID to the table but does not update any code the SQLEXEC method will start failing. Why? Because PeopleTools defines all table fields at the database level as NOT NULL for character fields. So when the field was added to the database and the code was not updated the database will try to insert a NULL in the MANAGER_ID field. However, an error will be thrown.

If you would have used the record object there would be no errors. How is this possible? When you perform inserts using a record object the SQL is dynamically generated based on the record fields from the PeopleTools tables. If the MANAGER_ID field were added to the table and no code was updated the record object based approach would have started to insert a blank space for the MANAGER_ID field and the code would not have errored out. Using this approach will make your overall system less brittle to changes.

Additionally, using the record object based approach can also make your code more compact (i.e. less lines of code). Let’s say you start passing record objects as function parameters. You can then easily copy field values between records without having to specify every field value as you can copy like named fields between the parameters and the record to be inserted. Less lines of code means less chances for bugs.