A payload is delivered to a relational database including IBM's DB2, Oracle, or Microsoft's SQL Server. The database resides on the local node or on an external enterprise system.
The transaction is either
- one way — data is transferred only to the database table.
- two directions — from the PLC to a database table and from a database table to the PLC. When the transaction transfers data in two directions, it is referred to as a bidirectional transaction.
Selection of the transport will display the schemas
associated with that database.
Selection of a schema will make available its associated tables. Selection of a table will display the columns defined in the table. Specifying an action determines the SQL operation or stored procedure to perform.
The following describes the default parameters that typically appear on the Transport Map window when a database transport is selected.
Schema
These are the schemas that are available for the database transport that you selected. The schema will contain tables whose columns and rows you want to store values from the map variable. Schema defaults to the user ID specified in the transport (the user ID associated with the database). The schema is used to differentiate tables within the database.
When you select Schema , all tables in that schema are automatically available from the Table drop-down list.
Table
When you select Insert , BatchInsert , Select , Select with Update , Select with Delete,Update, or Delete from the Action list, you can use the Table drop-down list. These are the available database tables.
Select the table that you want to update or add data to.
The associated SQL tab will be populated with column
information for that table.
Procedure
When you select StoredProcedure from the Action list, the To Enterprise title changes to From/To Enterprise and you will also see Procedure.
The
Procedure
drop-down list provides the
available subroutines for the selected stored procedure.
For information about the
Allow in Store and
Forward
check box, click
Using a stored
procedure
.
Action
Once the table is added to the Transport Map window, you can select an action. Specifying an action determines the SQL operation or stored procedure that handle the values returned from the PLC. The direction of the flow of data provided by the specific SQL operation or stored procedure is reflected as a To Enterprise , From Enterprise , and From/To Enterprise section on Transport Map window.
The following shows the options available from the Action drop-down list.
.
A database transport facilitates SQL Insert, Batch Insert,
Update, Select, Select with Delete, Select with Update,
Stored Procedure, and Delete operations as follows:
- Insert — Adds a single row of data into a database table. For more information, see Inserting data (Insert) .
- Batch Insert — Adds multiple rows of data into a database table. For more information, see Inserting multiple rows of data (Batch Insert) .
- Update — Changes the value of a specific column in a database table. For more information, see Changing data (Update) .
- StoredProcedure — Invokes a procedure that might update a single table or multiple tables in the same database. For more information, see Using a stored procedure.
- Select — Uses specific columns from a table and then updates PLC device variables with data from those columns. For more information, see Selecting columns (Select).
- Select with Update — Updates a set of columns on a record after the row has been fetched from the table. For more information, see Updating a set of columns (Select with Update).
- Count Rows — Returns the number of rows in a database table. The data is fetched from the database using criteria based on a Where clause. For more information, see Determining the number of rows on a table (Count Rows).
- Select with Delete — Deletes a record after the row was fetched from the table. For more information, see Deleting a set of columns on a record (Select with Delete).
- Delete — Deletes rows from a table. For more information, see Deleting rows in a table (Delete).
Once the action is selected, the next step is to create
the map variables. You can automatically create the map
variables using the
MapTable
button or you can add each map
variable separately. For more information, see
Creating
map variables automatically
.
The direction of the flow of data provided by the specific
SQL operation or stored procedure appears as
To
Enterprise
,
From Enterprise
, and
From/To Enterprise
on the bottom of the
Transport Map window.
To Enterprise
The data flows from the PLC to the enterprise database system.
The following shows an example of the To Enterprise section for a database destined payload.
A tab appears that matches the specified SQL operation.
For transport maps with Insert, Batch Insert, Update, and Delete operations, the To Enterprise section will contain these columns:
| Column name | Description |
|---|---|
| Column | The names in this column correspond to the column names from the selected database table. |
| DB Type | These are the custom data types that are associated with the database transport. For more information, click vendor database data types . |
| Required | This column reflects the meta data for the Required column as supplied by the database table specification. When Yes , the column must have data. For example, if you clear a value from a row of the Variable column that says Yes , and then try to save the transport map, a message will tell you that required column must have data. If the Required column shows No , the column will not become populated unless you set a value using a map variable or macro. Note that Update operations do not have a Required column. |
| Variable | This column contains the same map variables as the Input tab. Using the MapTable button, you can automatically create map variables and they will appear on the Variable column. For more information, see Creating map variables automatically . |
| Value | This column contains the value of a constant. You add the constant from the Variable column. Note that Select operations do not have a Value column. |
For more information on how to define the
To
Enterprise
section for a transport map that uses
an Insert operation, see
Inserting data
(Insert).
From Enterprise
The data flows from the database table to the PLC.
The following shows an example of the FromEnterprise section for a Select operation. A Select is considered a read-only operation because no data is modified on the local or enterprise database system.
For transport maps with Select operations, the
From
Enterprise
section will contain these columns:
| Column name | Description |
|---|---|
| Column | The names in this column correspond to the column names from the selected database table. |
| DB Type | These are the custom data types that are associated with the database transport. For more information, click vendor database data types . |
| Nullable | This column reflects the meta data for the Required column as supplied by the database table specification. When Yes , the column must have data. For example, if you clear a value from a row of the Variable column that says Yes , and then try to save the transport map, a message will tell you that required column must have data. If the Required column shows No , the column will not become populated unless you set a value using a map variable or macro. |
| Variable | This column contains the same map variables as the Input tab or Output tab. Using the MapTable button, you can automatically create map variables and they will appear on the Variable column. For more information, see Creating map variables automatically. |
|
Default Value
|
Optional. This column is used in
Select
operations to
handle null values from the database.
When specifying a value in the
Default Value
column,
the null returned from the database is
substituted with the default value. If
the database returns a value, the
default value is not used.
|
For more information, click
Selecting columns (Select)
.
From/To Enterprise
The data flows in two directions — from the database table to the PLC and from the PLC to the database table.
The From/To Enterprise section title indicates the direction of the data flows both ways and applies to read and write operations. The From/To Enterprise section applies to a Select with Update, Select with Delete, and Stored Procedures.
The following shows an example of the From/ToEnterprise section for a Select with Update operation
Notice the
Select
and
Update
tabs. The Select with Update
operation is used to update a set of columns on a record
after the row has been fetched from the table. For more
information, see
Updating a set of columns
(Select with Update)
. For inforation about the
Pessimistic locking
check box, see
Updating a set of columns
(Select with Update)
.
For stored procedures, the tab is slightly different.
The
From/To Enterprise
section provides a
StoredProcedure
tab with
these columns:
| Column name | Description |
|---|---|
| Parameter | The names in this column correspond to the parameter names from the selected stored procedure. |
| DB Type | These are the custom data types that are associated with the database transport. For more information, click vendor database data types . |
| Parm Type | The parameter was declared as IN, OUT, or INOUT. IN — the data that is being sent to the stored procedure. OUT — the data that is being returned from the stored procedure (bidirectional). INOUT — the data that is being sent to the stored procedure and then returned from the stored procedure (bidirectional). Note if you are running Microsoft SQL Server, you might see a Return parameter. This parameter type is automatically added by the SQL Server program. For more information, refer to the stored procedure documentation that came with Microsoft SQL Server. |
| Variable | This column contains the map variables from the Input tab or Output tab. Using the MapTable button, you can automatically create map variables and they will appear on the Variable column. For more information, click Creating map variables automatically . |
| Value | This column contains the value of a constant. You add the constant from the Variable column. |
| Storedprocedure : Note that a stored procedure with only an IN parameter is considered a one way transaction to the enterprise database system. |