People soft component ci - delete row from base table

Question

There is a need to update/insert and delete data from existing table based in data coming in the file through component interface. Although I have written below code but not getting confidence, if there is any other way to perform the same , please tell me. Code -

&Osession = %session
&Ocipersonaldata = &osession.getcompintfc.(compintfc.ci_prsonal_data);
&Ocipersonaldata.interactivemode = true;
&Ocipersonaldata.gethistoryitems= false;
&Ocipersonaldata.edithistoryitems= false;

&Ocipersonaldata.keypropemplid= d_persnid_aet.emplid
&Opersnidcollection = Ocipersonaldata.coll_pers_nid;
&Found =false;

For &I = 1 to Opersnidcollection.count
  &Opersnid = Opersnidcollection.item(&I);

  If Opersnid.country= d_persnid_aet.country and &opersnid.nationalid_type =d_persnid_aet.nationalid_type then
    If d_persnid_aet.actn = 'delete'
      Sqlexec(delete from ps_persnid where emplid =:1 and country =:2 and nationalid_type =:3",d_persnid_aet.emplud,d_persnid_aet.country,d_persnid_aet.nationalid_type);
    Else 
      If d_persnid_aet.actn = 'insert' then
        &Pernid = &persnidcollection.item (persnidcollection.count);
        &Pernid.nationalid= d_persnid_aet.nationalid;
      Else
        &Persnid =persnidcollection.insertitem(persnidcollection.count);
        &Pernid.nationalid= d_persnid_aet.nationalid;
        &Pernid.country= d_persnid_aet.country;
        &Pernid.nationalid_type d_persnid_aet.nationalid_type
      End-if;

Answer 1

The only subjectively 'better' way would be to use an existing Entity Framework construct that doesn't use a CI. CI's are indeed expensive to load and run, so an EF option, if available, is much faster.

Always use CI's or Entity Services to change business data. By crafting SQL, you're immediately taking responsibility for all downstream changes that may hang off that table.

In addition to @qyb2zm302's good answer, when you manipulate the data directly, you'd do yourself a favour, in the long term, to use the PeopleCode APIs to do so.

Local Record &rPersnId;
&rPersnId= GetRecord(Record.PERSNID);
&rPersnId.EMPLID.Value = d_persnid_aet.emplid;
&rPersnId.COUNTRY.Value = d_persnid_aet.country;
&rPersnId.NATIONALID_TYPE.Value = d_persnid_aet.nationalid_type;
If &rPersnId.SelectByKey() then
   &ret = &rPersnId.Delete();
End-If;

There a many reasons to do this, namely:

1. Cares for DB schema;
2. Helps with debugging
3. Search and references to records is helpful
4. Removes 'magic' strings from your code
5. You don't have to craft/test/debug SQL strings.
6. You can tell if SelectByKey is successful, then also if the Delete is successful. More options in user feedback, etc.
7. The overhead shouldn't be terrible unless you're doing millions of deletes at once, then I'd question the larger approach...

Answer 2

The purpose of a Component Interface (CI) is to ensure you are honoring business logic.

I see a SQL delete statement in your code, which probably defeats the purpose of using the CI. Since PeopleSoft is database agnostic, it does not take advantage of built-in database features such as foreign keys and cascading deletions. Instead, the logic to maintain referential integrity is implemented at the Application layer (i.e. via PeopleCode, SQR code, COBOL code, Application Engine SQL, etc.).

If you really want to do a SQL update/insert/delete operation (which is discouraged), then you are incurring a research burden to:

• identify foreign keys and other tables that will need corresponding modifications, and
• write sql to modify the data in all those other tables as well.

For this reason, it's preferable to use the Component Interface to do deletes (e.g. using a the InsertItem or Deleteitem methods). This depends on the underlying Component definition you are using, since not all Components allow deletion.

• references:
  • Cedar Hills Group > Integration Broker: The Missing Manual > Best Practices with PeopleSoft Table Updates and Inserts
  • PeopleSoft PeopleTools 8.56 > PeopleBooks > Products > Development Tools > PeopleCode API Reference > Component Interface Classes > Data Collection Methods
  • IT Toolbox > PeopleSoft Blog > The Component Interface – Methods, Properties, & Collections – oh my!