RSSql Tutorial Version 2.1

1. RSSql Tutorial Version 2.1 This tutorial has been developed using Microsoft’s PowerPoint. Simply click your mouse or hit the Enter key to advance. The Page Up and Page Down keys can also be used to scroll forward and back. Use the controls in the lower-left to navigate to a particular slide and to exit the tutorial.

2. RSSql Overview Industrial Control System Enterprise System DDE or OPC Server RSSql Enterprise Database(s) Enterprise Application SQL Server Oracle etc. PLC RSView/MMI RSSql (or Rascal) is a software product from Rockwell Software which functions as an industrial transaction manager. As such, RSSql allows an Industrial Control System on a factory floor to communicate, coordinate and share data with any Enterprise Software System which includes a database. The transaction model allows great flexibility in defining the interactions between these two systems to address a wide range of requirements. This tutorial will present an overview of RSSql’s capabilities and user interface. A more detailed tutorial is included with the RSSql Sample Application that is also included in this disk.

3. RSSql Overview Industrial Control System Enterprise System DDE or OPC Server RSSql Enterprise Database(s) Enterprise Application SQL Server Oracle etc. PLC RSView/MMI An Industrial Control System refers to the automated system which controls manufacturing processes in a plant. For RSSql’s purposes, it usually consists of a number of PLCs (or Programmable Logic Controllers) and one or more data server computers which run software compatible with the DDE (Dynamic Data Exchange) or OPC (OLE for Process Control) standards. RSLinx is an example of such a data server software package. In addition, the Industrial Control System may include some MMI stations (Man/Machine Interface) using a product like RSView32 that allows plant floor operators to interact with the Control System.

4. RSSql Overview Industrial Control System Enterprise System DDE or OPC Server RSSql Enterprise Database(s) Enterprise Application SQL Server Oracle etc. PLC RSView/MMI The Enterprise System is a general term that refers to any application which uses a database system. RSSql does not interface with the Enterprise application itself, but directly with the underlying database. The database could be one that was created just to hold data from RSSql, or part of a larger application such as an Enterprise Resource Planning system or legacy application.

5. RSSql Architecture RSSql Industrial Control System Enterprise Database RSLinx Connectors RSView Connectors OPC Connectors DDE Connectors Transaction Manager RSLinx OLE-DB Connectors OCI Connectors ODBC Connectors Control Connector Services Enterprise Connector Services RSView DDE Servers ODBC Databases 1) RSSql is made up of 4 components: the Transaction Manager, Control Connector Services, Enterprise Connector Services, and the Transaction Builder. In a RSSql installation there will be one Transaction Manager and one or more Control Connector Services and Enterprise Connector Services, all of which run as NT Services. The Transaction Builder is the Graphical User Interface (GUI) to RSSql. 3) Enterprise Connector Services communicate between RSSql and the Database System. RSSql has three types of Enterprise Connector Services : for interfacing with databases via ODBC, via OLE DB, and via OCI (Oracle Callable Interface). These Connector Services enable RSSql to interface with many different database systems, including Microsoft SQL Server, Microsoft Access, Oracle, Sybase, and others. RSSql can support multiple connections of each type for large applications. 2) Control Connector Services communicate between RSSql and the Industrial Control System. RSSql has four types of Control Connector Services : for interfacing with generic DDE or AdvanceDDE servers, with generic OPC servers, with RSLinx, and with RSView32. RSSql can scale to support multiple connections of each type for large applications.

6. RSSql Installation - Standard Version Industrial Control System Enterprise Database(s) DDE/OPC Server(s) RSSql Control Connector Services Enterprise Connector Services Transaction Manager RSLinx SQL Server Oracle etc. PLCs RSSql is sold in two versions, Standard and Professional. The Standard version, which is represented here, permits installation of the RSSql Transaction Manager and all Control Connector and Enterprise Connector Services on a single Microsoft NT computer. In addition, the DDE or OPC data server (such as RSLinx) for the Industrial Control System must also be installed on the RSSql computer. Connections to Databases on other computers are supported by this version.

7. RSSql Installation - Professional Version RSSql Control Connector Services Enterprise Connector Services Transaction Manager Industrial Control System Enterprise Database(s) SQL Server Oracle etc. DDE or OPC Servers The Professional version of RSSql permits scaling for much larger installations. The Transaction Manager remains on a single NT computer, but the Control Connector Services and Enterprise Connector Services can be distributed to other computers. Communications between computers is accomplished using TCP/IP.

