ExpandSqlBinds function

Syntax

ExpandSqlBinds(string)

Description

Prior to PeopleTools 8.0, the PeopleCode replaced runtime parameter markers in SQL strings with the associated literal values. For databases that offer SQL statement caching, a match was never found in the cache so the SQL had to be re-parsed and re-assigned a query path.

To process skipped parameter markers, each parameter marker is assigned a unique number. This doesn’t change the values associated with the parameter markers.

However, some SQL statements can’t contain parameter markers because of database compatibility.

To process these exceptions, use the ExpandSqlBinds function. This function does the bind variable reference expansion, and can be used within a SQLExec statement or on its own.

You should use ExpandSQLBinds only for those parts of the string that you want to put literal values into. The following code shows how to use ExpandSQLBinds with %Table:

SQLExec(ExpandSqlBinds("Insert....  Select A.Field, :1, :2 from ", "01", "02") |
 "%table(:1)", Record.MASTER_ITEM_TBL);

Parameters

Parameter	Description
string	Specify the string you want to do the bind variable reference expansion on.

Returns

A string.

Example

The following example shows both the original string and what it expands to.

&NUM = 1; 
&STRING = "My String"; 
&STR2 = ExpandSqlBinds("This :2 is an expanded string(:1)", &STRING, &NUM);

The previous code produces the following value for &STR2:

This 1 is an expanded string(My String)

If you’re having problems with an old SQL statement binds, you can use ExpandSqlBinds with it. For example, if your SQLExec is this:

SQLExec("String with concatenated bindrefs ‘M’:2, ‘M’:1", &VAR1, &VAR2),
 &FETCHRESULT1, &FETCHRESULT2);

you can make it work by expanding it as follows:

SQLExec(ExpandSqlBinds("String with concatenated bindrefs ‘M’:2, ‘M’:1", &VAR1,
 &VAR2), &FETCHRESULT1, &FETCHRESULT2);