Problems with dynamic filtering of DB query - "Value is not set for parameter x"

Ignition
, ,
1

Hey all, have a little hang up that I'm not sure how to resolve. I am trying to set up a filtering system for a log. Presently, each input field for the filter has a checkbox that will enable or disable that field. Interacting with the checkbox will also alter the query that is run on button press to update the table. To do this, I have created a set of labels to hold each piece of the query, and a final label that simply sums the pieces.

As a result, my query can look like either option below, with any possible combination of AND statements in between:

SELECT * FROM rcp_cleaninglog WHERE 0 = ? AND Reactor = ? AND Product = ? AND Step = ? AND cID = ? AND Initials = ?

SELECT * FROM rcp_cleaninglog WHERE 0 = ?

The same is done for the arguments, as follows:

[0,reactor,prod,step,cid,init]

[0]

I have included the 0 = 0 argument to allow a return of the entire dataset if no filters are applied, and to help with the flexibility (i.e., all actual arguments now require an 'AND' or preceding comma, no argument is "first" in the list, thus the formatting is standardized).

However, there seems to be some disconnect that appears to occur only on the 'arguments' side. When running the following script:

reactor = event.source.parent.getComponent('ReactorSlct').selectedLabel
prod = event.source.parent.getComponent('ProductSlct').selectedLabel
step = event.source.parent.getComponent('StepSlct').selectedLabel
cid = event.source.parent.getComponent('CIDSlct').text
init = event.source.parent.getComponent('InitialSlct').text

filter = event.source.parent.getComponent('QueryTxt').text
arg = event.source.parent.getComponent('ArgTxt').text

ret = system.db.runPrepQuery(filter,arg)

event.source.parent.getComponent('Wash Log').data = ret

The system will return the following error:

caused by Exception: Error executing system.db.runPrepQuery(SELECT * FROM rcp_cleaninglog WHERE 0 = ? AND Reactor = ? AND Product = ? AND Step = ? AND cID = ? AND Initials = ?, , [0,reactor,prod,step,cid,init], )
caused by GatewayException: The value is not set for the parameter number 2.
caused by SQLServerException: The value is not set for the parameter number 2.

I have triple checked the component names/links and they all appear to be correct. It is also fully independent of what parameter(s) are chosen to filter by, I have tested each param by itself and with others. The error message above has the full argument contained within it, meaning the query should see each necessary parameter, I am completely unsure why they aren't making it all the way through. My best guess is an issue within the system.db.runPrepQuery(x,y,z) line, maybe a missing argument, but I've formatted the query script itself no different than any other in the past.

Any help on this would be greatly appreciated!

Thanks.

2

Look into short circuit filtering
How to run Script faster - Ignition - Inductive Automation Forum

3

Something small that I noticed while looking back over the error message: It appears that my 'arguments' clause might be inserted in the 'database' parameter instead of the 'arguments' parameter for the query.

The script's syntax is
" system.db.runPrepQuery(query, args, database, tx)"
but the error message seems to show my query (condensed) as
"system.db.runPrepQuery(query, NULL, args, NULL)"

could this be the issue? is my 'arguments' set somehow empty because it's being assigned to the wrong parameter? If so I'm unsure of the fix.

Thanks!

4

I'll take a look at it, thank you

5

It looks to me like args is a single string, not an array. So that is used for the first ? in 0 = ? and there are no more parameters. The error is that there is no value provided for parameter number 2. Assuming (?) that they are counting parameters starting from 1 that would make sense.

Create list of arguments to pass in the second parameter. Or use a named query (with named parameters).