SQL Stored Procedures

For the uninitiated, Stored Procedures are a series of predefined SQL statements that are configured and stored in a database. They can be easily called from another application, such as Ignition, with a single statement. Conceptually, they are very similar to a scripting function as parameters may be passed to them, they can utilize looping and conditional logic, and they can return a value.

Stored Procedure Syntax

Stored Procedures are created and maintained in your database. As a result, the commands used to create a Stored Procedure vary per database. In regards to calling a Stored Procedure, the syntax can also differ.

For instance, if you have a Postgres database connection, calls have to get translated to the native SQL by the JDBC driver. This is done through the escapeSyntaxCallMode configuration property that treats this syntax like a Procedure call rather than a Function call, and translates it to SQL accordingly. However, if escapeSyntaxCallMode=callIfNoReturn isn't provided to the JDBC connection string, the Stored Procedure execution will fail when using the system.db.execSProcCall scripting function or a Stored Procedure Transaction Group. Alternatively, you can execute via a manually written query using CALL as shown below, which will work as expected.

SQL Server

The EXEC command is utilized to execute a Stored Procedure.

Pseudocode - Executing a Stored Procedure in SQL Server

EXEC dbo.myStoredProcedure

Parameters may be passed to the Stored Procedure. SQL Server's documentation has more details on utilizing parameters with Stored Procedures.

Pseudocode - Executing a Stored Procedure in SQL Server with Parameters

EXEC dbo.myStoredProcedure @myParam = 100, @AnotherParameters = 'Hello'

MySQL

MySQL uses the CALL command to execute a Stored Procedure. Note the parentheses characters at the end, as they must be present even when the Stored Procedure is not utilizing parameters.

Pseudocode - Executing a Stored Procedure in MySQL

CALL myStoredProcedure()

If parameters are defined, they can be passed via the parenthesis, similar to how functions work in Python.

Pseudocode - Executing a Stored Procedure in MySQL with Multiple Parameters

CALL myStoredProcedure(100, 'Hello')

For information on the creation of a Stored Procedure, as well as proper SQL syntax to call a Stored Procedure, reference your database's documentation. Alternatively, if you have a database administrator, they can typically help with the creation and execution of a Stored Procedure.

Calling Stored Procedures in Ignition

There are several locations in Ignition where Stored Procedures may be utilized.

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:

SQL - Calling a Stored Procedure in MySQL

CALL return_all_bays()

As with all bindings, Tag and Property references may be utilized by clicking the appropriate icons in the binding window.

Named Queries

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 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:

1. 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.
2. 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

Although, most other system functions in the db library, like system.db.runPrepQuery, can be used to call a Stored Procedure, we generally recommend against this. More functionality is built into system.db.createSProcCall and system.db.execSProcCall making them better suited to work with Stored Procedures.