Transport maps can be configured to execute a Select operation. The Select operation allows you to query specific columns from a database table and then update PLC device variables with data from those columns. The data is fetched from the database using criteria based on a Where clause. The Where clause is built from map variables you create using the Output tab. If the Where clause is not used, the operation will select all rows in the table up to the maximum specified using the Max Rows parameter. The data can only be written to device variables that you have write permission.
The following assumes that you have specified the database transport and selected a schema.
- Using the Action down arrow,
select Select.
The bottom of the Transport Map window changes slightly to accommodate the Select operation. - From the Table down arrow, select
the table you want to use as source of the enterprise
data for this transport map.
The Select tab at the bottom of the Transport Map window becomes populated with information from the selected table.
Notice the Distinct rows only check box. When the check box is selected, the Select operation will run with the SQL distinct operator. For more information, see Using the distinct operator with Select. - Since the purpose of this Select operation is to
update controller device variables with data from these
columns, you must create those map variable using the
Output tab.
To automatically add the map variables to the Output tab, make sure the Output tab is selected, and then click the MapTable button.
When you define the trigger using this transport map, you will associate one or more map variables from the transport map's Output tab to PLC device variables on the trigger's Output tab. - Under the From Enterprise section associate the
appropriate map variables on the
Output tab with the table columns on
the Select tab.
If you need help with this task, go to Associating map variables with table columns.
The Input tab will consist of a collection of map variables whose values will be associated with columns used in the Where clause.
- To construct a Where clause, click the
Where button.
The Where window appears.
The map variables from the Input tab appear under Data. By default, system macros are also available. - Select a column name, a mathematical expression to
relate the expression to either a map variable or a
macro, and then click Add.
The Where clause is added to the Statements section.
- Continue to associate additional columns with map variables and macros as appropriate.
- When the Where clause is complete, click
OK.
You are returned to the main Transport Map window.
The next step is to specify an Order by clause. Order By determines the order in which rows will be returned by the database (either ascending or descending). - Click the Order By button. The
Order By window appears.
The window provides the same column names as the selected table. - Select a column name from the
DatabaseColumns
pane, and then click Add. The column
name is added under the Database
Column.
- You can change the order at any time. Simply select the column name under Database Column, and then use the Move Up, Move Down, or Remove buttons.
- You can order the column names in ascending or
descending order. Click the down-arrow under the
SortOrder
column.
- When you complete the sort order, click
OK.
You are returned to the main Transport Map window. - A value in the Max Rows box
indicates the maximum number of rows that you want the
Select operation to handle.
For this example, 5 rows are specified. This prevents more than 5 rows of data from being processed causing a possible overlay of PLC memory. Rows over the specified limit are ignored.
The last step for this example Select operation is to create the trigger. When you create the trigger, you must have write authorization to the device variables that you associate with the map variables.
At runtime, a special resultCount variable is exposed to all Select operations. You can reference this variable when defining a trigger that makes use of the transport map. The resultCount variable will contain a value that indicates how many records were selected from the database, when the trigger executed. You can map the resultCount variable to a device variable and treat it as any other output variable.
Database null value handling
It is possible that a database column used in a Select operation contains a null value. This will cause the Select transport map to fail.
To correct this problem, type a value in the
Default Value column.
This Default Value column is used in Select operations to handle null values from the database.
The value entered must be compatible with the database
type. For example do not enter a string for a numeric data
type.
When the Select operation executes and a null is
encountered, the default value will be substituted for the
null, thereby allowing the transaction to complete
successfully.
The default value format for a database column of type date and time is MM/dd/yy HH:mm:ss.000 where hours are zero through 23.
Since this date format has an
abbreviated year pattern ("yy"), the abbreviated year
pattern must be interpreted relative to some century.
This is done by adjusting dates to be within 80 years
before and 20 years after the current
date.
For example, using a pattern of
"MM/dd/yy" and a current date of January 1, 1997, the
string "01/11/12" would be interpreted as January 11,
2012 while the string "05/04/64" would be interpreted
as May 4, 1964.
For the following enterprise gateway platforms you have to specify the default value of the timestamp as a long value that represents milliseconds since 1/1/1970 00:00:00.000
- Mistubishi MESInterface IT
- Enterprise Gateway for Siemens-ERPC