SQL Query Withing runScript()

I have the following script in the global scripts library under the script name ‘zTest’. I am attempting to run this code in two different expressions using runScript('shared.zTest.myFunc',0) and runScript('shared.zTest.myString',0). The expressions define parameters for a report.

def myFunc():
	x = system.db.runQuery('SELECT FormType FROM PMFormClass')
	x = [i[0] for i in x]
	x = [[a] for a in x]
	y = system.dataset.toDataSet(['Form Type'], x)
	return y

def myString():
	return 'Generic String'

The call to myString() successfully outputs the desired string, but myFunc() does not, yielding this error:

Using a button in Vision Client I can successfully take the return from myFunc() and pass it directly into a table as a dataset, so I believe the SQL query and the code in myFunc() is correct.

After some troubleshooting it appears that runScript() won’t run anything with a SQL query in it. Does anyone have any ideas why this might be?

I know for this very simple case it would be better to use the reporting module’s SQL capability, but this is the very boiled down version of the script that will eventually be in production. There are also a variety of reasons I would like to use the script method if it is possible.

Any ideas would be greatly appreciated.

Ignition Version: 7.9.6
Java Version (Gateway): 1.8.0_151-b12
Java Version (PC): 8.0.1510.12

system.db functions need to have a database name specified when run from the gateway scope (ie, when building your report); add a second string argument that’s just the name of your DB connection in Ignition.

1 Like

Works Perfectly! I feel a little silly that it was that simple. Thanks for the help.

Hi,
In Reports module’s parameters, I would like to link the client tag ‘DefaultDatabase’ to a runQuery function. It’s not working with my code below :

runScript("system.db.runQuery('SELECT start_datetime FROM overview_winter ORDER BY id DESC LIMIT 1','"+{[System]Client/System/DefaultDatabase}+"')[0][0]")

I tried tag() expression but it’s not working too. Can you help me ?

The Client folder in the System tag provider only exists when you're in the client scope - reports execute on the gateway, without a particular client or project associated with them.