Learn how to call Stored Procedures through Transaction Groups with the Stored Procedure Group.

Video recorded using: Ignition 8.1


(open in window)

[00:00] In this example, we're going to create a stored procedure group that will interact with the stored procedure on my database. So store a value, retrieve back the number of rows stored, and also retrieve a count of how many times we've called this stored procedure. A store procedure is essentially a type of function in a database, and it can have various parameters that are mapped as either input, output or both. First, we have to define to store procedure in the database. For the purposes of our example, we'll create a new procedure that's defined as follows. We're going to call it "safe insert", because store procedures are often used to provide extra security by validating data before inserting it into a database. In this store procedure, will have three parameters; an input parameter named value, an output parameter that we'll call a row count and an additional parameter called counter. Generally speaking, all databases support these features since most databases will support store procedures with input and output parameters. Our defined store procedure will insert our value input parameter into a table we've already created. Then, it will select the row count from that table into our output parameter. We will also have our counter parameter increment is valued by one with every execution. Now that the procedure is created, we can go back into ignition and create a store procedure group. From the transaction group design space, we'll select a store procedure group and create a new group named "myGroup". We'll select our procedure from the dropdown list. Since we have an active database connection to the server where the store procedure is defined, it should be visible to ignition automatically after having been created. Now, we must create items that will map to the parameters in our store procedure. For input value, we'll just select a ramp tag from our OPC browser and drag it into our items lists. The target name or first input or output parameters. Selecting the dropdown, we can see the parameters in our store procedure. I want this rent value to be stored into my database, so I will map it to my input parameter value. For the output parameters, we'll use Writable tags from our OPC browsers, such as WritableInteger1. We'll map this to our row count output parameter. Next, because we're not using this as an input, and in fact, we can't because it's defined as only as an output, we'll set the target name to read only. For our last parameter, I will use WritableInteger2. And we'll map both the target name and the output to the same parameter, which is counter. Now with a group configured, I enable it and I save my changes. As soon as we enable the group, we see that a value begins writing. Our row count is being written back and our counter is being incremented. This is a simple example, but shows how to map to all the different types of parameters in a store procedure. Additionally, we can choose them at the timestamp or quality code to other parameters. Items can also be mapped to return the value of a procedure if the database supports it. For example, in my SQL stored procedures can not return values, but functions can. So the exact mapping will depend on the definition of the procedure and what the database will actually support.

You are editing this transcript.

Make any corrections to improve this transcript. We'll review any changes before posting them.