# Different results running named query testing than in script

**URL:** <https://forum.inductiveautomation.com/t/different-results-running-named-query-testing-than-in-script/65493>\
**Category:** Ignition\
**Tags:** ignition81, perspective\
**Created:** [October 20, 2022, 10:03pm UTC](https://forum.inductiveautomation.com/t/different-results-running-named-query-testing-than-in-script/65493 "2022-10-20T22:03:39Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Chris\_McPherson](https://avatars.discourse-cdn.com/v4/letter/c/858c86/32.png) [@Chris\_McPherson](https://forum.inductiveautomation.com/u/Chris_McPherson)\
**Post date:** [October 20, 2022, 10:03pm UTC](https://forum.inductiveautomation.com/t/different-results-running-named-query-testing-than-in-script/65493/1 "2022-10-20T22:03:39Z")

</div>

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.

```auto
		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 :

```auto
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.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [October 20, 2022, 10:08pm UTC](https://forum.inductiveautomation.com/t/different-results-running-named-query-testing-than-in-script/65493/2 "2022-10-20T22:08:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Chris\_McPherson](https://avatars.discourse-cdn.com/v4/letter/c/858c86/32.png) [@Chris\_McPherson](https://forum.inductiveautomation.com/u/Chris_McPherson)\
**Post date:** [October 20, 2022, 10:10pm UTC](https://forum.inductiveautomation.com/t/different-results-running-named-query-testing-than-in-script/65493/3 "2022-10-20T22:10:31Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/4/74f7fab7107c0b1e2b201720d035a929643f9860.png)

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [October 20, 2022, 10:19pm UTC](https://forum.inductiveautomation.com/t/different-results-running-named-query-testing-than-in-script/65493/4 "2022-10-20T22:19:07Z")

</div>

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](https://docs.inductiveautomation.com/display/DOC81/Named+Query+Parameters)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 21, 2022, 12:48am UTC](https://forum.inductiveautomation.com/t/different-results-running-named-query-testing-than-in-script/65493/5 "2022-10-21T00:48:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Chris\_McPherson](https://avatars.discourse-cdn.com/v4/letter/c/858c86/32.png) [@Chris\_McPherson](https://forum.inductiveautomation.com/u/Chris_McPherson)\
**Post date:** [October 21, 2022, 3:29pm UTC](https://forum.inductiveautomation.com/t/different-results-running-named-query-testing-than-in-script/65493/6 "2022-10-21T15:29:39Z")

</div>

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

```auto
{'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.

---

<div class="post-metadata">

**Author:** ![Chris\_McPherson](https://avatars.discourse-cdn.com/v4/letter/c/858c86/32.png) [@Chris\_McPherson](https://forum.inductiveautomation.com/u/Chris_McPherson)\
**Post date:** [October 21, 2022, 3:32pm UTC](https://forum.inductiveautomation.com/t/different-results-running-named-query-testing-than-in-script/65493/7 "2022-10-21T15:32:13Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![bmeyers](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bmeyers/32/37453_2.png) [@bmeyers](https://forum.inductiveautomation.com/u/bmeyers)\
**Post date:** [October 21, 2022, 4:05pm UTC](https://forum.inductiveautomation.com/t/different-results-running-named-query-testing-than-in-script/65493/8 "2022-10-21T16:05:13Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Chris\_McPherson](https://avatars.discourse-cdn.com/v4/letter/c/858c86/32.png) [@Chris\_McPherson](https://forum.inductiveautomation.com/u/Chris_McPherson)\
**Post date:** [October 21, 2022, 4:15pm UTC](https://forum.inductiveautomation.com/t/different-results-running-named-query-testing-than-in-script/65493/9 "2022-10-21T16:15:19Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [October 21, 2022, 4:45pm UTC](https://forum.inductiveautomation.com/t/different-results-running-named-query-testing-than-in-script/65493/10 "2022-10-21T16:45:51Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Chris\_McPherson](https://avatars.discourse-cdn.com/v4/letter/c/858c86/32.png) [@Chris\_McPherson](https://forum.inductiveautomation.com/u/Chris_McPherson)\
**Post date:** [October 21, 2022, 9:41pm UTC](https://forum.inductiveautomation.com/t/different-results-running-named-query-testing-than-in-script/65493/11 "2022-10-21T21:41:58Z")

</div>

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.