Pass an array into a named query's WHERE ... IN ( , , , ...) statement

I’m not sure how to approach this. The query below works when hard coded.

SELECT id, t_stamp, machineId, cycleCount
FROM production_log 
WHERE 
	t_stamp >= :_start
	AND t_stamp <= :_end
	AND machineId IN ('Mach03', 'Mach04', 'Mach05', 'Mach06')

How would one pass in the machine list as an array?
Failing that, is there an alternative?

Many thanks.

No, you cannot pass an array as a parameter. You pretty much cannot do this (safely) with a Named Query. A Prep Query can be constructed dynamically with the correct number of question marks. I recommend formalizing it in a project script. If using Vision, use a gateway message handler and system.util.sendRequest() to run such without needing to weaken your client-side security.

Thanks, Phil.

That’s what I thought.

This is a Perspective application. The user selects a machine group to view statistics on. The gateway script generates the list of machines to pass into the named query. I can’t see any security risk there (if it were possible in the first place).

I think I can live with that. If I create more than enough parameters I can pass them in individually and use None / NULL for the unused parameters.

params = {"_start": '2022-05-24 00:00:00',
          "_end": '2022-05-24 08:00:00',
          "p1": "Mach03", 
          "p2": "Mach04",
          "p3": "Mach05", 
          "p4": "Mach06",
          "p5": None,
          "p6": None
}
system.db.runNamedQuery("mySelectQuery", params)

Any better ideas?

No, don’t use extra parameters. Use system.db.runPrepQuery() instead of system.db.runNamedQuery(). Generate the SQL with exactly the correct number of question marks inside the IN clause every time.

1 Like

As an out of the box solution, you could define sets of machine ID’s in a separate DB table do a join on that table against the production_log. That is basically the same functionality as you “IN” clause.

This of course presupposes that:

  1. You can modify the DB schema
  2. You have a fixed set of machines you want to query

OK, but that causes a few problems. I’m building a plant dashboard (using the Dashboard component). A chart widget will show the production rates for a group of similar machines. I was planning to pass through the dates and the machines of interest as view parameters and to pass those on to the named query in the chart data binding.

How do I do this now with your proposal? I can’t bind the chart data to a script.

Thanks again.

Might be one of the valid use cases where you should use runScript as an expression binding. That would be my first thing to try here

1 Like

Bytearray is possible to send as a parameter to the named query.

I have been using excel to create case statements of machines with integers passing as parameters as the keys. Might be adaptable. Might be too messy.

Line=Case 	when :machine=1  then 'Brm1' 
				When :machine=2  then 'Brm2'
				When :machine=3  then 'Brm3'
				When :machine=4  then 'Brm4'
				When :machine=5  then 'Brm5'
				When :machine=6  then 'Brm6'
				When :machine=11 then 'Cym1'
				When :machine=12 then 'Cym2'
				When :machine=13 then 'Dyg3'
				When :machine=14 then 'Dyg7'
				When :machine=15 then 'Eeg5'
				When :machine=16 then 'Eeg6'
	else null end )

You can bind to something you like the update rate of,
then use the script transform to replace that thing. I am not an expert though, could be a bad idea.