SQLExec function

Syntax

SQLExec({sqlcmd | SQL.sqlname}, [bindexprs [, outputvars]])

where bindexprs is a list of expressions, one for each :n reference within sqlcmd or the text found in the SQL definition sqlname, in the form: 

inexpr_1 [, inexpr_2]. . .

and where outputvars is a list of variables, record fields, or record object references, one for each column selected by the SQL command, in the form: 

out_1 [, out_2]. . .

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.

Note:

SQLExec opens a new database cursor each time it executes.

Parameters

Parameter Description

sqlcmd | SQL.sqlname

Specify either a String containing the SQL command to be executed or a reference to an existing SQL definition. This string can include bind variables, inline bind variables, and meta-SQL.

bindexprs

A list of expressions, each of which corresponds to a numeric (:n) bind variable reference in the SQL command string. It can also be a reference to a record object or an array of Any containing all the bind values. See Bind Variables in SQLEXEC for more details.

outputvars

A list of PeopleCode variables or record fields to hold the results of a SQL SELECT. There must be one variable for each column specified in the SELECT statement. It can also be a reference to a record object or an Array of Any that contains all the selected values.

Returns

Optionally returns a Boolean value indicating whether the function executed successfully.

Note:

Not returning a row is not considered an error. If this is a concern, consider using the %SqlRows system variable after your call to SQLExec.

Example

The following example, illustrates a SELECT statement in a SQLExec:

SQLExec("SELECT COUNT(*) FROM PS_AE_STMT_TBL WHERE AE_PRODUCT = :1 AND AE_APPL_ID =
 :2 AND AE_ADJUST_STATUS = 'A' ", AE_APPL_TBL.AE_PRODUCT, AE_APPL_TBL.AE_APPL_ID,
 AE_ADJ_AUTO_CNT);

Note the use of bind variables, where :1 and :2 correspond to AE_APPL_TBL.AE_PRODUCT and AE_APPL_TBL.AE_APPL_ID. AE_ADJ_AUTO_CNT is an output field to hold the result returned by the SELECT.

The next example is also a straightforward SELECT statement, but one which uses the %datein meta-SQL function, which expands to appropriate platform-specific SQL for the :5 bind variable:

 SQLExec("SELECT 'X', AE_STMT_SEG FROM PS_AE_STMT_B_TBL where AE_PRODUCT = :1 AND
 AE_APPL_ID = :2 AND AE_SECTION = :3 AND DB_PLATFORM = :4 AND EFFDT = %datein(:5)
 AND AE_STEP = :6 AND AE_STMT_TYPE = :7 AND AE_SEQ_NUM = :8", AE_STMT_TBL.AE_
PRODUCT, AE_STMT_TBL.AE_APPL_ID, AE_STMT_TBL.AE_SECTION, AE_STMT_TBL.DB_PLATFORM,
 AE_STMT_TBL.EFFDT, AE_STMT_TBL.AE_STEP, AE_STMT_TBL.AE_STMT_TYPE, &SEG, &EXIST,
 &STMT_SEG);

This last example (in SavePreChange PeopleCode) passes an INSERT INTO statement in the SQL command string. Note the use of a date string this time in the %datein meta-SQL, instead of a bind variable:

SQLExec("INSERT INTO PS_AE_SECTION_TBL ( AE_PRODUCT, AE_APPL_ID, AE_SECTION, DB_
PLATFORM, EFFDT, EFF_STATUS, DESCR, AE_STMT_CHUNK_SIZE, AE_AUTO_COMMIT, AE_
SECTION_TYPE ) VALUES ( :1, :2, :3, :4, %DATEIN('1900-01-01'), 'A', ' ', 200,
 'N', 'P' )", AE_APPL_TBL.AE_PRODUCT, AE_APPL_TBL.AE_APPL_ID, AE_SECTION, DB_
PLATFORM);

In the following example, a SQLExec statement is used to select into a record object. 

