PeopleCode | Options to perform a SQL select (including the dreaded SQLExec)
A. The Poison Chalice
If you’ve ever done a PeopleCode training course, you’ll no doubt have heard the advice to steer clear of the ‘SQLExec’ statement. There are certainly good reasons to follow this advice, but before we explore that in more detail, let’s start with a quick introduction to ‘SQLExec’ (in case you’re one of the two people who’s never heard of it before).
‘SQLExec’ allows the developer to encode any SQL statement directly into a PeopleCode programme and then call the SQL immediately as part of the programme:
SQLExec("select city, country from ps_addresses " |
"where emplid = :1 and address_type = :2",
&emplid_in, &address_type_in, &city_out, &country_out);
The string passed into the ‘SQLExec’ statement can be a select, update, delete, ‘create table’ or just about anything that’s possible in native SQL. For the purposes of the current post, we will confine ourselves to the ‘select’ statement only. It’s important to note that doing a select via ‘SQLExec’ is limited to one row of returned data only. There is no way to carry out multiple fetches from the same SQL ‘cursor’.
Despite this limitation and despite what the training courses might say, most custom built code is littered with ‘SQLExec’ statements. Many developers write their SQL directly in an external editor, and then copy the end result straight into App Designer once they’re happy with the result.
B. So What’s the Problem?
Although I’m not a big stickler for ‘rules’ and the so-called ‘best practice’ when it comes to writing code, I have to confess that there are a few compelling reasons why ‘SQLExec’ should be avoided, at least when it comes to doing a ‘Select’ query. Here are three such objections (numbers 1 and 2 are also mentioned in PeopleBooks):
Objection Number 1. The main reason to avoid ‘SQLExec’ is that the string passed to the statement is considered a ‘black box’ as far as PeopleSoft is concerned. You could pass in any command you want (‘delete from…’) and the system would happily execute the command without question. Using the alternative approaches described below adds in an extra layer of integrity checking that ‘SQLExec’ lacks. As a result, there is less danger of ‘little’ mistakes or of developers possibly sneaking in pieces of code that really shouldn’t be there.
Objection Number 2. Another downside of the ‘SQLExec’ ‘black box’ is that any references to records included within the string will not appear in a ‘Find Definitions Reference’ search in App Designer. If you are trying to do an impact analysis on a particular record, you’ll be completely clueless as to how people have used the record if it happens to have been included in ‘SQLExec’ statements. Your only option would be to resort to the much slower ‘Find In…’ search, which performs a string search across the entire database.
Objection Number 3. Once you’ve created an ‘SQLExec’ statement, there isn’t much else you can do with it. You pass in some data, and you get some data back… end of story. (I guess the simplicity is also why it appeals to many people.) On the other hand, the use of record and rowset objects automatically gives you access to a wide range of delivered methods and properties. This makes the code easier to amend and maintain. There is less of a chance of being forced to rewrite in order to cater for future changes.
After having considered the objections, let’s now turn our attention to some of the alternatives to ‘SQLExec’. In all cases, we will use the same requirement: selecting the city and country for a specific employee from the ADDRESSES table. In addition, we are only interested in the Address Type of ‘MAIL’. The normal SQL to achieve this task would be:
select city, country from ps_addresses where emplid = 'XYZ' and address_type = 'MAIL';
C. SQL Object
The simplest option is to save the select statement into a SQL object in App Designer:
Then, you could call the saved SQL object in PeopleCode as follows:
&emplidSQL = GetSQL(SQL.SAVED_EMPLID_SELECT, "EMPLID_XYZ"); While &emplidSQL.Fetch(&city, &country) MessageBox(0, "", 0, 0, &city | " " | &country); End-While;
However, this approach still doesn’t get around our earlier objections. Any records included in an SQL object won’t appear in a ‘Find Definitions Reference’ search, and there is still potential for a hidden statement to be ‘smuggled’ into the system. However, it’s still better than nothing at all. An SQL object is more visible than an ‘SQLExec’ statement, plus the SQL is now available for any other developer to use, helping to cut down on code duplication.
D. Record Object
The second approach is to create a record object and then populate the record via the ‘SelectbyKeys’ method. Although this is the quickest of the alternative methods, use of a record object is only possible when all the keys can be populated in advance. By definition, the record object – in the singular – can only ever return one record. In a relational database, this will only be possible if all the key values are known in advance.
In the case of our example with the PS_ADDRESSES table, there are three keys to consider: EMPLID, ADDRESS_TYPE and EFFDT. Although we don’t know the Effective Date, PeopleCode supplies a second method – ‘SelectbyKeysEffDt’ – to handle the case of a record being selected on the basis of effective dating.
&emplidRec = CreateRecord(Record.ADDRESSES);
&emplidRec.EMPLID.Value = "EMPLID_XYZ";
&emplidRec.ADDRESS_TYPE.Value = "MAIL";
&emplidRec.SelectbyKeyEffdt(%Date);
MessageBox(0, "", 0, 0, &emplidRec.CITY.Value | " " |
&emplidRec.COUNTRY.Value);
Better still, the use of this record will appear in any ‘Find Definition References’ search in App Designer, making life easier for those performing upgrades or future changes.
E. Rowset Object
The main limitation of using the Record object above is that all the key fields must be known in advance, which in turn implies that only record will ever be returned. In our particular example with the ADDRESSES record, both these conditions happened to be satisfied, so we had no difficulty in using the record object. However, if we didn’t know all the keys in advance (say, for instance, we wanted to select all address types for the Employee ID), or if we wanted to selected multiple rows of data, we could instead resort to a rowset object. This provides an SQL-like way to populate a standalone rowset based on a ‘where’ clause:
&emplidRS = CreateRowset(Record.ADDRESSES);
&row_count = &emplidRS.Fill("where EMPLID = :1 and " |
"%EffDtCheck(addresses fill_ed, fill, %CurrentDateIn)",
"EMPLID_XYZ");
If &row_count > 0 Then
For &i = 1 To &row_count
&emplidRec = &emplidRS.GetRow(&i).ADDRESSES;
MessageBox(0, "", 0, 0, &emplidRec.CITY.Value | " " |
&emplidRec.COUNTRY.Value);
End-For;
End-If;
Note the use of the meta-SQL construct ‘%EffDtCheck’ to restrict the selection to the latest effective dated record. This is a slightly more complicated feature than ‘SelectbyKeysEffDt’, since it requires three parameters: (1) the record to use for the effective date check along with that record’s alias, (2) the alias of the primary record (always ‘fill’ when using a rowset fill method), and (3) the date to use for the effective dated check.
F. SQLExec (dare we suggest it?)
Despite all we’ve said above, I can think of two cases in which ‘SQLExec’ might still be the best option.
The first is when you want to do a one-off, aggregate calculation, such as a ‘select count(*) from rec_name’. There is no easy way to do this using the other approaches described above. Furthermore, since it’s most likely a one-off statement limited to a particular process (say, selecting the count of records in a temporary table), there is little point in setting up a saved SQL object. In this case, a quick ‘SQLExec’ handles the requirement nicely:
SQLExec("select count(*) from temp_table " |
"where process_instance = :1", &prcsinst_in, &rec_count_out);
The second benefit of ‘SQLExec’ concerns those cases where performance is of the utmost importance. ‘SQLExec’ can deliver impressive results, which is not surprising giving you are talking directly to the database in a language it understands, rather than going through an intermediate channel of a PeopleCode programme. However, this argument needs to be countered with the objections mentioned early. Are the speed improvements really worth it? Performance isn’t everything. You also need to factor in such things as code readability, future upgrades and maintenance.
In short, ask yourself if the benefits of ‘SQLExec’ outweigh the costs? In the majority of cases, I would argue “no”. The alternative approaches are still preferable, even if they cannot compete with ‘SQLExec’ in terms of response time.
G. A Quick Experiment
Finally, just to see how much quicker ‘SQLExec’ is in relation to the competition, I decided to carry out an experiment. I ran the following ‘SQLExec’ statement 1000 times:
SQLExec("select FIELDCOUNT from PSRECDEFN " |
"where recname = 'INSTALLATION'", &fieldcount_out);
Then I repeated the test by using a record object, again looping 1000 times:
&tempRec = CreateRecord(Record.PSRECDEFN); &tempRec.RECNAME.Value = "INSTALLATION"; &tempRec.SelectbyKey();
The average response time for three complete runs was:
Using SQLExec: 1.07 seconds
Using Record Object: 3.38 seconds
So yes, in all fairness to ‘SQLExec’, that’s a commendable response time. Even so, I fail to be convinced of ‘SQLExec’ as a solution to every requirement.
See Also:
Tip 034: Loops in PeopleCode