Run Multiple SQL Statements in SQL Binding

Ignition
1

I am trying to pivot my data from a table for use in some components, but to do so required using multiple SQL statements to get the data into the format I needed. I am able to run this just fine directly against my DB, but not with Ignition (through the Database Query browser or the data binding on the component).

Here is my SQL code:

SET @sql = NULL;

SELECT
	GROUP_CONCAT(DISTINCT
		CONCAT(
			'MAX(IF(recipe_name = "',
			recipe_name,
			'", viscosity_average, NULL)) AS "',
			recipe_name,
			'"'
		)
	)
INTO 
	@sql
FROM 
	pm_viscosity_report
WHERE 
	recipe_name IS NOT NULL AND
	recipe_name != '';
	

SET @sql = CONCAT(
	'SELECT date_end, ',
	@sql,
	' FROM pm_viscosity_report WHERE date_end IS NOT NULL GROUP BY date_end'
);

EXECUTE IMMEDIATE @SQL;

Is this not possible directly? If not, I think I may have to resort to letting the client do this in scripting.

The error I get is:

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT
	GROUP_CONCAT(DISTINCT
		CONCAT(
			'MAX(IF(recipe_name = "',
			recipe_n' at line 3

I’ve been hacking at this for some time and just cannot figure out what to do. I know these forums are full of people way smarter than me, so I thought I’d see what you all come up with.

2

SQL statements in Ignition are run through JDBC driver(s), which generally speaking do not support more complex queries/statements - such as nested queries, or those with variables.

Your best bet is probably to make your queries into a stored procedure, then execute that stored procedure from Ignition.

3

I’ll give that a shot.