8. Starting RSSql When RSSql is run for the first time, this graphic will be presented. The top section shows how RSSql transactions bridge the gap between Control Systems and Enterprise Systems. The bottom section shows the overall structure of RSSql with its three major components: Transaction Manager, Control Connector Services, and Enterprise Connector Services. It also contains links to the Help system which explain each component and its role in the operation of the system. You can return to this graphic at any time by selecting the System Graphic button on the toolbar on the RSSql main screen.

9. Creating a Configuration To create an application, we must build a RSSql Configuration. A RSSql configuration is a set of RSSql transactions that are treated as a single unit, together with the Control Connectors and Enterprise Connectors required to perform them. You may create many configurations, but the RSSql Transaction Manager can run only one configuration at a time. There are two ways to create a new Configuration. Select “New” from the main toolbar, or from the Configuration menu select “New” or “Checklist”.

10. The Configuration Checklist screen outlines the steps to be taken to create a complete configuration. All of the functions it uses are accessible directly from the RSSql main screen, but the Configuration Checklist is a handy tool that imparts a step-by-step organization to the process. To create a new Configuration, type in a new name in the Configuration Name box, then click on the “Step 1” button.

11. 1) Here we provide basic information about the RSSql Configuration. The Path entry tells RSSql where to store the data files associated with this configuration. If RSSql components are installed on other computers on the network, this Path must be a fully qualified UNC path name to which all those computers have access. 2) In the bottom half of the screen,you select the Control Connector Services and Enterprise Connector Services that your configuration will use. Choose DDE for generic DDE or AdvanceDDE servers. RSLinx and RSView32 both use OPC with RSSql 2.1. Generic OPC is for OPC servers from third-party vendors. 3) For Enterprise Connector Services, ODBC is the most common method which is supported by most database systems. Oracle OCI is the native interface for all Oracle databases. Use OLE DB for interfacing with SQL Server 7.0 only.

12. Defining Connectors 2) Notice on the RSSql main screen (which would be updated if we exited the Checklist screen), a root entry in the Configuration tree called Tutorial_Config has been created, and within it is an entry for each one of the three Connector Services we selected. 1) This completes Step 1 of the Configuration Checklist, and you can see that Step 1 is checked off on the Checklist screen. Also under Step 2 on the Checklist is an indication that we have three Connector Services plus the Transaction Manager service, or four in total. This is a good time to note that we can exit this Configuration Checklist at any time and pick up later where we left off. 3) We have selected which Connector Services to use, and now in Step 2 of the Checklist we must define the Connectors and assign them to specific computers.

13. First we define where the Transaction Manager, which is an NT Service, will run. The default is the current computer and current user account. If this is a distributed RSSql installation and the Transaction Manager will run somewhere else, enter the information for that computer. The User Name must have administrator privileges to initialize an NT Service on the computer, and access to all necessary RSSql files on other computers. The Port Number is used for TCP/IP communications and should only be changed if there is a conflict. Enter the password for the user and hit “Apply” to save this information.

14. RSSql uses an Apply and Close button combination on any dialog where you can do the same type of task more than once. For instance, in this dialog, you could define the Transaction Manager , several Control Connectors and several Enterprise Connectors. The Apply/Close approach allows you to do this without leaving the dialog. In dialogs that support single actions, RSSql uses a standard OK/Cancel combination.

15. 2) Clicking “Host/User” will set all of the Host Names, User Names, and Passwords of all of the Connector Services to match those on the current screen. In addition, all of the data file paths that RSSql uses will be set to the Configuration data file path. Clicking “Password” will change the passwords of all of the Connector Services to match the current one, making it easier to implement periodic password changes. 1) All of the connectors can be defined from this screen. Next we will define an RSLinx Connector. Type in a new name for the connector, and specify the NT host computer on which the service will run. The Connector service will run as an NT Service on that computer and will log on using the User Name and Password supplied here. By using the drop-down under the Connector Service field, we can define connectors for each of the three connector services we created without leaving this screen: OLE DB , RSLinx and RSView. Click “Apply” after each connector is defined. Note that if we have RSSql Professional and a distributed installation, we can create more than one Connector for each type of Connector Service, but each connector must run on a different host computer. In this example, all Connectors will run on the same local computer, which is called “rsi_mink”.

