SQL runScript in expression binding

Ignition
1

I’m trying to make a quick popup for editing some data in a table

So, user selects a row in the table. That unique value is passed to the popup. The pop up has a few labels that will be pre filled with the data by using that unique value. On the label I added a custom expression binding, I know I can do this with named queries very easy, guess I was just being lazy doing it in a expression, for the amount of time this form will be used.

system.db.runScalarQuery("SELECT category FROM equipment WHERE serial='BB-001'")

The above code works in the script console, I used a fixed Where value as the script console wont know the passed value for testing. That code wont work in the expression binding as I need to use ‘runScript’

runScript("system.db.runScalarQuery('SELECT category FROM equipment WHERE serial = 'BB-001'')")

No matter what I do I cant get that to work. If I remove the where Claus it works and returns the first value. With the where Claus I just cant figure it out, single quotes, double quotes, tried them all (I think). I will eventually change the where Claus to the {view.params.Serial} I just used a fixed value for quick testing.

Any ideas ?

2

The best idea is to not do that and move it into a named query, as you already stated.

If it’s just for understanding, your double single quotes are the issue, as you’ve also identified. You need to use one of the following:

runScript("system.db.runScalarQuery(""SELECT category FROM equipment WHERE serial = 'BB-001'"" )")

runScript("system.db.runScalarQuery('SELECT category FROM equipment WHERE serial = ''BB-001''')")
3

You’re missing the database parameter
https://docs.inductiveautomation.com/display/DOC81/system.db.runScalarQuery

or Nick might be right… I couldn’t get mine to work without adding the database parameter though. Didn’t pick up on the difference in quotes :o

1 Like
4

I would switch to a named query either way, but you could also do it this way
runScript('system.db.runScalarQuery', 0, "SELECT category FROM equipment WHERE serial = 'BB-001'")

5

Ah yes, that too. Gateway scope so it needs to know which database as there’s no default database config for gateway :+1:

6

Both these gave errors, the top one was a config error and the bottom one an expression error, even when adding the DB name.

The only one that worked was…

runScript('system.db.runScalarQuery', 0, "SELECT category FROM equipment WHERE serial = 'BB-001'")

I’m not sure what the ‘0’ is doing in that expression, or the single quotes around the runScalarQuery. Nothing like that is mentioned in the docs, but it works.

I’ll switch to a named query, this is just handy when you want to fire up a quick page.

Thanks all.

7

The docs do refer to it, it’s the pollrate,
https://docs.inductiveautomation.com/display/DOC81/runScript

1 Like
8

Your right, I was looking in the wrong place. :upside_down_face: I was looking at the runScalarQuery Docs

https://docs.inductiveautomation.com/display/DOC81/system.db.runScalarQuery