Introduction

PeopleCode has two little gem functions that expand binds: ExpandBindVar and ExpandSqlBinds.

At first glance, these two functions look similar.  A cursory glance at PeopleBooks show that they both expand binds in strings.  Even the parameter list is the same (not).   But how they are used is two very different situations and are not interchangeable.

ExpandBindVar Vs ExpandSqlBinds

ExpandBindVar() is a simple function that just inserts the values of component fields into a string. 

ExpandSqlBinds() is for SQL Statements.  It replaces one or more bind variables with the value of the corresponding parameter.   If the value past is a number, then it is just passed as a value of the variable.   If the value is not a number, it automatically wraps that value in single quotes.

In the end, both functions create a new string by injecting values into a string using bind variables of some sort.   It’s just how they do it which is very different.

PeopleBooks 8.60 Reference

ExpandBindVar

ExpandBindVar(str)

The ExpandBindVar simply takes a string that has record.field notation with a bind symbol (“:”) and returns a string with the value of the record fields replaced in the original string.  It does not do any handling for field type (Char/Num, etc.) nor does it inject quotes.

This function is just a convenient tool to create user readable strings for your page using preformatted statements and field references.

local string &Str_Result = ExpandBindVar(":X_PT3_E033_WRK.NAME went for a walk in the park on :X_PT3_E033_WRK.ASOFDATE on day :X_PT3_E033_WRK.FTE of his weekend.");

This statement injects the values of three different fields into the string with a result looking something like this:

Douglas Lewis went for a walk in the park on 2023-02-12 on day 1 of his weekend.

This is how this ExpandBindVar() statement works:

• X_PT3_E033_WRK.NAME is a character field containing the string “Douglas Lewis”
• X_PT3_E033_WRK.ASOFDATE is a date field with the value “2023-02-12”
• X_PT3_E033_WRK.FTE is a numeric field containing the value 1
• There is a colon preceding each of these fields in the string.
• All three of these fields must be available in the component buffer at execution time.

This looks to be an older style PeopleCode function predating PeopleTools Version 8.    Notice that the notation is the “RECORD.FIELD” from the component buffer.  This function cannot handle string variables or objects with similar values.

Also notice that the ExpandBindVar() function only has one parameter, that is a string.  There is no matchup between the bind variable in the string and the corresponding field in a parameter list).

A good implementation of this function is to place the string in the message catalog so the message can be changed if needed without migrating and testing new code.

ExpandBindVar() will generate errors if you attempt to binds such as :1, :2, :3, etc and try to match them up with a parameter list.

ExpandSqlBinds

ExpandSqlBinds (string [,param1] [,param2] [,param3] [,etc])

This ExpandSqlBinds() function takes a string with bind markers and injects the value of the corresponding parameter into the string at that position.  If the value is declared to be a number or integer, then the value is injected as is.  Anything else is quoted before injection.

This function is expecting a SQL statement.  However, the result is not executed against the database during this function’s execution.  So, any string can be used.

Local string &Result = ExpandSqlBinds("UPDATE %Table(EMPLOYEES) SET COUNTRY_NM_FORMAT = :1, FTE = :2   WHERE EMPLID = :3 AND EFFDT < %DateIn(:4)", &Rec_X_PT3_E033_WRK.COUNTRY_NM_FORMAT.Value, &Int_FTE, &Str_Emplid, &Rec_X_PT3_E033_WRK.ASOFDATE.Value, &Rec_X_PT3_E033_WRK.RECNAME.Value);

This statement injects the values into four bind variables by matching the bind number with the corresponding parameter in the list with a result looking something like this:

UPDATE %Table(EMPLOYEES)   SET COUNTRY_NM_FORMAT = '001', FTE = 1  WHERE EMPLID = 'KU0001' AND EFFDT < %DateIn('2023-02-12')

Here is how this ExpandSqlBinds() statement works:

• The value of the &Rec_X_PT3_E033_WRK.COUNTRY_NM_FORMAT object is quoted and injected at the :1 position: ‘001’
• The &Int_FTE declared as an integer.  So, the value is not quoted where it is injected into the string a the :2 position: 1
• The &Str_Emplid variable was declared as a string.  It’s value is quoted and injected at the :3 position:  ‘KU0001’
• The &Rec_X_PT3_E033_WRK.ASOFDATE object is type Date.  Its value is quoted and injected into the string at :4 position: ‘2023-02-12’

Notice that this function can take the parameters in multiple different forms: Variables, appropriate data Objects or hard coded strings.

A great use for this statement is for those occasions where we need to generate the SQL string based on something in the environment before execution.   We can generate the SQL with the appropriate values in the context of transaction or situation of that current session.