16. 2) On the RSSql main screen, which would be updated if we exited the Checklist, we can also see the new connectors have been created in the Configuration tree. We are going to exit the Configuration Checklist and take a closer look at that main screen. 1) Once you have created at least one Connector for each Connector Service type in your Configuration, Step 2 is complete. Note we get an indication on the Checklist Screen that under steps 3 and 4 there are now two Control Connectors and one Enterprise Connector to be defined.

17. We have now created the basic outline for a RSSql Configuration called Tutorial_Config. This configuration consists of three Connectors: 1) OLE-DB_on_Mink that can connect to any number of OLE DB sources available to its host computer. These OLE DB sources may point to databases on other computers. 2) RSLinx_on_Mink which can connect to an RSLinx server running on its host computer, and 3) RSView_on_Mink which provides an optimized link to any active RSView32 project on its host computer. Now we need to define the Data Points and Data Objects that each of these connectors will use.

18. Defining Control System Data Points Control System Data Points are defined in Step 3 of the Configuration Checklist.

19. RSLinx Data Points This is the screen where we identify data points in the control system serviced by RSLinx that are to be used in RSSql transactions. The center of the RSLinx Data Points definition screen is the RSWho interface of RSLinx itself. Expand the tree to locate the RSLinx device containing the desired Data Points.

20. 2) The Device Name will be automatically filled in on the screen. 4) Click on “Add Tags” to view the data points associated with the selected Tag file. 1) Click on the desired RSLinx device. 3) If a Tag File has been associated with the RSLinx server, the Tag File name will also be filled in. If there is a tag file which has not been associated, you may browse for it. RSSql provides the ability to connect directly to Tag Files of the AI Series and RSLogix Family of PLC programming software.This Tag File is the .RSP file generated by an RSLogix5 project.

21. 4) Click “Apply” and “Close” to create the data points in RSSql. 3) Select the option of having the tag addresses resolved at runtime, or having RSSql do a one-time import of the tag addresses. 1) All the data point tags, PLC addresses, and other tag file information is listed. Choose the data points to make available to RSSql by double-clicking on the tag line, 2) ... or selecting one or more tag lines and using the “>>” button to move the tag to the right pane.

22. The RSLinx data points are imported into RSSql and auto-named based upon the Connector name and the Tag Symbol. A new data point can be added by simply scrolling down and typing the information into a blank line.

23. Data Point Collection Parameters 1) The right side of the Data Points window shows the Collection Parameters for each data point, which control how RSSql manages collecting the value of the data point. There are three methods to modify these parameters: First, you can click “Collection Defaults” to set parameters for the next data points to be added. 2) Second, you can select Data Points in the lower window, right click, and select “Edit Collection Params of Selected Rows” to change the Collection Parameters for a set of data points. These first two options will bring up the Collection Parameters screen.

24. 1) The Collection Parameters screen is used, with minor variations, with all RSSql Control Connectors. 2) Data Point Collection Type determines how the Control Connector gets new values for the data point. “Scheduled: Use the Current Subscribed Value” directs the RSLinx to send new values to the Control Connection periodically at the “Subscription Scan Rate”. The Control Connector caches the data value so it can provide it to the Transaction Manager upon request. “Scheduled: Request the Current Value from the Device” directs the Control Connector to ask RSLinx to read a new data point value from the device (PLC) whenever it is needed for a transaction. “Unscheduled: Send Subscribed Value Whenever It Changes” is a collection type that is driven by the RSLinx server. When the server detects a new data point value, it sends the value to the Control Connector, which passed the value to the Transaction Manager. This setting is required if a data point is to be a transaction trigger.

25. 1) There are two Timeout Properties RSSql uses in collecting Data Point values. “Data Retrieval” specifies how long the Transaction Manager will wait for the Control Connector to give it a data point value. If the Data Retrieval timeout is exceeded, the Substitution Properties to the right are invoked. “Data Valid” indicates how long a data value that is cached by the Transaction Manager is “fresh”. If a data point value is needed by a transaction, the Transaction Manager will first check to see if the data value it has in its buffers is “fresh” (within the time specified by “Data Valid”). If the value is fresh, the Transaction Manager will use it. If the value is stale, the Transaction Manager will collect a new value from the Control Connector. A Data Valid time of 0 will force RSSql to get the latest value from the Control Connector every time the data point is needed. 2) If the Transaction Manager cannot obtain a data value within the time specified by the “Data Retrieval” value on the left, the value will “time out”. In this case, you can specify one of three courses of action for RSSql. First, you can specify that there should be “No Substitution” for the timed out data point. In this case, the entire transaction that required the missing data value will fail. Second, you can direct RSSql to “Substitute Previous Value”, in which case the last known value for the missing data point will be used. Finally, you can specify an actual value to be substituted for the missing data value. 3) Clicking on “Apply” will apply any changes on this screen to the selected Data Points. “Factory Defaults” will restore the Data Point defaults.

