About OLE DB Provider and SQL Syntax

The Historian OLE DB Provider supports the SET and SELECT statements in SQL queries. The supported statements follow the standard SQL-92 conventions. Consistent with SQL standards, these statements are not case-sensitive.

Some reporting packages, such as Crystal Reports, hide the SQL syntax by allowing you to use experts and wizards. However, familiarity with SQL syntax may help you in troubleshooting and tuning your SQL commands.

The Historian OLE DB Provider currently does not allow SQL inserts, updates, deletes, or commits. There is also no event notification, because that is typically used along with inserting, updating, or deleting, and these operations are not allowed.

This chapter describes the supported SQL syntax for SELECT and SET statements. While the Historian Interactive SQL application allows SET statements, not all reporting packages do. For instance, Crystal Reports does not allow SET statements. In this case, you would set query parameters with a WHERE command in your SELECT statement instead. The WHERE clause overrides the SET statement for that query. Everything that can be done in a SET statement can be done using a WHERE clause.

The following figure shows a simple SELECT statement. With a SELECT statement, you can specify the Historian table and columns from which you want to retrieve data. The Historian OLE DB Provider establishes the server name at connect time. You can filter the data returned from SELECT by specifying a filter option in the WHERE clause.

The rest of this chapter details how to create statements like this and gives examples of how to build more complex, yet efficient SQL statements. This chapter is only intended to get you started with creating SQL queries with the Historian OLE DB Provider. It assumes that you are familiar with the SQL language and SQL-92 conventions. For more detailed information on the SQL language or how to create queries with your third-party reporting software, refer to your third-party documentation.

The Historian OLE DB Provider does not support the full SQL-92 syntax. The following sections document the supported syntax.