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.
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:
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
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.
This is old topic but I was just looking at doing something similar and this is top search result and I don't see anything more recent, so just in case someone else is looking.
My thought was to take the array and convert it to a string in the script where you are calling the NamedQuery then in the NamedQuery convert back to array
-- Assume :IDList is a comma-separated string like '1,5,7,9'
DELETE FROM dbo.YourTargetTable
WHERE YourIDColumn IN (
SELECT TRY_CAST(value AS INT)
FROM STRING_SPLIT(:IDList, ',')
WHERE TRY_CAST(value AS INT) IS NOT NULL
);
script to run query
# Example: Deleting selected items based on their IDs
id_list = [1, 5, 7, 9]
id_string = ",".join(str(i) for i in id_list)
args = {
"IDList": id_string
}
system.db.runNamedQuery("Namespace/DeleteItemsByIDList", args)
Pssssst!
and
I had a similar problem and this is my way of doing it.
When project start, i need select 1 or more "Companies" for Start:
I fill the table with a NQ with JSON return, so when a Select a Company i need this on onRowClick for do a list:
try:
# Obtener los datos actuales de la tabla en formato JSON
json_data = self.props.data
# Extrae los encabezados de las columnas del primer elemento del JSON
headers_data = list(json_data[0].keys()) if json_data else []
# Extrae las filas
filas_data = [list(row.values()) for row in json_data]
# Crea el Dataset
dataset = system.dataset.toDataSet(headers_data, filas_data)
# Fila seleccionada y dato de la tabla
sr = self.props.selection.selectedRow
data = system.dataset.toPyDataSet(dataset)
# Obtener el índice de la fila seleccionada
rowIndex = event['row']
# Verificar si el índice de la fila es válido
if rowIndex is not None:
# Obtener el valor actual de 'seleccion' en la fila seleccionada
currentValue = json_data[rowIndex]['selected']#['seleccion']
# Imprimir el valor actual en consola
#system.perspective.print("Fila {0}: 'seleccion' actual es {1}".format(rowIndex, currentValue))
id_emp = data[rowIndex]['value'] #Asi viene del dataset
lista = self.view.custom.id_emp_seleccion
#lista = self.session.custom.filtroEmpresaSeleccion.id_emp_lista
#self.view.custom.id_empresa_seleccionadas
# Cambiar el estado del check según su valor actual
if currentValue is False:
#system.perspective.print("Check estaba en Falso")
# Cambiar el estado del check a True
self.props.data[rowIndex]['selected'] = True
if not id_emp in lista:
lista.append(id_emp)
else:
#system.perspective.print("Check estaba en Verdadero")
# Cambiar el estado del check a False
self.props.data[rowIndex]['selected'] = False
#lista.remove(id_emp)
if id_emp in lista:
lista.remove(id_emp)
system.perspective.print('lista:')
system.perspective.print(lista)
In my Session Custom i need a "List of Companies" (Array)
Ex:
id_emp_lista:
[
62,
76,
3,
1,
78,
2
]
I've another Session custom for do string:
E.B:
{this.custom.filtroEmpresaSeleccion.id_emp_lista}
Transform Script:
id_emp_seleccion = value
str_return = ""
if id_emp_seleccion:
str_return += ', '.join(str(emp) for emp in id_emp_seleccion)
return str_return
Finally, i do another Sessino custom 'filtro_id_emp_select' translate to: filter_company_selected.
E.B:
{this.custom.filtroEmpresaSeleccion.id_emp_lista}
Transform Script:
id_emp_seleccion = value
cadena = "IN ("
if id_emp_seleccion:
cadena += ', '.join(str(emp) for emp in id_emp_seleccion)
cadena += ")"
return cadena
i use to much this 'filtro' for concat another filters:
I pass the filter in every query because its my principal filter based on a first selection.
Maybe is not the best way, but trust its work and i concat many string filter for pass to multiple NQ.
and isn't hard to maintain.
I hope it helps.
