SQL Stored Procedure Encapsilation

I’m working a a vendor’s documentation that demonstrates calling a stored procedure in a way that I’m not familiar with. I would like to encapsulate the call in a custom script in the project and then use that script throughout the project. Can anyone suggest how this would look like in Ignition scripting? I have worked with stored procedures in Ignition before and I reviewed the Ignition documentation but am a little perplexed reconciling the Ignition stored procedure call with the variable types that the code example provides.

There’s unfortunately a lot of ceremony involved with stored procedure calls (due to the way JDBC works) but the manual page for createSProcCall should get you started:

The gist is that you create the stored procedure call “object” with create, then you register your parameters (and the values you’re going to supply), then you exec the stored procedure.

1 Like

You may be toast. I’m not familiar with any generic JDBC way to deliver complex data to your procedure like that. I could do it with pure PostgreSQL connection, so I’m sure it can be done, but possibly not with Ignition’s infrastructure.

Thanks for the reply. Is there a datatype for NULL because documents I cannot see it. For example, if the value in python that I want to send to the call is None then I want to register a NULL value for the variable type.

It appears so. I will likely have to encapsulate the stored procedure call inside another stored procedure on the database side.