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 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? 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 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 and getTagDefinition operations.
Attributes to Export 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.

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 on the Output tab.

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:

If you select the getSnapshot or getTagDefinition operation, the following input item is available:
Input Item Description
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 applicable only when the Use Wildcards check box is cleared on the General tab.

If you select the getSnapshot or getTagDefinition operation, the following input item is available:
Input Item Description
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 applicable only when the Use Wildcards check box is cleared on the General tab.

If you select the getArcvalue operation, the following input items are available:
Input Item Description
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

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.

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

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.