Tuesday 8 November 2016

SQLExec function

Description:
Use the SQLExec function to execute a SQL command from within a PeopleCode program by passing a SQL command string. The SQL command bypasses the Component Processor and interacts with the database server directly. If you want to delete, insert, or update a single record, use the corresponding PeopleCode record object method.
If you want to delete, insert, or update a series of records, all of the same type, use the CreateSQL or GetSQL functions, then the Execute SQL class method.

Limitation of SQLExec SELECT Statement:
SQLExec can only Select a single row of data. If your SQL statement (or your SQL.sqlname statement) retrieves more than one row of data, SQLExec sends only the first row to its output variables. Any subsequent rows are discarded. This means if you want to fetch only a single row, SQLExec can perform better than the other SQL functions, because only a single row is fetched. If you need to SELECT multiple rows of data, use the CreateSQL or GetSQL functions and the Fetch SQL class method. You can also use ScrollSelect or one of the Select methods on a rowset object to read rows into a (usually hidden) work scroll.

SQLExec statements that result in a database update (specifically, UPDATE, INSERT, and DELETE) can only be issued in the following events:
  • SavePreChange
  • WorkFlow
  • SavePostChange
  • FieldChange 

    Syntax:
    SQLExec("Select sum(posted_total_amt) from PS_LEDGER 
    where deptid between :1 and :2", DEPTID_FROM, DEPTID_TO, &SUM);
      
    :1 is replaced by the value contained in the record field DEPTID_FROM;
    :2 is replaced by the value contained in the record field DEPTID_TO. 
    &SUM will be assigned value of sum(posted_total_amt) selected.
     
    HAPPY LEARNING :)

No comments:

Post a Comment

FieldEdit vs FieldChange Event in PeopleSoft

FieldEdit event is more of a validation event used to validate the value entered in a field and throw error/warning if the value is not wh...