Stored Procedure Group

A stored procedure group will interact with a database stored procedure. A stored procedure in a database is like a function. The procedure has a name and it may or may not take parameters. Once the procedure is called and the parameters passed into the procedure, the query executes on the database server and a the resultset is returned. The actual text of the query is stored and managed on the database server. Stored procedure provide an increased level of security and maintenance that make them ideal for certain scenarios.

Suppose the following stored procedure already exists on the database server:

CREATE DEFINER=`root`@`localhost` PROCEDURE `tankCount`(in invar varchar(45), out outvar integer)
BEGIN
select count(area) into outvar from test.tanks where area = marker1;
END


 And suppose the procedure is called with the following SQL statement and returns the number of tanks that are associated with Area A.

call tankCount("Area A")

This is a very simple scenario. Often stored procedures contain very long or complicated queries. Lets say you want to use a transaction group to call this stored procedure and then to handle the result by writing to a tag location.

To use a transaction group to call a stored procedure

  1. Create a new Stored Procedure transaction group.

  2. Drag two tags into the Basic OPC/Group Items section of the group. The two tags must be of the same datatype as the stored procedure's parameters. In this case the two tags will be a string and an integer type.

  3. Go to the Action tab of the transaction group, select the appropriate Data source. Select the procedure name from the procedure name drop down menu.

  4. In the Basic OPC/Group Items section, from the Target Name column dropdown of the string tag, select the input parameter, and select None for the output.

  5. In the Basic OPC/Group Items section, from the Target Name column drop down of the integer tag, select Read-only, and select the output parameter for the output.

  6. Click Enabled and do a File > Save to save the project and to start the transaction group.

Note: When running a Stored Procedure from an Oracle database, you cannot use named parameters. Instead you must use the index (number) arguments. IE: 1 is the first argument, 2 is the second, etc.

Next...