Question is related to Application Engine in PeopleSoft Q9 A

Question is related to Application Engine in PeopleSoft

Q9 Assume there are 2 tables ABC and XYZ. Both the tables are having ROW_NUM filed with length of 3 digits. ABC is having the 5 values 699, 799, 899, 999, 299. And we are trying to insert these 5 values into XYZ table by adding 1 to each number

Main Section

Step1

DoSelect

%Select(ROW_NUM)

SELECT ROW_NUM

FROM ABC

PeopleCode

Here I will write the Commit only for 3rd Example.

SQL

INSERT INTO XYZ

VALUES (%Bind(ROW_NUM)+1)

Step2

PeopleCode

SQLExec(\"COMMIT\");

What would be the program flow when

Section Level - Auto Commit – After Step is unchecked,

Step Level – Commit After is Later.

Step Level – OnError is Ignore.

Scenario is same as above B, Assume due to some Code error Step2 – PeopleCode got errored out then what will happen?

What would be the program flow when

Section Level - Auto Commit – After Step is unchecked,

Step Level – Commit After is Default.

Step Level – OnError is Abort.

Solution

Q9

AE will throw and error while inserting 4th row.

Since we selected the Ignore option, AE will ignores that error and Issues the Commit, so first 3 rows will be get committed.

And then continues with the process and inserts the 5th row.

And it will not Commit after the Step1 since we selected Later option for Step1. So 5th row will be in buffer.

Then Step2 – PeopleCode will execute and issues the Commit.

So finally there would be 4 rows 700, 800, 900, 300 in XYZ.

In 1st iteration 699 + 1 = 700 will be inserted into XYZ.

In 2nd iteration 799 + 1 = 800 will be inserted into XYZ.

In 3rd iteration 899 + 1 = 900 will be inserted into XYZ.

In 4th iteration it will try to insert 999 + 1 = 1000 which is of 4 digits length. Since field length is 3, AE will throw an error.

All the 3 inserted rows will be rolled back.