Developer ToolsFree generatorPDF, Excel, Word exportOracle PeopleSoftAll Oracle PeopleSoft tools

PeopleCode SQLExec, SQL Fetch and Try-Catch Snippet Generator

This generator writes the PeopleCode patterns developers look up most: SQLExec for one row, CreateSQL with a Fetch loop, %SelectAll into a record object, try-catch with MessageBox, MsgGet and a rowset loop. It targets PeopleTools 8.5x and 8.6x.

Fill in

Key field

Field used in the WHERE clause.

Key value for testing

Record for single-row SQLExec

Record name without the PS_ prefix.

Column returned by SQLExec

Record for the Fetch loop

First select column

Second select column

Level 1 scroll record on the component

Primary record of the level 1 scroll you want to loop over.

Message set number

Custom message sets normally start at 20000.

Message number

Generated code

Download:

1. SQLExec: single row into variables

Local string &emplid = "KU0001";
Local string &value;

/* SQLExec returns only the first row of the result set */
If SQLExec("SELECT NAME FROM PS_PERSONAL_DATA WHERE EMPLID = :1", &emplid, &value) Then
   If None(&value) Then
      MessageBox(0, "", 0, 0, "No row found for %1", &emplid);
   End-If;
End-If;

2. CreateSQL with an explicit SELECT and a Fetch loop

Local SQL &sql;
Local string &a;
Local string &b;
Local integer &count = 0;

&sql = CreateSQL("SELECT DEPTID, JOBCODE FROM PS_JOB WHERE EMPLID = :1 ORDER BY DEPTID", &emplid);
While &sql.Fetch(&a, &b)
   &count = &count + 1;
   /* work with &a and &b here */
End-While;
&sql.Close();

3. %SelectAll into a record object, inside try-catch

Local Record &rec;
Local SQL &sqlRec;

try
   &rec = CreateRecord(Record.JOB);
   &sqlRec = CreateSQL("%SelectAll(:1) WHERE EMPLID = :2", &rec, &emplid);
   While &sqlRec.Fetch(&rec)
      /* fields are available as &rec.FIELDNAME.Value */
      &a = &rec.DEPTID.Value;
   End-While;
   &sqlRec.Close();
catch Exception &ex
   MessageBox(0, "", 20000, 1, "Error reading JOB: %1", &ex.ToString());
end-try;

4. MsgGet: read message catalog text

Local string &txt;
&txt = MsgGet(20000, 1, "Message not found: 20000, 1");

5. Loop over a level 1 rowset in the component buffer

Local Rowset &rs;
Local Row &row;
Local integer &i;
Local string &dept;

&rs = GetLevel0()(1).GetRowset(Scroll.JOB);
For &i = 1 To &rs.ActiveRowCount
   &row = &rs(&i);
   &dept = &row.JOB.DEPTID.Value;
End-For;

Check field names against your record definitions in Application Designer; JOB is effective-dated, so add EMPL_RCD and EFFDT logic (or %EffDtCheck) for current rows. Block 5 only works where the scroll record is a level 1 scroll on the component. Syntax follows the PeopleCode Language Reference for PeopleTools 8.5x and 8.6x.

Want a second pair of eyes on your PeopleCode?

Send us a PeopleCode program or Application Engine step and we will review it for SQL, buffer and error-handling issues.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Enter the key field, a test value, the records and columns you need, the scroll record of the component, and a message set and number from your message catalog. Blocks 2 and 3 reuse &emplid from block 1 and &a from block 2, so copy the declarations along with the blocks you need into the event (FieldChange, SavePreChange, an Application Engine PeopleCode action or an application class method). The record names you type are record definition names; the generator adds the PS_ prefix in literal SQL, which matches the default table naming. If your record uses a non-standard SQL table name, replace it, or switch to %Table(:1) with a record bind so PeopleTools resolves the table name for you.

Use SQLExec only when you expect zero or one row.
Use CreateSQL and Fetch for multi-row reads, and always Close the SQL object.
Use a record object with %SelectAll when you want every field and correct date handling.

What each built-in does

SQLExec(sql, binds, outputs) runs the statement and fills the output variables from the first row only; it returns True if the statement ran, not whether a row was found, so test the output for None. CreateSQL returns a SQL object; Fetch returns True while rows remain and fills its arguments in column order. %SelectAll(:1) expands to a SELECT of every field of the bound record, wrapping dates with %DateOut so Fetch into a record works. MessageBox takes style, title, message set, message number, default text and optional parameters that replace %1, %2. MsgGet takes set, number and default text. Exceptions raised by PeopleTools, such as SQL errors, are caught by catch Exception &ex, and &ex.ToString() returns the formatted text.

Gotchas

ActiveRowCount excludes rows the user has deleted in the buffer, while RowCount includes them, so loops that update data should use ActiveRowCount. In literal SQL, dates should go through meta-SQL such as %DateIn(:1) or %CurrentDateIn rather than database-specific functions, so the code stays portable across Oracle, SQL Server and Db2. SQLExec cannot be used to read more than one row, and an UPDATE through SQLExec in a FieldChange event bypasses component save processing, so prefer buffer changes or a Component Interface for transactional updates.

Close SQL objects inside loops, or you hold cursors until the program ends.
Bind values with :1, :2 instead of concatenating strings, to avoid SQL injection and parse overhead.
MessageBox in SavePreChange does not stop the save; use Error for that.

Frequently Asked Questions

Does PeopleCode SQLExec return multiple rows?

No. SQLExec fills its output variables from the first row only and discards the rest. For multiple rows, create a SQL object with CreateSQL and loop with While &sql.Fetch(...) End-While, then call &sql.Close(). SQLExec returns True when the statement executes, so check whether the output variable is empty to detect no rows.

What is the try catch syntax in PeopleCode?

Write try, the protected statements, then catch Exception &ex followed by the handling statements, and close with end-try;. You can catch more specific exception classes before the generic Exception. Inside the catch, &ex.ToString() gives the message text, and &ex.MessageSetNumber and &ex.MessageNumber give the catalog entry.

What are the MessageBox parameters?

MessageBox(style, title, message_set, message_num, default_text, params). Style 0 is a simple OK box. Title is normally passed as an empty string. If the message set and number exist in the catalog that text is used, otherwise the default text is shown. Extra parameters substitute %1, %2 and so on in the text.

How do I loop through a rowset in PeopleCode?

Get the rowset, for example GetLevel0()(1).GetRowset(Scroll.JOB), then use For &i = 1 To &rs.ActiveRowCount and read fields with &rs(&i).JOB.DEPTID.Value. For a standalone rowset not in the component buffer, use CreateRowset(Record.JOB) and Fill with a WHERE clause.

If you want developers and analysts to ask questions of your PeopleSoft data in plain English on your own servers, we can set up a private AI layer over it.

Talk to an Expert Explore More Free Tools