Record.Insert() peoplecode not creating an exception on unique constraint error

Question

The peoplecode is straightforward, instantiate the record object, read from an XML file, assign values to the rec.fields, and then perform an insert followed by a commitwork().

The only thing that I can see that would be a reason for the data from the XML to not be inserted is that it is the second use of the same SSN in the file. The table it's being inserted into has BRANCH_ID, CREATION_DT, and SSN as the key structure. The BRANCH_ID and the CREATION_DT is static for the entire file.

Here's the code....

You'll see below that I have tried to use a TRY/CATCH (which did not work properly, in that it never found an exception to catch), as well as simply testing the boolean return of both the insert and commitwork. The boolean approach as you see throws an exception, but it does not have a valid reason to offer as an explanation for the failed insert.

As the code is below right now, you'll see that I'm simply looping through and not using the boolean of the insert/commitwork or a try/catch to determine an error. The program does not stop because of this unique constraint error, which is very strange. It simply keeps going on to the next row.

Naturally, I need to be able to log into the error table that the row was not inserted because of a unique constraint. Any suggestions?

&REC1 = CreateRecord(Record.D2_PLFS_TBL);
&REC1.SetDefault();

&Node1 = &rootNode.GetElementsByTagName("LocatorData");

&Mega1File.WriteLine("THERE ARE >" | &Node1.Len | " rows in this file."); 
&cDate = Substring(&cDate, 8, 2) | Substring(&cDate, 3, 5) | Substring(&cDate, 1, 2); 

For &Cnt1 = 1 To &Node1.Len
   rem try;
   &xmlDoc1 = CreateXmlDoc(&Node1 [&Cnt1].GenXmlString()); 

   &Mega1File.WriteLine(%This.GetElementData(&xmlDoc1, "SocialSecurityAccountNumber"));

   &REC1.GetField(Field.IBTRANSACTIONID).Value = &IBTransactionId;
   &REC1.GetField(Field.TRANSACTION_NBR).Value = &Cnt1;
   &REC1.GetField(Field.BRANCH_ID).Value = &Branch_CD1;
   &REC1.GetField(Field.CREATION_DT).Value = &cDate; 
   &REC1.GetField(Field.SSN).Value = %This.GetElementData(&xmlDoc1, "SocialSecurityAccountNumber");
   &REC1.GetField(Field.SSN1).Value = %This.GetElementData(&xmlDoc1, "XRefSocialSecurityAccountNumber");
   &REC1.GetField(Field.D2_ACCT_STATUS).Value = %This.GetElementData(&xmlDoc1, "AccountStatus");
   &REC1.GetField(Field.NAME).Value = %This.GetElementData(&xmlDoc1, "MemberName");
   &REC1.GetField(Field.NAME1).Value = %This.GetElementData(&xmlDoc1, "OwnerName");
   &REC1.GetField(Field.D2_MBR_TYPE).Value = %This.GetElementData(&xmlDoc1, "MemberType");
   &REC1.GetField(Field.D2_SPCL_HNDLNG).Value = %This.GetElementData(&xmlDoc1, "SpecialHandling");
   &REC1.GetField(Field.D2_DOMFOR_CD).Value = %This.GetElementData(&xmlDoc1, "DomForeignCode");
   &REC1.GetField(Field.ADDRESS1).Value = %This.GetElementData(&xmlDoc1, "Address");
   &REC1.GetField(Field.COUNTRY_2CHAR).Value = %This.GetElementData(&xmlDoc1, "CountryCode");
   &REC1.GetField(Field.CITY).Value = %This.GetElementData(&xmlDoc1, "CityName");
   &REC1.GetField(Field.STATE).Value = %This.GetElementData(&xmlDoc1, "StateName");
   &REC1.GetField(Field.POSTAL).Value = %This.GetElementData(&xmlDoc1, "ZipSuffix");
   &REC1.GetField(Field.POSTAL2).Value = %This.GetElementData(&xmlDoc1, "ZipName");
   &REC1.GetField(Field.BRANCH_FLAG).Value = %This.GetElementData(&xmlDoc1, "BranchOfService");
   &REC1.GetField(Field.D2_RANK_RATE).Value = %This.GetElementData(&xmlDoc1, "RankRate");
   &REC1.GetField(Field.UNIT_CD).Value = %This.GetElementData(&xmlDoc1, "UIC");
   &REC1.GetField(Field.RETIREMENT_DT).Value = %This.GetElementData(&xmlDoc1, "RetirementTransferDate");

   &REC1.Insert();
   CommitWork();

  /*        If &REC1.Insert() Then
        &Mega1File.WriteLine("INSERTED");

        CommitWork();
     Else
        &Mega1File.WriteLine("NOT !!!!!!    INSERTED");

        throw CreateException(0, 0, "exception here");
     End-If; 
  catch Exception &ex

     Local Record &recError = CreateRecord(Record.D2_DRAS2_ERROR);

     &Mega1File.WriteLine("Caught the ERROR >" | &ex.ToString() | "<");

     &recError.IBTRANSACTIONID.Value = &IBTransactionId;
     &recError.TRANSACTION_NBR.Value = &Cnt1;
     &recError.SSN.Value = %This.GetElementData(&xmlDoc1, "SocialSecurityAccountNumber");
     &recError.MESSAGE_NBR.Value = 0;
     &recError.MESSAGE_TEXT_254.Value = "IGS-PLFS - " | &ex.ToString();
     &recError.FIELDNAME.Value = "SSN";
     &recError.VALUE_TEXT.Value = "Duplicate Row, Unique Constraint on SSN";
     &recError.Insert();
     CommitWork();

  end-try;*/
End-For;

score 0 · Answer 1 · answered Jun 21 '18 at 20:53

Reading PeopleBooks, it says the insert method will return a value of false if those keys already exist. Any other reason will cause program termination.

so you could write something like:

if not &REC1.insert() then
    throw CreateException(0, 0, "Duplicate Insert");
End-If;

Record.Insert() peoplecode not creating an exception on unique constraint error

1 Answers1