Learn about placeholders for replaceable parameters
- Last UpdatedAug 19, 2025
- 5 minute read
- PI System
- Interfaces
Placeholders define the values to be assigned to replaceable parameters in SQL queries. Replaceable parameters in queries are specified using question marks. The value of a placeholder is determined when the query is executed.
Note: Placeholders (question marks) can only appear in positions occupied by column names. For instance, in SELECT lists or in the WHERE clauses. Placeholders are not allowed as a substitute for table names. The reason is that the binding fails (ODBC does not know the data type "table"); moreover, the interface cannot log an error message specific to attempt to use a placeholder for a table name because interface does not do any SQL statement analysis of this type before invoking the SELECT.
To work around this issue, use a stored procedure and pass in the table name as a param to the stored procedure. Define the table name as a variable within the stored procedure and use that variable in the SQL query for the table name.
Specify placeholders in a tag's ExDesc attribute, separated by whitespace, for example:
P1=TS P2=SS_I P3=AT.TAG
Note: The placeholders are numbered and must be specified in ascending order.
The next example illustrates how data from events in the archive are transferred through placeholders to an RDBMS table for various PI data types. The example is based on the following query:
/SQL="INSERT INTO table1 (name, instant, column_num, status_num, status)
VALUES (?,?,?,?,?);"P1=AT.TAG P2=TS P3=VL P4=SS_I P5=SS_C
Values are assigned in order: the first replaceable parameter is assigned the value of the placeholder assigned to P1, the second replaceable parameter is assigned the value of the placeholder assigned to P2, and so on.
Specify placeholders in the tag that executes the query, as follows:
|
Data handling method |
Tag that defines placeholders |
|---|---|
|
Single |
Single tag |
|
Distribution |
Distributor tag |
|
Group |
Primary tag |
|
RxC |
Primary tag |
For string constant placeholders, specify the argument in double quotes; for example:
P1="any_string"
If the same placeholder definition is used multiple times in a query, it is possible to shorten the definition string using a back-reference; for example: P5=P3
To derive data from a tag other than the tag associated with the query, you can qualify a placeholder with the name of the tag. For example, to derive the value from another tag, precede the VL placeholder with the name of the tag in single quotes as follows:
P1='sinusoid'/VL
The following table shows how the interface evaluates placeholders for output tags. Values from digital PI tags must correspond to string columns in the RDBMS.
|
PI point data type |
Event state |
Placeholders |
||
|---|---|---|---|---|
|
VL |
SS_I |
SS_C |
||
|
Float, Timestamp, Integer, String |
No error |
<Value> |
0 |
"O.K." |
|
Error |
< Previous Value> |
<Digital State String> |
<Digital State String> |
|
|
Digital |
Digital state is in the range of success statuses |
<Digital State String> |
0 |
"O.K." |
|
Digital state is in the range of "bad" statuses |
<Digital State String> |
1 |
"Bad Value" |
|
To configure the value to be used when no value exists at the specified timestamp, set the mode parameter as follows:
P1='sinusoid'/VL('timestamp',mode)
For output tags, the timestamp is usually the time from the source tag event. If a value exists at the specified timestamp, the value is used. Valid values for the mode parameter are as follows:
|
Mode |
Value used |
|---|---|
|
Previous |
The first value before the specified time |
|
Next |
The first value after the specified time. Specified time cannot exceed snapshot time. |
|
Interpolated |
Interpolated value at the specified time |
The following tables list the placeholders supported by the interface. For placeholders that accept a tag name argument, specify the tag name in single quotes preceding the placeholder, as shown in the following table.
|
Placeholder keywords for ExDesc (Pn=) |
Description |
|---|---|
|
ANN_C and 'tagname' /ANN_C |
Annotation number (varchar) Max. 1023 characters |
|
ANN_I and 'tagname' /ANN_I |
Annotation number (integer) |
|
ANN_R and 'tagname' /ANN_R |
Annotation number (float) |
|
ANN_TS and 'tagname' /ANN_TS |
Annotation timestamp |
|
QE |
Questionable bit (number). Non-zero when set. |
|
LET |
Last execution time Input: Time when the query finished execution. The time difference between LST and LET can be significant in the case of a long-running query. |
|
LST |
Last scan time |
|
SS_C and 'tagname' /SS_C |
Current status digital code string. For valid events, SS_C is set to "O.K." Max. 79 characters |
|
SS_I and 'tagname' /SS_I |
Current status integer representation |
|
ST |
Scan time Input: Start of new scan for a scan class Output: Time of output event |
|
TE |
Timestamp end. Used in recovery mode |
|
TS and 'tagname' /TS |
Timestamp. For input tags, the timestamp is taken from the interface's internal snapshot |
|
VL and 'tagname' /VL ('timestamp', mode ) |
Current value Digital tags: max 79 characters. String tags: max 977 characters The mode parameter indicates the value to be used when no value exists at the specified timestamp. Valid values for mode:
|
The following placeholders return information about points and data from PI point attributes.
|
Placeholder keywords for ExDesc (Pn=) |
Description |
|---|---|
|
AT.TAG |
Tag name of the current tag Max. 1023 characters |
|
AT.DESCRIPTOR |
Descriptor of the current tag Max. 1023 characters |
|
AT.EXDESC |
Extended descriptor of the current tag Max. 1023 characters |
|
AT.ENGUNITS |
Engineering units for the current tag Max. 13 characters |
|
AT.ZERO |
Zero of the current tag |
|
AT.SPAN |
Span of the current tag |
|
AT.TYPICALVALUE |
TypicalValue of the current tag |
|
AT.DIGSTARTCODE |
Digital start code of the current tag |
|
AT.DIGNUMBER |
Number of digital states of the current tag |
|
AT.POINTTYPE |
Point type of the current tag Max. 1 character |
|
AT.POINTSOURCE |
Point source of the current tag Max. 1023 characters |
|
AT.LOCATION1 |
Location1 of the current tag |
|
AT.LOCATION2 |
Location2 of the current tag |
|
AT.LOCATION3 |
Location3 of the current tag |
|
AT.LOCATION4 |
Location4 of the current tag |
|
AT.LOCATION5 |
Location5 of the current tag |
|
AT.SQUAREROOT |
SquareRoot of the current tag |
|
AT.SCAN |
Scan flag of the current tag |
|
AT.EXCDEV |
Exception deviation of the current tag |
|
AT.EXCMIN |
Exception minimum time of the current tag |
|
AT.EXCMAX |
Exception maximum time of the current tag |
|
AT.ARCHIVING |
Archiving flag of the current tag |
|
AT.COMPRESSING |
Compression flag of the current tag |
|
AT.FILTERCODE |
Filter code of the current tag |
|
AT.COMPDEV |
Compression deviation of the current tag |
|
AT.COMPMIN |
Compression minimum time of the current tag |
|
AT.COMPMAX |
Compression maximum of the current tag |
|
AT.TOTALCODE |
Total code of the current tag |
|
AT.CONVERS |
Conversion factor of the current tag |
|
AT.CREATIONDATE |
Creation date of the current tag |
|
AT.CHANGEDATE |
Change date of the current tag |
|
AT.CREATOR |
Creator of the current tag, specified as the PI user's numeric ID. Max. 8 characters |
|
AT.CHANGER |
Changer of the current tag. REM: See also AT.CREATOR Max. 8 characters |
|
AT.RECORDTYPE |
Record type of the current tag |
|
AT.POINTNUMBER |
Point ID of the current tag |
|
AT.DISPLAYDIGITS |
Display digits after decimal point of the current tag |
|
AT.SOURCETAG |
Source tag of the current tag Max. 1023 characters |
|
AT.INSTRUMENTTAG |
Instrument tag of the current tag Max. 1023 characters |
|
AT.USERINT1,2 |
UserInt1, UserInt2 |
|
AT.USERREAL1,2 |
UserReal1, UserReal2 |
When populating values configured using placeholders, the interface uses the data types listed in the following table. The ODBC driver attempts to bind using the most compatible data type. If the binding does not succeed, the interface tries again using the second- and third-choice data types, if indicated.
Point attributes placeholders
|
Attribute |
SQL Data Type |
|---|---|
|
AT.ATTRIBUTE AT.CHANGER AT.CREATOR AT.DESCRIPTOR AT.ENGUNITS AT.EXDESC AT.INSTRUMENTTAG AT.NEWVALUE AT.OLDVALUE AT.POINTSOURCE AT.POINTTYPE AT.SOURCETAG AT.TAG "any_string" |
SQL_VARCHAR |
|
AT.DIGSTARTCODE AT.DIGNUMBER AT.LOCATION1 AT.LOCATION2 AT.LOCATION3 AT_LOCATION4 AT.LOCATION5 AT.SQUAREROOT AT.SCAN AT.EXCMIN AT.EXCMAX AT.ARCHIVING AT.COMPRESSING AT.FILTERCODE AT.RES AT.COMPMIN AT.COMPMAX AT.TOTALCODE AT.RECORDTYPE AT.POINTNUMBER AT.DISPLAYDIGITS AT.USERINT1 AT.USERINT2 |
First choice: SQL_INTEGER Second choice: SQL_FLOAT Third choice: SQL_DOUBLE |
Snapshot Placeholders
|
Placeholder |
SQL (ODBC) Data Type |
||
|---|---|---|---|
|
First choice |
Second choice |
||
|
ST,TS, LET,LST, ANN_TS |
SQL_TIMESTAMP |
||
|
VL |
floating point and timestamp tags |
SQL_DOUBLE |
SQL_FLOAT |
|
integer tags |
SQL_INTEGER |
SQL_FLOAT |
|
|
digital tags |
SQL_VARCHAR |
||
|
string tags |
SQL_VARCHAR |
||
|
QE |
SQL_INTEGER |
SQL_FLOAT |
|
|
SS_I, ANN_I |
SQL_INTEGER |
SQL_FLOAT |
|
|
SS_C, ANN_C |
SQL_VARCHAR |
||
|
ANN_R |
SQL_REAL |
SQL_FLOAT |
|