You can create SQL definitions in PeopleSoft Application Designer. These can be entire SQL programs, or just fragments of SQL statements that you want to re-use. PeopleCode provides the SQL class for accessing these SQL definitions in your PeopleCode program at runtime.

The SQL class provides capability beyond that offered by SQLExec. Unlike SQLExec, which fetches just the first SELECTed row, operations for the SQL class allow iteration over all rows fetched. This can dramatically improve performance if you’re doing a million operations and you’ve set the BulkMode property to True.

Functions

  1. CreateSQL(&Sql,&var1,&var2)
  2. FetchSQL(SQL.Name,&dbtype,&Effdt)
  3. GetSQL(SQL.Name,&var1,&var2)
  4. StoreSQL(&Sql,SQL.Name,&dbtype,&Effdt)

Methods

  1. Close()
  2. Execute(&var1,&var2)
  3. Fetch(&var1,&var2)
  4. Open(SQL.Name,&var1,&var2)

Properties

  1. BulkMode         [R/W]
  2. IsOpen             [R]
  3. Ltrim                [R/W] %SQLStatus_OK
  4. ReuseCursor    [R/W]  0   No Errors
  5. RowsAffected   [R] %SQLStatus_NotFound
  6. Status             [R]   1       Record Not Found
  7. TraceName      [R/W] %SQLStatus_Duplicate
  8. Value              [R]   2       Duplicate Record Found

Code Example : SQL

Below example shows how to use SQL:

&SQL.Open(SQL.SEL_ORDER,&BU,&OrderNo);

While &SQL.Fetch(&Custid,&Billid)

          /*More Processing*/

End-While;

&SQL.Close();

Advertisements