# Mysql query in Expression Binding

**URL:** <https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289>\
**Category:** Ignition\
**Created:** [September 19, 2013, 4:34pm UTC](https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289 "2013-09-19T16:34:20Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![cacordova](https://avatars.discourse-cdn.com/v4/letter/c/bbce88/32.png) [@cacordova](https://forum.inductiveautomation.com/u/cacordova)\
**Post date:** [September 19, 2013, 4:34pm UTC](https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289/1 "2013-09-19T16:34:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![adamaustin](https://avatars.discourse-cdn.com/v4/letter/a/5f8ce5/32.png) [@adamaustin](https://forum.inductiveautomation.com/u/adamaustin)\
**Post date:** [September 20, 2013, 1:47am UTC](https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289/2 "2013-09-20T01:47:16Z")

</div>

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:

```auto
SELECT ISNULL(TagColumn,SimulatedColumn)...
```

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

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

---

<div class="post-metadata">

**Author:** ![george](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/george/32/14066_2.png) [@george](https://forum.inductiveautomation.com/u/george)\
**Post date:** [September 20, 2013, 1:13pm UTC](https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289/3 "2013-09-20T13:13:55Z")

</div>

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:

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

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

---

<div class="post-metadata">

**Author:** ![Robert.McKenzie](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/robert.mckenzie/32/4134_2.png) [@Robert.McKenzie](https://forum.inductiveautomation.com/u/Robert.McKenzie)\
**Post date:** [September 20, 2013, 2:55pm UTC](https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289/4 "2013-09-20T14:55:16Z")

</div>

Both of these are great answers, but I have to side with George on this one. ![:thumb_left:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e26afb081ddde6df016df6ee49159cf56aaca688.gif ":thumb\_left:")

---

<div class="post-metadata">

**Author:** ![cacordova](https://avatars.discourse-cdn.com/v4/letter/c/bbce88/32.png) [@cacordova](https://forum.inductiveautomation.com/u/cacordova)\
**Post date:** [September 20, 2013, 11:03pm UTC](https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289/5 "2013-09-20T23:03:09Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![cacordova](https://avatars.discourse-cdn.com/v4/letter/c/bbce88/32.png) [@cacordova](https://forum.inductiveautomation.com/u/cacordova)\
**Post date:** [September 21, 2013, 12:36am UTC](https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289/6 "2013-09-21T00:36:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\
**Post date:** [September 22, 2013, 3:06pm UTC](https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289/7 "2013-09-22T15:06:45Z")

</div>

Maybe this will work:

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

---

<div class="post-metadata">

**Author:** ![Robert.McKenzie](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/robert.mckenzie/32/4134_2.png) [@Robert.McKenzie](https://forum.inductiveautomation.com/u/Robert.McKenzie)\
**Post date:** [September 23, 2013, 3:03pm UTC](https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289/8 "2013-09-23T15:03:32Z")

</div>

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')"))
`

---

<div class="post-metadata">

**Author:** ![cacordova](https://avatars.discourse-cdn.com/v4/letter/c/bbce88/32.png) [@cacordova](https://forum.inductiveautomation.com/u/cacordova)\
**Post date:** [September 23, 2013, 4:51pm UTC](https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289/9 "2013-09-23T16:51:15Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Robert.McKenzie](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/robert.mckenzie/32/4134_2.png) [@Robert.McKenzie](https://forum.inductiveautomation.com/u/Robert.McKenzie)\
**Post date:** [September 23, 2013, 5:11pm UTC](https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289/10 "2013-09-23T17:11:27Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![cacordova](https://avatars.discourse-cdn.com/v4/letter/c/bbce88/32.png) [@cacordova](https://forum.inductiveautomation.com/u/cacordova)\
**Post date:** [September 23, 2013, 5:15pm UTC](https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289/11 "2013-09-23T17:15:27Z")

</div>

Thank You! I’ll try it now

---

<div class="post-metadata">

**Author:** ![cacordova](https://avatars.discourse-cdn.com/v4/letter/c/bbce88/32.png) [@cacordova](https://forum.inductiveautomation.com/u/cacordova)\
**Post date:** [September 23, 2013, 5:27pm UTC](https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289/12 "2013-09-23T17:27:01Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![cacordova](https://avatars.discourse-cdn.com/v4/letter/c/bbce88/32.png) [@cacordova](https://forum.inductiveautomation.com/u/cacordova)\
**Post date:** [September 23, 2013, 5:32pm UTC](https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289/13 "2013-09-23T17:32:21Z")

</div>

[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:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/76d1620bec252bfac07050c889ff5a91dc8a9c25.gif ":scratch:")

how do you suggest i do it?

---

<div class="post-metadata">

**Author:** ![Robert.McKenzie](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/robert.mckenzie/32/4134_2.png) [@Robert.McKenzie](https://forum.inductiveautomation.com/u/Robert.McKenzie)\
**Post date:** [September 23, 2013, 5:38pm UTC](https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289/14 "2013-09-23T17:38:26Z")

</div>

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](http://www.inductiveautomation.com/support/usermanuals/ignition/expression_binding.htm):

[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]

---

<div class="post-metadata">

**Author:** ![cacordova](https://avatars.discourse-cdn.com/v4/letter/c/bbce88/32.png) [@cacordova](https://forum.inductiveautomation.com/u/cacordova)\
**Post date:** [September 23, 2013, 5:47pm UTC](https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289/15 "2013-09-23T17:47:27Z")

</div>

i got it now. thanks.

---

<div class="post-metadata">

**Author:** ![leo\_amm](https://avatars.discourse-cdn.com/v4/letter/l/9d8465/32.png) [@leo\_amm](https://forum.inductiveautomation.com/u/leo_amm)\
**Post date:** [October 13, 2017, 2:09am UTC](https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289/16 "2017-10-13T02:09:22Z")

</div>

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?

---

<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 13, 2017, 12:25pm UTC](https://forum.inductiveautomation.com/t/mysql-query-in-expression-binding/7289/17 "2017-10-13T12:25:37Z")

</div>

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:

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

```auto
SELECT * FROM goldinventory {Root Container.RoleWhere}

```

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