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 :)