Local Record &DST; 
&DST = CreateRecord(RECORD.DST_CODE_TBL); 
&DST.SETID.Value = GetSetId(FIELD.BUSINESS_UNIT, DRAFT_BU, 
RECORD.DST_CODE_TYPE, ""); 
&DST.DST_ID.Value = DST_ID_AR; 
SQLExec("%SelectByKeyEffDt(:1,:2)", &DST, %Date, &DST); 
/* do further processing using record methods and properties */

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.

Note:

The PeopleSoft record name specified in the SQL SELECT statement must be in uppercase.

Limitations of SQLExec UPDATE, DELETE, and INSERT Statements

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

Remember that SQLExec UPDATEs, INSERTs, and DELETEs go directly to the database server, not to the Component Processor (although SQLExec can look at data in the buffer using bind variables included in the SQL string). If a SQLExec assumes that the database has been updated based on changes made in the component, that SQLExec can be issued only in the SavePostChange event, because before SavePostChange none of the changes made to page data has actually been written back to the database.

Limitation of SQLExec Passing Value in Application Package Instance Variable

Application package instance variables cannot be passed as reference variables. Therefore, when you use SQLExec to retrieve a value and return it in a given instance variable, it fails, but the error is not thrown at compile time. Instead, an error is thrown at runtime.

As a workaround, you should save the value to a local variable and then set the instance variable in a subsequent statement. The following sample code uses the workaround:

...
instance integer &iImageMaxSize;
...
local integer &tempImageMaxSize;
SQLExec("SELECT UPLOAD_PHOTO_SIZE FROM PS_INSTALLATION_HR", &tempImageMaxSize);
&iImageMaxSize = &tempImageMaxSize;

Setting Data Fields to Null

SQLExec does not set Component Processor data buffer fields to NULL after a row not found fetching error. However, it does set fields that aren’t part of the Component Processor data buffers to NULL. Work record fields are also reset to NULL.

Using Meta-SQL in SQLExec

Different DBMS platforms have slightly different formats for dates, times, and date/times; and PeopleSoft has its own format for these data types as well. Normally the Component Processor performs any necessary conversions when platform-specific data types are read from the database into the buffer or written from the buffer back to the database.

When a SQLExec statement is executed, these automatic conversions do not take place. Instead, you need to use meta-SQL functions inside the SQL command string to perform the conversions. The basic types of meta-SQL functions are:

  • General functions that expand at runtime to give you lists of fields, key fields, record fields, and so on. %InsertSelect or %KeyEqual are typical examples.

  • In functions that expand at runtime into platform-specific SQL within the WHERE clause of a SELECT or UPDATE statement or in an INSERT statement. %DateIn is a typical example.

  • Out functions that expand at runtime into platform-specific SQL in the main clause of SELECT statement. %DateOut is a typical example.

Following is an example of a SQL SELECT using both and "in" and "out" metastring:

select emplid, %dateout(effdt) from PS_CAR_ALLOC a where car_id = '" |⇒
 &REGISTRATION_NO | "' and plan_type = '" | &PLAN_TYPE | "' and a.effdt = ⇒
(select max (b.effdt) from PS_CAR_ALLOC b where a.emplid=b.emplid and b.effdt <=⇒
 %currentdatein) and start_dt <= %currentdatein and (end_dt is null or end_dt >=⇒
 %currentdatein)";

See Understanding Meta-SQL.

Bind Variables in SQLExec

Bind variables are references within the sqlcmd string to record fields listed in bindvars. Within the string, the bind variables are integers preceded by colons: 

 :1, :2,. . .

The integers need not in numerical order. Each of these :n integers represents a field specifier in the bindvars list, so that :1 refers to the first field reference in bindvars, :2 refers to the second field reference, and so on.

For example, in the following statement:

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.

Note the following points:

  • Bind variables can be used to refer to long character (longchar) fields. Long character fields are represented in PeopleCode as strings. You should use %TextIn() meta-SQL to ensure these fields are represented correctly on all database platforms.

  • Bind variables can be passed as parameters to meta-SQL functions, for example:

    SQLExec(". . .%datein(:1). . .", START_DT, &RESULT)

  • If a bind variable :n is a Date field that contains a null value, SQLExec replaces all occurrences of ":n" located before the first WHERE clause with "NULL" and all occurrences of "= :n" located after the first WHERE to "IS NULL".

