Using SQLExec and Record Objects

Here is a quick code snippet to show how to use SQLExec to pull data into a record object not using the key fields of the record. It is not clear in PeopleBooks how to use Record objects with SQLExec. I use record objects all over the place in my PeopleCode. Here is a quick way to do a select to populate a record object NOT using key fields.

In this example, we will populate a record object for PSOPRDEFN for the user profile that has the employee ID of “EE2343.” The only key field on PSORPDEFN is OPRID which we are trying to find.

local string &emplid = "EE2343";
Local Record &recO = CreateRecord(Record.PSOPRDEFN);

SQLExec("%selectall(:1) WHERE EMPLID = :2", &recO, &emplid, &recO);

The &recO record is both a parameter and a return object. We pass the record object as a parameter to SQLExec, which expands the record definition and uses it to create the SQL statement. The :1 and :2 are placeholders for the parameters that will be passed in, which are the record object and the employee ID, respectively. The result of the SQL query will populate the &recO record object with all the fields from the PSOPRDEFN record that match the EMPLID.

Here another one to grab the preferred email address for an employee. The record object is EMAIL_ADDRESSES and the key field is EMPLID. We will use the pref_email_flag to filter the results.

Local Record &recEmail = CreateRecord(Record.EMAIL_ADDRESSES);
SQLExec("%selectall(:1) WHERE EMPLID = :2 and PREF_EMAIL_FLAG = 'Y'", &recEmail, &emplid, &recEmail);

Now you can access all the fields in the record like &recEmail.EMAIL_ADDR.value

The great thing about this is that it automatically pulls all the fields. If you later add or remove fields from the record you are selecting from you do not have to touch this code because “%selectall” will dynamically figure out the fields based on the record definition. You will not need to declare variables for each field in the record, which is a big time saver and reduces the chance of errors and future maintenance.

If you are just selecting by the keys use either the record SelectByKey() or SelectByKeyEffdt(%date) methods. If you are not using the keys, then use SQLExec with the record object as shown above.