Existing Stored Procedures MSSQL

Ignition
1

We have an existing MES (MSSQL) that has numerous SP’s that basically contain recipes, component pick bins, etc. These SP’s typically require input parameters of stationID (which workcell) and PartID (which SKU). the outputs are typically good/bad (invalid combination) and a result set containing a list of picklights, or a list of recipes to run. We would want to apply these ‘lists’ and write to PLC tags for each item in the list(s). These are not time based nor really grouped, they are processed as parts are placed in the workcell.

I have not found a good source for explaining the use of Stored Procedures other than transactions in SQL bridge. I really don’t see how that would meet our needs.

Any and all suggestions, guidance much appreciated

2

Ignition has builtin scripting functions for calling stored procedures. Examples of using them are at this link: docs.inductiveautomation.com/di … eSProcCall

Best,

3

Thanks. I have been attempting to make this work with the following example snippet on that page:

#This example would call a stored procedure “get_shift_workers” with no arguments, which returned a result set of employees for the current shift. It then pushes the resulting dataset into a Table component:

call = system.db.createSProcCall(“get_shift_workers”)
system.db.execSProcCall(call)

results = call.getResultSet()
table = event.source.parent.getComponent(“Table”)
table.data = results

This snippet fails in the script console with: File “”, line 5, in
NameError: name ‘event’ is not defined

I changed the sp to one in my DB and I do get the result set (once comment the event statement) but do not know how to access the data.

4

Hi mwaynesmith,

The script would only work in a window, like in an event handler on a button where the window had a table called “Table” in it.

Instead try this script in the script console:

[code]call = system.db.createSProcCall(“get_shift_workers”)
system.db.execSProcCall(call)

results = call.getResultSet()

for row in system.dataset.toPyDataSet(results):
print list(row)[/code]
I suggest that you read about datasets in the Ignition User Manual and in my blog post about it here: nickmudge.info/post/datasets-in-Ignition

That way you will know how to work with datasets and access data in them.
Best,

5

Thanks, that worked.