Question is related to Application Engine in PeopleSoft Q1As

Question is related to Application Engine in PeopleSoft

Q1.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\");

1.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.

2.What would be the program flow when

Section Level - Auto Commit – After Step is unchecked,

Step Level – Commit After is After Step.

Step Level – OnError is Abort.

Solution

1

1). In DoSelect 5 rows will be selected from ABC Table.

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.

Commit will not execute so no row will be inserted into XYZ.

2). Same as above example - Inserts 3 rows and AE will throw an error while inserting 4th row, Commit will not be executed, all 3 rows will be rolled back and there will not be any row in XYZ Table