SQLExec in PeopleCode - Syntax and Examples

What is SQLExec in PeopleCode?

SQLExec is a PeopleCode function that allows you to execute SQL statements directly against the database. It is often used to perform database operations like SELECT, INSERT, UPDATE, or DELETE without relying on component buffers. SQLExec is particularly useful for fetching single-row data or performing database updates in scenarios where component-based processing isn’t feasible.

What is the Syntax SQLExec?

SQLExec() uses the following syntax.

SQLExec({sqlcmd | SQL.sqlname}, [bindexprs [, outputvars]])
 
SQLExec("SELECT DESCR FROM PS_RX_TOOLS_TBL WHERE RX_TOOLID = :1", &sToolId, &sToolName);

SQLExec Parameters
The parameters used by SQLExec are explained below.

Parameter	Purpose	Example
sqlcmd	The actual SQL to be executed.	`"SELECT DESCR FROM PS_RX_TOOLS_TBL WHERE RX_TOOLID = :1"`
SQL.sqlname	The name of an SQL object that has the SQL to be executed.	`SQL.RX_GET_TOOL_NAME`
bindexprs	List of expressions to be assigned to the Bind variables like:1, :2, etc.	`&sToolId`
outputvars	List of variables, record fields, or record object references, to match what is selected by the SQL.	`&sToolName`

SQLExec Return Value

The Return value is optional for SQLExec().
It returns a Boolean value TRUE if the function was executed successfully and FALSE otherwise.
It does NOT return the number of rows affected by the SQL.

%SqlRows system variable

To determine the number of rows affected by the most recent UPDATE, DELETE, or INSERT executed through SQLExec(), use the %SqlRows system variable. When this is used with SELECT, it only returns 1 or 0 – remember SQLExec discards all except the first row during SELECTs.

SQLExec("DELETE FROM PS_RX_TOOLS_TBL WHERE RX_TOOLID = :1", &sToolId);
MessageBox(0, "", 0, 0, "Rows Deleted: " | %SqlRows);

SQLExec PeopleCode Examples

Next, we will look at some examples of how SQLExec can be used in PeopleCode.

Insert using SQLExec

SQLExec("INSERT INTO PS_RX_TOOLS_TBL (RX_TOOLID, EFFDT, DESCR) VALUES (:1, %DateIn(:2), :3) ", &sToolID, %Date, "Test");
 
SQLExec("INSERT INTO %table(:1) (RX_TOOLID, EFFDT, DESCR) VALUES (:2, %DateIn(:3), :4) ", Record.RX_TOOLS_TBL, &sToolID, %Date, "Test");

Update using SQLExec

SQLExec("UPDATE PS_RX_TOOLS_TBL SET DESCR =:1 WHERE RX_TOOLID= :2", &sToolsName, &sToolID);

Delete using SQLExec

SQLExec("DELETE PS_RX_TOOLS_TBL WHERE RX_TOOLID= :1", &sToolID);

How to handle Dates in SQLExec?

These Meta-SQL variables expand into platform-specific SQL syntax for the dates.
Use %DateIn in the WHERE clause, UPDATE, INSERT etc.
Use %DateOut in the Select clause.

SQLExec("INSERT INTO PS_RX_TOOLS_TBL (RX_TOOLID, EFFDT, DESCR) VALUES (:1, %datein(:2), :3) ", &sToolID, &sDate, "Test");
 
SQLExec("SELECT %DATEOUT(EFFDT) FROM PS_RX_TOOLS_TBL WHERE RX_TOOLID = :1", &sToolID, &sDate);

How to perform a Commit using SQLExec?

If a COMMIT has to be issued for whatever reason, this is how it can be done in SQLExec.

SQLExec("COMMIT");

How to handle Long character fields in SQLExec?

When inserting text into LongChar fields, use the %textin() meta SQL.

SQLExec("INSERT INTO PS_RX_TOOLS_TBL (RX_TOOLID, EFFDT, DESCR, DESCRLONG2) VALUES (:1, %datein(:2), :3, %textin(:4)) ", &sToolID, %Date, "Test", &sLongText);

How to use SQL Objects in SQLExec?

If the SQL used is complex and long, it can be better managed using a SQL Object.
First, create the SQL Object and add the SQL.

It can then be used in the SQLExec.

SQL.RX_UPDATE_TOOLNAME
UPDATE PS_RX_TOOLS_TBL 
  SET DESCR =:1 
 WHERE RX_TOOLID= :2
 
 
SQLExec(SQL.RX_UPDATE_TOOLNAME, &sToolsName, &sToolID);
<pre>
 
This will have the same effect as the following SQLExec, which uses a SQL string.
 
<pre lang="peoplecode">
SQLExec("UPDATE PS_RX_TOOLS_TBL SET DESCR =:1 WHERE RX_TOOLID= :2", &sToolsName, &sToolID);

Limitations of SQLExec

Now let’s look at some limitations.
For starters, SQLExec can only select a single row of data.
If your SQL happens to select more than one row, all except the first row will be discarded.

SQLExec that results in database updates can only be fired from the following PeopleCode Events.

SavePreChange
WorkFlow
SavePostChange
FieldChange

When working inside Classes, you need to be mindful of not selecting a value and directly assigning it to an instance variable within SQLExec. This is not supported as instance variables cannot be passed as reference variables. You can use a temporary variable in SQLExec and then assign this variable to the instance variable as a workaround.

Instance String &iToolName;
 
Local string &sToolName;
 
SQLExec("SELECT DESCR FROM PS_RX_TOOLS_TBL WHERE RX_TOOLID = :1", &sToolId, &sToolName);
&iToolName = &sToolName;

Difference between SQLExec and CreateSQL

Feature	SQLExec	CreateSQL
Purpose	Executes SQL statements directly against the database.	Instantiates a SQL object from the SQL class.
Row Selection	Single row. `SQLExec("SELECT DESCR FROM PS_RX_TOOLS_TBL WHERE RX_TOOLID = :1", &sToolId, &sToolName);`	Multi-row (with Fetch) `&sqlTools = CreateSQL("%SelectAll(:1) where RX_TOOLID = :2", &rToolsRec, &sToolId); While &sqlTools.Fetch(&rToolsRec); /* do something with the values fetched */ End-While;`
DML	Multi-row effect using a single SQL statement. `SQLExec("INSERT INTO PS_RX_TOOLS_TBL (RX_TOOLID, EFFDT, DESCR) VALUES (:1, :2, :3) ", &sToolID, %Date, "Test");`	Multi-row (with Execute). `&SQL = CreateSQL("%Insert(:1)"); While /* Set all the field values of PS_RX_TOOLS_TBL. */ &SQL.Execute(&rToolsRec); End-While; &SQL.Close();`