26. 2) When you are finished making changes, click “Apply” to apply the changes, and “Close” to dismiss the screen. 1) Finally, Data Collection parameters can be changed one at a time by typing directly on the active grid (some are drop down, some accept a value). Changing the ORDERDONEBIT to Unscheduled mode allows it to be a transaction trigger.

27. RSView32 Data Points 2) Click on “Add RSView Tags” to access the data points. 1) This screen is used to create Data Points from an RSView project. RSSql is tightly coupled with RSView32 and will detect any RSView32 project that is currently open or running on the host computer for the Connector. The RSView32 project name(s) automatically appears in the list.

28. 1) All of the data points in the RSView32 project are displayed. To select them, move them into the right pane by simply double-clicking on the tag name, 2) ...or highlighting the tag name and using the arrow key.

29. 2) “Apply” applies the changes, and “Close” dismisses the screen. 1) Again the data points are auto-named, and the collection parameters can be changed on the active grid or by right-clicking on the Data Point.

30. OPC Data Points A new feature of RSSql 2.1 is the support of OPC, or OLE for Process Control, for interfacing with control systems. There is no OPC Connector in this RSSql Configuration, but we will briefly review the screens that are used to create OPC Data Points. This is the screen where we identify data points in the control system serviced by an OPC Server to be used by RSSql in transactions. All the OPC servers on the host computer for the OPC Connector are listed in this drop-down box. We’ll choose a third-party OPC server product called KEPServer which is sold by a company called KepWare.

31. 2) Click “Browse Server” to activate OPC Browse. 1) Choose the host computer from the drop-down list, which can be a remote computer if the OPC server supports DCOM. 3) The Topics defined for the server are shown in the left pane. If a tag file has been associated with the Topic, clicking on the device will... 4) ... bring up the right pane, which lists the data points symbols. This works with tag files of the AI Series and RSLogix Family of PLC programming software. You can also use CSV (comma separated variable) files to provide this information.

32. 1) Highlight the Data Points (called Items or Item Leaf in OPC) that you wish to use in RSSql transactions. 2) Then click the “Add” button. 3) The Data Points appear in the lower pane. RSSql automatically assigns unique names to each data point. OPC Data Point collection parameters can be modified in the same ways as data points of other Connector types.

33. Defining Enterprise Connector Data Objects 1) Back on the Configuration Checklist, the Data Points for both of the Control Connectors have been defined, and Step 3 is complete. 2) The next step is to define the connections to the database and identify the table columns and stored procedure parameters that will be used in the RSSql transactions. RSSql is not limited to a pre-defined database schema - it can interface with databases, tables, views and stored procedures that you define. We will define combinations of database connections and database objects called Data Objects. This is Step 4 of the Configuration Checklist.

34. OLE DB Data Objects 1) The OLE DB connector is used to communicate with SQL Server 7.0. 2) Many Data Objects can use the same connector, and each must be given a unique name. 3) This Data Object will append records to a database table. A Data Object can also Update existing database records. 4) The Database Connection field is used to select an OLE DB connection to a specific database, or create one if one does not already exist. Click on the Browse button to create a connection.

35. 1) An OLE DB database connection points to a specific database on a specific server. Choose a server from the drop-down list - the local computer is “rsi_mink”. 2) Click “Refresh” to get a list of all the OLE DB compatible databases on that server.

36. 2) You can Test the database connection before leaving this screen. Click “Apply” to save the Connection. 1) Select the database for this connection. RSSql automatically names the Connection. Fill in the user name and password that will be used to log into the selected database on the selected server. (The username “sa” and the default blank password for the system administrator is shown here, but is not recommended.) From this dialog you can create connections to databases on other computers.

