JDBC Query

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

Note: The time zone of the data retrieved from the OSIsoft PI Server is the time zone of the OSIsoft PI Server.

General

In 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 in the General tab of the JDBC Query activity:
Field Module Property? Description
Name No A unique name of the activity in a process.
Server Connection 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 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.
Use Wildcards No The option that determines precise query or fuzzy query will be performed on the PI server according to the tag names you enter in the Input tab.
  • If you select this check box, you can only enter one tag name in the Input tab, and then fuzzy query is performed on the PI server.
  • If you do not select this check box, you can enter multiple tag names in the Input tab, and then precise query is performed on the PI server.
Note: This check box is only available for the getSnapshot and getTagDefinition operations.
Attributes to Export No The list of value attributes that you can select to specify the schema for the output data in the Output tab.

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

Export xsd schema 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 in the Output tab.

Description

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

Input

In 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 in the General tab:

Input Item Description
If you select the getSnapshot or getTagDefinition operation, the following input item is available:
TagName The name of the tag that you want to retrieve.

You can enter multiple tag names by right-clicking TagName and then clicking Duplicate. But this operation is only applicable when the Use Wildcards check box is cleared in the General tab.

If you select the getArcvalue operation, the following input items are available:
TagName The name of the tag that you want to retrieve.
StartTime The starting time of the time range within which you want to retrieve the historical values.
EndTime The ending time of the time range within which you want to retrieve the historical values.
Count The number of values that you want to retrieve.

Output

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

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

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 be output as 36.99999618.... For more information, see https://techsupport.osisoft.com/Troubleshooting/Known-Issues/119059.

Fault

In 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 in the Fault tab:
Element Name Data Type Description
msg String Displays the error message.
msgCode String Displays the error code.