Calling Stored Procedures in Ignition
There are several locations in Ignition where Stored Procedures may be utilized from.
SQL Query Bindings
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, Tagand Property references may be utilized by clicking the appropriate icons in the binding window.
Named Queries support Stored Procedure calls. As mentioned under Stored Procedure Syntax, the syntax is based entirely on how your database expects a Stored Procedure to be called. Be mindful of the Query Type setting, as it has to match what the stored procedure is doing: if it is returning a result set, leave it set to Query, if it is modifying a record in the database, then set the type to Insert Query.
Stored Procedure Groups
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.
Stored Procedures in Python Scripts
There are a few ways to call a Stored Procedure from a script in Ignition.
Using createSProcCall and execSProcCall
The recommended approach to calling a Stored Procedure from a Python script in Ignition typically involves two main steps:
- Calling system.db.createSProcCall to create a call context, or object that effectively represents the impending stored procedure call. This object can be used to specify parameter values that will be passed to the Stored Procedure.
- Using system.db.execSProcCall to execute the Stored Procedure.
Once the Stored Procedure has been executed, the call context generated in step #1 can be used to reference any values that were returned.
Other System Functions
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.