Different results running named query testing than in script

Ignition
,
1

I have run across a very strange case where running a named query in the test tab works fine but will return an empty dataset when run with system.db.runNamedQuery. I have gone so far as to hard code the values into the query itself so the parameters aren't used and still get an empty data frame.

		for i in range(1,6):
			modTitle = "mod" + str(i)	
			if i < len(newPsetData["modList"]) and len(newPsetData["modList"][i-1])==3:
				params[modTitle] = newPsetData["modList"][i-1]
		system.perspective.print("params: " +str(params))	
		preview = system.db.runNamedQuery('PSET_RULE_MANAGEMENT/BOMVER_SERIAL_MULTI_PREVIEW',params)
		system.perspective.print(preview.getRowCount())
		return preview

The results from the print statement before the named query are :

params: {'mod2': u'356', 'team': u'502', 'stn': u'5020', 'machine_Info': u'MIDSHIP385', 'mod1': u'385'}

Which are expected values.

I know I have posted bugs with stupid mistakes based on typos but this time I am at a complete loss since I checked all the values as they are passed into the query.

Thanks for all your help.

2

You'd better show us a screenshot of the named query and the parameters, I think.

3

Here are the parameters of the named query. At the moment a majority are hard coded and the query returns expected results when run in the test tab.

image
image1337×676 41.3 KB

4

I suspect that the problem is caused by trying to use Value parameters outside the WHERE clause.

You would have to use parameter type QueryString and the {myString} syntax. I'm not sure how to do this in a `runNamedQuery' command.

This opens up some SQL injection possibilities so use with care.

https://docs.inductiveautomation.com/display/DOC81/Named+Query+Parameters

5

I don't trust anything that uses SQL script structures in JDBC. It isn't officially supported and basically only works with Microsoft. I would move all of that into a real stored procedure.

6

I was using the declared variables as a way to test the query with the idea of removing the parameters as a possible issue. The parameters are passed as a dictionary. The values that have been passed in during testing are

{'mod2': u'356', 'team': u'502', 'stn': u'5020', 'machine_Info': u'MIDSHIP385', 'mod1': u'385'}

These values work perfectly fine in the testing tab.

7

So the idea would be to call it as a store procedure on that is kept on the SQL side? I admit that I am less familiar with the practices that are unsupported by ignition. What are the JDBC structures that should be avoided?

8

What version of Ignition? What version of SQL? Can you replicate this problem with less parameters? In your screenshot of the named query you have a lot of code going on that we can't see. I think @pturmel is correct and I would move the stored procedure to the SQL side.

9

My apologies, that is all useful information. Ignition is version 8.1.19, SQL is Microsoft SQL Server 2019 (RTM-CU13) (KB5005679) - 15.0.4178.1. I do have a separate query that handles less parameters (only one mod) and that also works when being called by script. I don't have the permissions necessary to create a stored procedure so there is a bit of bureaucracy involved in setting it up which is why I want to do all I can resolve it before going down that route.

10

Post your SQL query (leaving at least some of the variables in there). It may be possible to salvage it by removing the DECLARE statements and using the parameters directly in the body of the query.

2 Likes
11

The bug has been sorted out and it might be useful information as well.

TL;DR: empty assignment can work in testing but not in scripting. WHERE mod IN (mod1,mod2,mod3,mod4,mod5)

Digging in a bit:
The loop that assigns the mod list to the param dictionary had an off by one error so mod5 was never assigned. Apparently this fails when scripting but in some cases in testing but not when scripting.

1 Like