SQLExec

It helps you to execute SQL command from within Peoplecode programs.
It bypasses the component processor and interacts directly with the database.

Syntax
SQLExec({sqlcmd SQL.sqlname}, bindexprs, outputvars)

Limitations:

SQLExec can only select single row of data. If the SELECT statement retrieves more than one row of data, Output variables will hold only the first row of data and the remaining rows will get discarded.

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)

The number of output variables cannot exceed 64.

Examples:

Using Bind Variables in SQLExec
SQLExec(“Select first_name from ps_names where emplid = :1 and name_type = :2”,&emplid, &name_type,&out_first_name)

Using Inline Bind Variables
Inline bind variables are included directly in the SQL string in the form:
:recordname.fieldname
SQLExec("Select sum(posted_total_amt)
from PS_LEDGER
where deptid between :LEDGER.DEPTID_FROM
and :LEDGER.DEPTID_TO", &sum);

Note: You have to change the Inline Bind Variables inside quotes if you are changing the record field name.

Output Variables in SQLExec
SQLExec(“Select first_name from ps_names where emplid = :1 and name_type = :2”,&emplid, &name_type,&out_first_name)

Using Arrays for Bind Variables
By using parameter of type "Array of Any" in place of a list of bind values or in place of a list of fetch result variables, helps you to simplify the statement. This is generally used when you don't know how many values are needed until the code runs.
&Stmt = "INSERT INTO PS_TESTREC (TESTF1, TESTF2, TESTF3, TESTF4, . . . N) VALUES (:1, :2, %DateTimeIn(:3), %TextIn(:4), . . .N)";

&AAny = CreateArrayAny("a", 1, %DateTime, "abcdefg", . . .N);

SQLExec(&Stmt, &AAny);