There are several locations in Ignition where Stored Procedures may be utilized from.
Instead of typing a query directly into a SQL Query binding, a Stored Procedure may be executed instead. Assuming a MySQL database contains a Stored Procedure named 'return_all_bays', we can call the procedure on a binding with the following:
As with all bindings, Tag and Property references may be utilized by clicking the appropriate icons in the binding window.
One of the easiest ways to utilize Tags with a Stored Procedure is to use the Stored Procedure Group. Parameters can be easily assigned to each item in the group, and utilize all of the features of a Transaction Group, such as scheduled execution and triggers.
Each item in the group is linked to a specific parameter in the Stored Procedure. Any IN or INOUT parameters can write directly to the Tags, while new values can be fed into OUT and INOUT parameters allowing you to easily move data from Tags into the database with the Stored Procedure.
There are a few ways to call a Stored Procedure from a script in Ignition.
The recommended approach to calling a Stored Procedure from a Python script in Ignition typically involves two main steps:
Once the Stored Procedure has been executed, the call context generated in step #1 can be used to reference any values that were returned.
Technically, most other system functions in the "db" library, such as system.db.runPrepQuery, can be used to call a Stored Procedure. We generally recommend against this, as system.db.createSProcCall and system.db.execSProcCall are better suited to work with Stored Procedures and have some additional functionality not found in the other db functions.