Versions Compared

Old Version 3

changes.mady.by.user Paul Scott

Saved on

compared with

New Version Current

changes.mady.by.user Paul Scott

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.


 

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:

Code Block
languagesql
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.
Code Block
languagesql
call tankCount("Area A")

  

Parameters in the Stored Procedure Group

When using a Stored Procedure Group, paremeters parameters may be configured to each item based on the type of the parameter:

  • The Target Name column is used for writing, so specifying an IN or INOUT parameters under this column will have the item try to write its value to the parameter
  • The Output column is used to move the value of an OUT or INOUT parameter into an item in the group. Note that if an item in a group is configured to reference an OUT parameters, its Target Name value should be set to Read-Only

A general reference of how items should be configured based on the type of the associated parameter can be seen below.



Scroll html ignore


Iulink
URLhttps://inductiveuniversity.com/video/stored-procedure-group?r=/course/transaction-groups
NameStored Procedure Group

 


 

 

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...