Running Dynamic SQL with SQLEXEC
By Chris Malek | Sat, Jun 11, 2011
If you look at the SQLEXEC Documentation in PeopleBooks you will see a reference that you can actually run dynamic sql with the SQLEXEC. This allows you to pull a SQL statement from a setup table or perhaps build one at runtime and then execute it against the database with SQLEXEC. The SQLEXEC PeopleCode function will actually take an “Array of Any” for both the input binds and output parameters.
So if we take this dynamicSQL Function:
Function dynamicSql(&numberOfColums As integer) Local string &sqlToRun; Evaluate &numberOfColums When = 1 &sqlToRun = "SELECT OPRDEFNDESC FROM PSOPRDEFN WHERE OPRID = :1"; Break; When = 2 &sqlToRun = "SELECT OPRDEFNDESC, EMPLID FROM PSOPRDEFN WHERE OPRID = :1"; Break; When = 3 &sqlToRun = "SELECT OPRDEFNDESC, EMPLID, ACCTLOCK FROM PSOPRDEFN WHERE OPRID = :1"; Break; End-Evaluate; &inArray = CreateArrayAny(); &inArray.PUSH(%OperatorId); &returnArray = CreateArrayAny(); SQLExec(&sqlToRun, &inArray, &returnArray); If All(&returnArray) Then Local integer &I; For &I = 1 To &returnArray.LEN Warning &returnArray [&I]; End-For; End-If; End-Function;
This function is only useful for this tutorial. It will take an integer parameter between 1 and 3 and pull that many different columns off the PSOPRDEFN table for the current user logged in. It will then issue a warning with the value for each of those values returned.
If you call the function like this:
You should get one warning with the value of PSOPRDEFN.OPRDEFNDESC.
If you call it like this:
You should get two warnings.
Real World Use case for DynamicSQL
Where would you ever use this? A great use case example of where this can be used is in our My Alerts Module. In this module we allow a SQL to be defined in a setup table. This SQL can have any number of return values that can be substituted at runtime to display to the user.
So we could have an Alert setup that runs this SQL statement:
SELECT A.EMPLID, A.NAME FROM PS_PERSON_NAME WHERE EMPLID = %EMPLOYEEID;
Then we could display a message to the current user logged in that would take the A.EMPLID and A.NAME values out of the sqlexec return array and display a message like:
Hello John Doe, Did you know your emplid is HC223453?
Where “John Doe” and HC223453 came from the database at run time.
If you are going to try this you need to wrap it in a Try/Catch statement.
Chris Malek is a PeopleTools® Technical Consultant with two decades of experience working on PeopleSoft enterprise software projects. He is available for consulting engagements.About Chris Work with Chris
PeopleSoft Simple Web Services (SWS)
Do you need a quick way to create web services?
Introducing a small but powerful PeopleSoft bolt-on that makes web services very easy. If you have a SQL statement, you can turn that into a web service in PeopleSoft in a few minutes.
Integration Broker - The Missing Manual
I am in the process of writing a book called "Integration Broker - The Missing Manual" that you can read online.