1

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;
qyb2zm302
  • 6,458
  • 2
  • 17
  • 17
angel
  • 47
  • 8

2 Answers2

1

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.

qyb2zm302
  • 6,458
  • 2
  • 17
  • 17
  • 1
    Hello, thank you so much for looking into the code. I have replated the sqlexec statement with deleteitem. Hopefully other than this, the code is looking ok. There is also need to update one more record which is not in component definition. there is i have used sqlexec only. Let me know if this is fine. – angel Nov 30 '21 at 06:55
  • "There is also need to update one more record which is not in component definition. there is i have used sqlexec only. Let me know if this is fine." The same response in my answer (and the provided references) applies for this other record. If you can do it via CI, do so. If not, then you have to do your own research to ensure you understand and manually honor all the data relationships. Some ideas on how to that are: code reading (which is hard since there's a lot of code out there spread across multiple languages), PeopleTools trace on SQL, or database-level auditing with your DBA. – qyb2zm302 Dec 02 '21 at 02:08
  • thank you for your valuable input. as suggested i have started reading the existing code and checked all the reference related to other record and able to know the data relationships. I have one question, i have one field which is marked as required on page and for this field i m not going to receive data in file, so i tried passing blank or null value but its failing. any suggestion pls. – angel Dec 07 '21 at 19:26
  • That's where a PeopleTools trace could show you what normally happens. You could also try using the screen to add the data, then querying the required field using SQL to see what value it contains. Normally, this would be handled by the CI, but it sounds like you are attempting to bypass the CI (which is not generally recommended). – qyb2zm302 Dec 11 '21 at 01:06
  • Thank you. Got it. Regarding this i was trying to store the errors in the staging table caused during CI load. As it was National Id load, i am getting errors related to Nid format. I have tried writing "Update query in exception block : and trying to store error message in table . But the program is going into No Success. – angel Dec 13 '21 at 16:16
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...
ZeusT
  • 515
  • 2
  • 8