GetPointsSQL Method (Server object)



Given a query string, this method returns a PointList collection that contains PIPoint objects which match the request. The query string is in the format of a SQL "Where clause" and may reference tables and columns from tables supported by the OSI PI-ODBC product.


object.GetPointsSQL WhereClause, [nvExtraTableList], [asynchStatus]

The GetPointsSQL method syntax has these parts:




An object expression that evaluates to a Server object.


A string containing an expression in SQL syntax that defines the desired selection criteria. The "where" keyword should be omitted from the string. For example: "Tag Like 's*' AND Span < 100.0" The table names may be omitted from the column names if only columns from the PIpoint table are referenced. If additional tables are used they must be included in the nvExtraTableList argument and join criteria should be added to the where clause.


An optional NamedValues collection containing the names of tables other than PIpoint used in the whereClause argument. The value portion of the contained NamedValues is ignored.  Starting with version 1.3.x of the PI-SDK this table can also contain a NamedValue with the name "ANSISQLVAL".  This is described under Remarks below.


An optional PIAsynchStatus object created by the caller and passed to the call allowing the method to be executed asynchronously, providing a means to cancel the call, and providing a means for the caller to be signaled as the call progresses and completes its tasks or to poll for results.


This call provides a powerful mechanism for finding specific points on the Server based on a wide range of criteria. The call makes use of the underlying Server's SQL subsystem and follows the syntax described in the OSI PI-ODBC manual. There are cases where this syntax does not match the attribute names available from the PointAttributes collection in the PI-SDK though it is still valid for this call. In addition, there are attributes available through the PI-SDK (including those from user defined Point Classes) which are not recognized by this routine as it is based on the SQL implementation. To perform a query against these attributes, use the Server.GetPoints method.


Because GetPointsSQL is such a powerful call, care must be used in constructing reasonable queries.  Posting a complicated query that involves looking at every tag on a large PI system can take some time to execute and adversely affect other users as with any database system.  Adding a restriction based on tag name or point source is advisable for performance.  


The SQL subsystem limits the size of strings returned.  For this reason, tag names located by the SQL subsystem will be no more than 80 characters in length.  If you have tags with longer names use the GetPoints call.  


True asynchronous calling of this method is now invoked when a PIAsynchStatus object is passed as the asynchStatus argument.  This means the call will return quickly (generally with success unless local argument checks fail) but the returned PointList will be empty until the call completes. 



GetPoints vs. GetPointsSQL

GetPointsSQL can implement complex queries, including joins with other PI tables. GetPoints cannot do such complex queries, but it is faster, and can use custom point attributes, which GetPointsSQL cannot. To convert between equivalent GetPoints and GetPointsSQL queries, note that GetPointsSQL uses attribute names as defined by PI-ODBC, but GetPoints uses attribute names as defined internally in the PI Point Database. These names differ in some instances.


If no points are found by this method it returns an error of pseNoPointsFound.  The Server.GetPoints call does not return an error in this case but instead returns an empty PointList collection.


Starting with version 1.3.x of the PI-SDK the nvExtraTableList argument can contain a NamedValue with the name "ANSISQLVAL" where the value is ignored.  This is not the name of a table but a flag recognized on PI3 servers by the SQL subsystem (pisqlss). The flag is identical to setting ANSISQLVAL=1 in the pisql.ini file in the [Options] section.  The purpose of this flag is to return values for integer tags in a floating point return variable (rval) as well as the integer variable (ival).  This is valuable to the GetPointsSQL method because it allows a where clause that queries for tags where a particular value is found in the snapshot or archive.  Without the flag set, such a query will only return floating point tags where the value is matched.  With the flag set, both integer and floating point tags where the value is matched are returned.


Trappable Errors

In addition to generic errors (such as Out of Memory), the following errors may occur:




The query returned no points.


The call failed. Consult the Err.Description for more detailed server information on the cause of the failure.

Enabling Operational Intelligence