37. 1) The database connection is now specified. Select the desired table in the database from the drop-down list, and the columns contained in that table are listed on the left of the screen. 3) Clicking “Apply” saves the Data Object. We have created an OLE DB Data Object called Machine_Status_Logging, which will insert records into the Machine_Status table in the server/database referenced by the \\RSI_MINK\RSSql_Tutorial connection, and the columns listed on the right are available to RSSql transactions. A RSSql transaction can refer to one Data Object only, so all the fields necessary for the transaction must be contained in the Data Object. If fields from different tables are required for the same transaction, you can create a View or a Stored Procedure in the database which references multiple tables. 2) The database columns to be used in RSSql transactions are selected from the left pane and moved to the right pane with the buttons in the middle. Double-clicking a database column will also move it to the right pane.

38. 1) You may also create Data Objects that update records in a database table, as illustrated here. The Update Mode is selected... 2) ...and an additional parameter is automatically assigned to each Data Object Column. Update fields are updated by the transaction, and non-Update fields are used to select the proper records in the table to be updated.

39. 1) Finally, a Data Object can use a Stored Procedure. A Stored Procedure is like a database macro program written in SQL language. Stored Procedures can have inputs and outputs (called Parameters ), which can be used to create bi-directional transactions which pass information from the control system to the database, and return database values to the control system. Select “Stored Procedure”, 2) and choose the desired Stored Procedure from the list box. Note RSSql has a Wizard for building Stored Procedures. 3) What we now see in the left pane are the input and output parameters or variables from the stored procedure. The input parameters will be passed into the stored procedure from RSSql, and the output parameters will be sent from the stored procedure to RSSql. All parameters are copied to the right pane to create the Data Object.

40. ODBC Data Objects RSSql also supports ODBC and Oracle OCI Connectors. Illustrated here is the screen for building ODBC Data Objects. The process of creating Data Objects is identical to that for OLE DB Connectors. Only the process for defining Database Connections varies. Clicking the Browse button next to the Database Connection field brings up...

41. …This ODBC Connection dialog box. Select a an existing System DSN from the drop-down box, or click the Browse button to create a new one.

42. An ODBC System DSN is available to all users on the computer, including the RSSql ODBC Connector service, which runs as an NT service. User DSNs will not work with RSSql. All the existing System DSNs are listed in this Microsoft ODBC Administration tool dialog. Click “Add” to create a new System DSN. You will be prompted for a name, a server, a default database, and a user id and password. The System DSN “Tutorial” already exists on this system and is an ODBC connection to the RSSql_Tutorial database.

43. Choose the System DSN “Tutorial” from the drop-down list of System DSNs.

44. 2) Use the “Test” button to test the connection before leaving this screen, then click “Apply” to save the connection. 1) RSSql assigns a Connection Name which is the same as the System DSN, but can be modified. Here again, you may enter a user name and password for the targeted database, which will override the user name and password specified in the System DSN. (The username “sa” for the system administrator is shown here, but is not recommended.)

45. Here is the same Data Object for writing to the Machine_Status table that we created using OLE DB, now created using ODBC. This illustrates the ability of RSSql to manage many different connections concurrently. Note that there is a convenient “Convert” button on all the Data Object screens which is used to convert an existing Data Object from one connector to another (from ODBC to OLE DB, for example.)

46. Creating Transactions Now Step 4 has been completed. Now that both the Control System “end” and the Enterprise database system “end” have been defined, we must create the RSSql Transactions, which will bind these two systems together and allow them to exchange data. That is Step 5.

47. Transactions control the movement of data between the Control System and the Enterprise Database System. You can create many transactions within a configuration that will all be active at the same time. A transaction acts on a single Data Object, but can use Data Points from any Control Connector. First, name the transaction, then choose the Data Object the transaction will use from the drop-down list.

48. This data object adds a record to the Machine_Status table, and the table columns associated with the Data Object appear in the lower pane. The next task is to define how the data is to be supplied for each of these columns.

49. Drag and Drop Bindings 2) The checkboxes and drop-downs filter which Data Points appear in the lower pane. 1) A double-click of the mouse brings up a window containing all of the data points available to be bound to the Data Object Columns. 4) ... and drop it on the correct Data Object Column in the left window. This specifies that the transaction will copy the current Data Point value into the corresponding Data Object Column when the transaction is executed. This method can be used for all of the bindings that do not require calculations. 3) Then just drag a Data Point from the right window...

50. Sophisticated calculations can be performed to create values to be stored in the database using RSSql’s Expression Editor. Instead of binding a Data Point to the Data Object column, we bind an Expression.