Inline Bind Variables in SQLExec

Inline bind variables are included directly in the SQL string in the form:

:recordname.fieldname

The following example shows the same SQLExec statement with standard bind variables, then with inline bind variables:

Rem without Inline Bind Variables;
SQLExec("Select sum(posted_total_amt) 
   from PS_LEDGER 
   where deptid between  :1 and :2", deptid_from, deptid_to, &sum); 
Rem with Inline Bind Variables;
SQLExec("Select sum(posted_total_amt) 
   from PS_LEDGER 
   where deptid between  :LEDGER.DEPTID_FROM 
   and :LEDGER.DEPTID_TO",  &sum);

Inline bind variables, like all field and record references enclosed in strings, are considered by PeopleTools as a "black box". If you rename records and fields, PeopleTools does not update record and field names that are enclosed in strings as inline bind variables. For this reason, you should use standard bind variable in preference to inline bind variables wherever standard bind variables are available (as they are in SQLExec).

Prior to PeopleTools 8.0, PeopleCode replaced runtime parameter markers in SQL strings with the associated literal values. For databases that offer SQL statement caching, a match was never found in the cache so the SQL had to be re-parsed and re-assigned a query path. However, with PeopleTools 8.0, PeopleCode passes in bind variable parameter markers. For databases with SQL caching, this can offer significant performance improvements.

If you use inline bind variables, they will still be passed as literals to the database. However, if you convert them to bind variables, you may see significant performance improvements.

Output Variables in SQLExec

If you use SQLExec to Select a row of data, you must place the data into variables or record fields so that it can be processed. You list these variables or fields, separated by commas in the output part of the statement following the bindvars list. Supply one variable or field for each column in the row of data retrieved by SQLExec. They must be listed in the same order in which the columns will be selected.

The number of output variables cannot exceed 64.

Selecting Columns with Leading Spaces

When you execute a select SQL statement that returns data from a Character type column that has leading spaces, leading spaces are removed from the resulting text. If the column is a Long Character type, leading spaces are not removed.

Using Arrays for Bind Variables

You can now use a parameter of type Array of Any in place of a list of bind values or in place of a list of fetch result variables. This is generally used when you do not know how many values are needed until the code runs.

For example, suppose that you had some PeopleCode that dynamically (that is, at runtime) generated the following SQL statement:

&Stmt = "INSERT INTO PS_TESTREC (TESTF1, TESTF2, TESTF3, TESTF4, . . . N) VALUES (:
1, :2, %DateTimeIn(:3), %TextIn(:4), . . .N)";

Suppose you have placed the values to be inserted into an Array of Any, say &AAny:

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

You can execute the insert by:

SQLExec(&Stmt, &AAny);

Because the Array of Any promotes to absorb any remaining select columns, it must be the last parameter for the SQL object Fetch method or (for results) SQLExec. For binding, it must be the only bind parameter, as it is expected to supply all the bind values needed.

SQLExec Maintenance Issues

SQLExec statements are powerful, but they can be difficult to upgrade and maintain. If you use a SQL string passed in the command, it’s considered a "black box" by PeopleCode. If field names or table names change during an upgrade, table and field references within the SQL string are not updated automatically. For these reasons, you should use a SQL definition and the meta-SQL statements provided in PeopleTools 8.0, instead of typing in a SQL string.

Generally, you should use SQLExec only when you must interact directly with the database server and none of the ScrollSelect functions, or record class methods (which are somewhat easier to maintain) will serve your purpose effectively.

Be Careful How You Use It

SQLExec performs any SQL statement the current Access ID has database privileges to perform. This normally includes SELECT, INSERT, UPDATE, and DELETE statements against application data tables. However, you can set up users to use Access IDs with more privileges (typically, AccessIDs have full database administrator authority). In such cases, the user could alter the structure of tables using SQLExec, or even drop the database.

WARNING:

The PeopleSoft application will not stop the end user from doing anything that the Access ID has privileges to do on the database server, so be very careful what you write in a SQLExec statement.

Related Topics