Running Dynamic SQL with SQLEXEC

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:

dynamicSQL(1);

You should get one warning with the value of PSOPRDEFN.OPRDEFNDESC.

If you call it like this:

dynamicSQL(2);

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.