MySQL Stored Procedure Question

Ignition
1

I created a Stored Procedure for pivot table on MySQL workbench.
The Stored Procedure has a Prepared Statement and an Execute. If I call it on the workbench I get a result showing me what I would expect two columns with 18 rows with the data I am looking for.

Two questions; when I call this Procedure from Ignition the results is the number of rows affected.
How can I get the actual result that I see on workbench?

Next, a version of this Procedure I have been testing also creates a temporary table. If I try to query that temp table on Ignition I get an error saying the table does not exist within the schema. I am able to query it within the workbench and It is in the same schema. What could be going wrong here?

My goal with using this Procedure is actually to combine 3 Procedures to produce the results I need to create a Gantt Chart.

Thank you

2

Are you invoking the .getResultsSet() against your Stored Procedure call context after you execute the stored procedure to retrieve the result set associated with the call?

Edit: I couldn’t find an anchor to link to, but take a look at the examples here if you haven’t yet!

1 Like
3

Thank you. My issue is that I was simply trying to test the Call Procedure within the Database Query Browser.

4

Is there a way to call the stored procedure from a Query Tag?

5

You can probably invoke the stored procedure call within the query definition of a Query Tag, but I’m not sure that I’d recommend it. It doesn’t have rich support for parameter placeholders that would insulate you from SQL Injection.

I’d probably recommend another mechanism for updating a tag with results from your procedure. First step would probably be to put the procedure invocation into a named query. From there you could use something like a Gateway Timer Script to run the named query and write the results to a memory tag. Many other possible ways of going about it, too, depending on your requirements.

6

Query tags can’t run Named Queries, either. Not yet, at least.

Consider running the stored procedure or named query from a script, where the script runs on a timer or on a tag change.

7

I was able to work this out just now within a tag change script on a memory tag that I will use as a place holder for my parameter. I have changed my Query Tag to a Memory tag with the Dataset type.

	if currentValue.value != 0:
		
		call = system.db.createSProcCall("PROCEDURENAME")
		call.registerInParam(1, system.db.INTEGER, currentValue.value)
	  
		system.db.execSProcCall(call)
	
		results = call.getResultSet()
		
		system.tag.write("[.]MemoryDatasetTag", results)

Edit* I was trying to work out a way to “merge” horizontally three stored procedures (three datasets) into one dataset that will work with the Gantt Chart component. I ended up finding a pure SQL way to produce query that gets the results I needed.