Named Query Error in Ignition but not in SQL

I have the following query save as a named query in Ignition, and during a gateway event it is triggered in a script and is meant to flip a bit in a SQL table.

with CTE as
(SELECT top (3) BagProcessed
FROM [PLC].[dbo].[Production_TBL_Test]
WHERE Dest_Type like '%Bag%' and BagProcessed not in (1) and Roaster_ID = 2
order by RoastDate asc, RoastTime asc)
UPDATE CTE
SET
CTE.[BagProcessed] = 1

When I run this query in SQL, it works as desired. When I try to run it in the Named Query test tab, I get the error "Index 2 out of range"

Please help.

This is a limitation of the JDBC driver which can only have a single statement (I think that is the right term). In your query, you have a select and an update statement which would have to be separated in Ignition. You could make this a stored procedure in your db and call it from Ignition as an alternative.

7 Likes

So I made this query a stored procedure in SQL, and my NamedQuery calls it but I am still getting the same error. Any suggestions?

You have to create a stored procedure call if you go this route.
system.db.createSProcCall - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)

2 Likes