Working with SQL Queries

A valid SQL must be constructed and verified before it can be incorporated into a document generation script via the use of a SQL: keyword. A basic working knowledge of SQL would be required in order to accomplish this task. Queries would need to be written and tested via a SQL tool in order to determine that no syntax errors are present and that the correct information is returned. Examples of available tools include Microsoft SQL Server Management Studio and the Microsoft sqlcmd utility. More information about both utilities can be obtained via the Microsoft website.

A dataset returned via a SQL statement would need to be processed within a Document Generation Template via a looping statement in the form of a ##Repeat statement. This statement allows for the Document Generation Engine to process each record within a dataset for output.

Example Query 1

In this example, we are going to return a list of all objects within the ACM database. The SQL statement that we will be using is as follows:

Running this query when connected to a SQL database via SQL Management Studio or sqlcmd will return a list of all data contained within the Objects table within the ACM database.

We will be using a document generation ##Repeat instruction to print each record contained within the SQL result set that is returned from the database. The statement we will be inserting into the Document Template Editor is as follows:

##Repeat

.1(SQL: SELECT * FROM Objects)

[1.{Name}]

##EndRepeat

.1

Once the SQL query completes execution, we will have a dataset containing matched data from the database. This matched data object can be thought of as a temporary table of information existing in memory. The ##Repeat instruction will be used to iterate through this temporary table and in the previous example, we have elected to return data from the column named "Name". This column must exist within the returned dataset. Any column can be selected for returned data and the original ACM Objects table can be reviewed to check on what columns may contain information that may be useful for the task at hand. As an example: information on each object may be required and the required columns that are need are known ("Name" and "Description" columns in the Objects table). All columns are specified to be returned via the SQL statement by using the "*" wildcard character: (SELECT * FROM Objects). The following document generation script will enable us to return the information as required:

##Repeat

.1(SQL: SELECT * FROM Objects)

[1.{Name}], [1.{Description}]

##EndRepeat

.1

The highlighted section shows the columns that will be returned ("Name" and "Description" fields). Running this script within the Document Template Editor would return a list of all information contained within the Objects table of the ACM database. The following is an example of output from the previous script (returned information will vary depending on how the objects are created within the ACM database):

MyProject, Project Information

My_Controller, ControlLogix Controller

Controller_Fault_Handler, Task Description

PowerUp_Handler, Task Description

Unscheduled, Task Description

FTAlarmEvent_Server, FactoryTalk Alarms and Events

FTViewSE_Server, FactoryTalk View SE Display

FTHistorianSE_Server, FactoryTalk Historian SE Scan Classes

Looking at the information returned, we may decide that we only want to return objects related to the currently opened ACM project. Earlier in this section we spoke of the four temporary tables created behind the scenes when running a document generation script. One of these tables (#Objects) contains information pertaining to objects for the current ACM project. Switching the SQL script to use the temporary objects table can be achieved by changing the table name to the temporary table name:

SQL: SELECT * FROM #Objects

The complete script would now look as follows:

##Repeat

.1(SQL: SELECT * FROM #Objects)

[1.{Name}], [1.{Description}]

##EndRepeat

.1

Example Query 2

In this example, we are going to be querying information from the temporary objects table that will provide information relating to the current ACM project only. We are going to retrieve information on objects relating to a specific library. Once again, we are going to be selecting the "Name" and "Description" fields but this time the query will be optimized and will only return these fields to the dataset. We will replace the "*" wildcard used in the previous query with the actual fields we want to return.

Search parameters will be to a specific library so that we return objects for this library only. The WHERE part of the SQL statement will accomplish this and provide the filter. The catalog number field within the #Objects temporary table will be used where we will be returning records that match the value as specified. In the test example, only record matches will be returned where the CatalogNumber column equals the value ‘MsVlv2sS’.

The statement we will be inserting into the Document Template Editor is as follows:

##Repeat

.1(SQL: SELECT Name, Description FROM #Objects WHERE CatalogNumber='MsVlv2sS')

[1.{Name}], [1.{Description}]

##EndRepeat

.1

As can be seen via the highlighted section, we have replaced the "*" wildcard used in example 1 with the actual field names that are required from the dataset. This will improve efficiency and will be seen in the speed of execution of the query with only the required columns returned.

Once again, the ##Repeat instruction will process each record returned in the result-set and we will be returning both available columns, namely "Name" and "Description".

The following is an example of some returned information (returned information will vary depending on how the objects are created within the ACM database):

XV_0001, Valve Two State 1

XV_0002, Valve Two State 2

XV_0003, Valve Two State 3

XV_0004, Valve Two State 4

XV_0005, Valve Two State 5

Advanced Query

The following sample will return all libraries in use for a specific library type.

The SQL statement that we will be using is as follows:

SELECT COUNT(libs.CatalogNumber) AS Qty, libs.CatalogNumber, libs.Description

FROM #Objects AS objs INNER JOIN RegisteredLibraries AS libs

ON objs.LibraryID = libs.LibraryID

WHERE libs.LibraryType = 'ControlModule'

GROUP BY libs.CatalogNumber, libs.Description

ORDER BY libs.CatalogNumber

The query selects information across two tables (#Objects and RegisteredLibraries) and returns all libraries of the type ‘ControlModule’ in use within the currently loaded ACM project as we are querying information from the temporary #Objects table as discussed earlier.

The query is using the temporary #Objects table. The "#" will need to be removed when testing this query with a SQL tool like SQL Management Studio or sqlcmd as the temporary Objects table is only created by the Documentation Generation Engine when a documentation generation script is run within ACM. An error will be generated if the "#" is not removed as the table does not exist within the ACM database.

The complete document generation script would look as follows:

##Repeat

.1(SQL: SELECT COUNT(libs.CatalogNumber) AS Qty, libs.CatalogNumber,libs.Description FROM #Objects AS objs INNER JOIN RegisteredLibraries AS libs ON objs.LibraryID = libs.LibraryID WHERE libs.LibraryType = 'ControlModule'GROUP BY libs.CatalogNumber, libs.Description ORDER BY libs.CatalogNumber)

[1.{Qty}] [1.{CatalogNumber}] [1.{Description}]

##EndRepeat

.1

Once the data has been retrieved from the database, the result-set is then processed and iterated through via the ##Repeat instruction. Three columns would be created in the output. The first column would display the number of instances in which the selected library is used, followed by the catalog number for the library and finally the libraries description information in the last column.

The following is an example of some returned information (returned information will vary depending on how the objects are created within the ACM database):

112 MsAinSiS Analog Input

128 MsDinSiS Digital Input

128 MsVlv2sS Valve Two State