I am using 8.1.0. I have watched the video for the subject matter and still dont quite understand. What I want to do is execute a stored procedure and have those columns that it returns write to a memory tag so I can them use those tags and compare them to another set of tags for a SpecAudit project that I am working on.
Does this sound like something that could be achived?
Definitely something that can be achieved but I am not sure a transaction group is the most appropriate method of doing it.
Transaction groups are similar to historian/historical data in a tag in that they target certain tags and save values on a predetermined time interval and execution condition (if tagX > 1, then save tags A,B,C values every 1 second for instance).
What I think you might want for, if you’re just trying to update a memory tag to the data returned via a stored procedure, is to setup a gateway timer script (if you want it to run every X seconds) or setup a tag change event (if you want some tag change to drive the execution) and in there you can do something like
call = system.db.createSProcCall("get_shift_workers")
system.db.execSProcCall(call)
values = [call.getResultSet()]
tags = ['some/Tag/Path']
system.tag.writeBlocking(tags, values)
Check out system.db.createSProcCall - Ignition User Manual 8.1 - Ignition Documentation to see how to call a stored procedure via scripting.
Doing this in a gateway timer script or gateway tag change event would also allow you to do any data comparisons needed.
Alright, so here is my stored procedure that was created in SQL and how I execute in SQL
exec [65M19Extruder_ItemsBySpecAndRevisionNumberForWinCC] 3709 , 1
In scripting would I call it like this:
system.db.execSProcCall([65M19Extruder_ItemsBySpecAndRevisionNumberForWinCC] 3709 , 1)
?
3709 and 1 are params? You will need to use registerInParam.
Try
call = system.db.createSProcCall("get_shift_workers")
call.registerInParam(1, system.db.INTEGER, 3709)
call.registerInParam(2, system.db.INTEGER, 1)
system.db.execSProcCall(call)
values = [call.getResultSet()]
tags = ['some/Tag/Path']
system.tag.writeBlocking(tags, values)
Take a look at the page system.db.createSProcCall - Ignition User Manual 8.1 - Ignition Documentation
They offer examples on multiple scenarios you might use or need
I hate to ask but since I have a stored procedure already do I need to do this portion? system.db.createSProcCall
Yes. You’re “creating” the Ignition/JDBC object used to execute the stored procedure, not creating anything on the actual database.
So to start the script will look like this:
call = system.db.createSProcCall("[65M19Extruder_ItemsBySpecAndRevisionNumberForWinCC]")
call.registerInParam(1, system.db.INTEGER, 3709)
call.registerInParam(2, system.db.INTEGER, 1)
system.db.execSProcCall(call)
Another option might be to run your stored procedure in a named query, then run the named query with system.db.runNamedQuery. It seems easier, at least to me.
Yes, assuming that your parameters are both integer types.
You would also need to register a return parameter if you are returning things from the SP.
It’s very dependent on how the SP is written.
I agree with @josborn a Named Query is the easiest and IMHO most reliable.
I have been thinking about a named query also, mainly because I am more familiar with it. Right now the Stored Procedure, when ran in a Named Query, returns a table with 203 columns and 1 row. I assume i will need 203 memory tags for the data to be stored in?
And the parameters are integers but are also variable. like 3801,1 or 3897,3
You can have a single tag of dataset type.
This seems like something that you would do in a Gateway Tag Change Event, then you can run the query and do the comparison all in one place.
What determines if the parameters change?
Right now, our scheduling team lets the production team know what product to run and then they reference that to a listing of what product runs at what spec/rev and enters that as the machine setting in HMI. After that periodically production checks the running settings to the spec/rev. I am trying to automate that check.
Since we dont have a digital input for the spec/rev, that will have to entered manually into a text field that will change parameters in the named query so a comparison can happen, spec/rev against actual values from machine tags. From there I will setup some way for them to be notified, so the issues can be addressed.
I have always used output parameters in my procedures to return data to a TransactionGroup.
Example:
CREATE PROCEDURE ABC
@IN_VALUE1 INT
,@IN_VALUE2 INT
,@OUT_VALUE1 INT = NULL OUTPUT
,@OUT_VALUE2 INT = NULL OUTPUT
,@OUT_VALUE3 INT = NULL OUTPUT
AS
BEGIN
SELECT
@OUT_VALUE1 = <ANYVALUE1>
,@OUT_VALUE2 = <ANYVALUE1>
,@OUT_VALUE3 = <ANYVALUE1>
FROM
Table_XYZ
END
After that you can map those OUTPUT parameters directly in your TransactionGroup