Mysql query in Expression Binding

Ignition
1

On the text field, I’m trying to use the expression binding, wherein what I want to do is

If the tag Column is equal to Initial it will do the Select Initial From TestDB else if the Column is equal to Simulated it will do Select Simulated From TestDB, how is the proper syntax for this?

Thanks

2

I think you may have to use SQL binding. I think ANSI SQL would accept ISNULL(firstfield,secondfield) but that’s assuming you use the NULL value to represent when someone is not from the initial value:

SELECT ISNULL(TagColumn,SimulatedColumn)...

Instead if you want, you can use the CASE WHEN expression:

sSQL ="SELECT CASE WHEN ColumnName = 'Initial' THEN InitialColumnName
ELSE SimulatedColumnName END from TableName"
3

If I’ve understood correctly, you have an SQLTag (of type String) which is called Column.

In the binding for your text field select SQL Query as the binding type and enter the following:

SELECT {[default]Column} FROM table_name

Obviously replace “table_name” with whatever table you need the data from and you may need a WHERE clause in there to limit it to a single value returned.

Now if the value of the SQLTag Column is “Initial” then the following query would be executed:

SELECT Initial FROM table_name

Depending on where your Column tag sits within the hierarchy you can use the Insert Tag button within the editor when doing the binding to ensure the correct tag path is entered.

4

Both of these are great answers, but I have to side with George on this one. :thumb_left:

5

I meant on the expression binding property…

for example if on script I would do

if{Column} == "Initial": system.db.runQuery("SELECT Notes FROM table_name") else: system.db.runQuery("SELECT Simulated FROM table_name")

but instead on the script, I want to do it on the Expression Binding. It’s

If(condition,trueReturn,falseReturn) right? But how do I insert the query part?

6

what’s the syntax for if, else on the expression binding?

7

Maybe this will work:

if(condition,
    runScript("system.db.runQuery('SELECT Notes FROM table_name')"),
    runScript("system.db.runQuery('SELECT Simulated FROM table_name')"))
8

Nick is almost there, but you need to make sure you’re using runScalarQuery() so the script returns a single value instead of a dataset. if(condition, runScript("system.db.runScalarQuery('SELECT Notes FROM table_name')"), runScript("system.db.runScalarQuery('SELECT Simulated FROM table_name')"))

9

how is the syntax if i want to do an if/else if I have two conditions?

like on the script i would do, but on the expression property, how should i do it?

if condition1: system.db.runQuery("SELECT Notes FROM table_name") elif condition2: system.db.runQuery("SELECT Simulated FROM table_name")

if its not possible, what can i use?

10

If statements can be nested. (The extra spacing isn’t necessary, just for clarity)if(condition1, trueReturn1, if(condition2, trueReturn2, falseReturn2 ) )

11

Thank You! I’ll try it now

12

Hmm… So the separate ‘if’ is not possible?

13

[quote=“Robert.McKenzie”]If statements can be nested. (The extra spacing isn’t necessary, just for clarity)if(condition1, trueReturn1, if(condition2, trueReturn2, falseReturn2 ) )[/quote]

it’s always returning false.

i have two conditions… is there anything else i can use? or maybe i can use the sql query,
but i have two select statements, and i still need the condition… ughhh. :scratch:

how do you suggest i do it?

14

The first if condition is evaluated, and if it returns false, then the second if condition is evaluated. If your expression is always returning the falseReturn2 value, then the flaw is in your conditions.

From the user manual:

[code]Example
You want to display a process’s current state, translating a code from the PLC to a human-readable string, use of these two expressions (they’re equivalent)
if ({CurrentProcessState} = 0, “Not Running”,
if ({CurrentProcessState} = 1, “Warmup phase - please wait”,
if ({CurrentProcessState} = 2, “Running”, “UNKNOWN STATE”)))

  • or -
    switch ({CurrentProcessState},
    0,1,2,
    “Not Running”,
    “Warmup phase - please wait”,
    “Running”,
    “UNKNOWN STATE”)[/code]
15

i got it now. thanks.

16

if (hasRole(“Supervisor”),runScript(“system.db.runQuery(‘SELECT * FROM goldinventory’)”),
if (hasRole(“Operator_Type1”),runScript(“system.db.runQuery(‘SELECT * FROM goldinventory WHERE ContainerType = 1’)”),
if (hasRole(“Operator_Type2”),runScript(“system.db.runQuery(‘SELECT * FROM goldinventory WHERE ContainerType = 2’)”),
if (hasRole(“Operator_Type3”),runScript(“system.db.runQuery(‘SELECT * FROM goldinventory WHERE ContainerType = 3’)”),""))))

When I use the above mentioned expression and run/Launch the ignition, my client station response becomes very slow and unresponsive… any idea?

17

runScript() is a terrible way to run SQL queries, due to the way it executes in the event thread. It is always better to use an intermediate (custom) property to construct part of the SQL as a string, and embed that in your full query in a normal query binding. Consider a custom property named RoleWhere with this expression:

if (hasRole("Supervisor"), "",
  if (hasRole("Operator_Type1"), "WHERE ContainerType = 1",
    if (hasRole("Operator_Type2"), "WHERE ContainerType = 2",
      if (hasRole("Operator_Type3"), "WHERE ContainerType = 3",
      "WHERE 0=1"))))

Then in your SQL Binding:

SELECT * FROM goldinventory {Root Container.RoleWhere}

Note the extra where condition that applies when no roles are present.

2 Likes