JDBC Query

By using the JDBC Query activity you can set tag values, retrieve tag definitions and retrieve the tag value with the most recent timestamp or historical tag values within a specified time range. You can set the values of the tag and the timestamp with specified time and tag value.

Note: The time zone of the data retrieved from the OSIsoft PI Data Archive server is the time zone of the OSIsoft PI Data Archive server.

General

On the General tab, you can specify the name of the activity in the process, establish a connection to the PI JDBC server, select the type of operation you want to perform, specify and export the XSD schema.

The following table lists the fields on the General tab of the JDBC Query activity:
Field Module Property? Visual Diff? Description
Name No Yes A unique name of the activity in a process.
Server Connection Yes Yes The PI JDBC shared resource selected to connect to a PI JDBC server.

Click the icon to select an OSIsoft PI System JDBC connection resource. If no matching PI JDBC connection resource is found, click Create Shared Resource to create one.

Operation No Yes The type of operation you want to perform:
  • getSnapshot: gets the snapshot value that has the most recent timestamp.
  • getArcvalue: gets the historical values within a specified time range.
  • getTagDefinition: gets tag definitions.
  • getValueHistory:
    • For a positive integer provided as count parameter on the Input tab, getValueHistory gets the specified number of historical values.
    • If no input is provided for the count parameter on the Input tab, getValueHistory gets the historical values within a specified time frame.
  • setTagValues: sets tag values.
Use Wildcards No Yes The option that determines precise query or fuzzy query is performed on the PI Data Archive server according to the tag names you enter on the Input tab.
  • If you select this check box, you can only enter one tag name on the Input tab, and then fuzzy query is performed on the PI Data Archive server.
  • If you do not select this check box, you can enter multiple tag names on the Input tab, and then precise query is performed on the PI Data Archive server.
Note: This check box is available only for the getSnapshot, getArcvalue and getTagDefinition operations.
Attributes to Export No No The list of value attributes that you can select to specify the schema for the output data on the Output tab.

You can also load a customized output schema file for your JDBC activity. For more information, see Customizing JDBC Output Schema.

Note: This option is available only for the getSnapshot, getArcvalue and getTagDefinition operations.
Export xsd schema No No After finishing your selection in Attributes to Export or loading a customized output schema file, click this button to export the schema which is then displayed on the Output tab.
Note: This option is available only for the getSnapshot, getArcvalue and getTagDefinition operations.

Description

On the Description tab, you can type a short description for the JDBC Query activity.

Input

On the Input tab, you can specify the tag name, time span, or both for your activity.

The input items are different depending on the operation type you select on the General tab.
Operation Refer
getSnapshot and getTagDefinition getSnapshot and getTagDefinition: Input Items
getArcvalue getArcvalue: Input Items
getValueHistory getValueHistory: Input Items
setTagValues setTagValues: Input Items

Output

On the Output tab, you can find the output results structured in the schema you specify on the General tab.

For the getSnapshot and getTagDefinition activities, results of only the tags that have matching data on the PI Data Archive server are returned.

Operation Refer
getSnapshot getSnapshot: Output Items
getArcvalue getArcvalue: Output Items
getTagDefinition getTagDefinition: Output Items
getValueHistory getValueHistory: Output Items
setTagValues setTagValues: Output Items
Note: When you use the JDBC Query activity to get tag definitions, rounding errors might occur in the output results of certain tag attributes. For example, the tag attribute "comdevpercent" which is set to 37% might have the output as 36.99999618. For more information, see https://techsupport.osisoft.com/Troubleshooting/Known-Issues/119059.

Fault

On the Fault tab, you can find the error messages and error codes of the JDBC Query activity. For more information about errors, see Error Codes.

The following table lists the error schema elements on the Fault tab:
Element Name Data Type Description
msg String Displays the error message.
msgCode String Displays the error code.