# SQL Query Error?

**URL:** <https://forum.inductiveautomation.com/t/sql-query-error/25478>\
**Category:** Ignition\
**Created:** [May 31, 2019, 9:33pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478 "2019-05-31T21:33:33Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![bchristenson](https://avatars.discourse-cdn.com/v4/letter/b/ecccb3/32.png) [@bchristenson](https://forum.inductiveautomation.com/u/bchristenson)\
**Post date:** [May 31, 2019, 9:33pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/1 "2019-05-31T21:33:33Z")

</div>

I have a little problem, I have a table called Travis with recipe data in it. I have a recipe select pull down list that is connected to the SQL database. When I have a recipe selected I have a button I want to push that will load data from the selected row in to tags in my PLC. I am getting an error:

Parse error for event handler “actionPerformed” SyntaxError: (“no viable alternative at input ‘Density’”, (’ ', 4, 49, ‘“RecipeTags/Density” = system.db.runQuery(select Density from Travis where RecipeName = ‘recipeselected’)\n’))

My code is this:

```auto
#	Set row that I want to pull data from
recipeselected = event.source.parent.getComponent('Recipe Select').selectedStringValue
#	Run Query to grab data and set it to an memory tag
"RecipeTags/Density" = system.db.runQuery(select Density from Travis where RecipeName = 'recipeselected')
#	Set Memory Tag equal to my PLC Tag
"Controller:Global/Ingredient/Ingredient_0_" = "RecipeTags/Density"

```

I am not sure why it is giving me this error when looking up a value. this is the same call as my binding on another screen. Any help would be helpful.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [May 31, 2019, 10:15pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/2 "2019-05-31T22:15:13Z")

</div>

You’ve got a few syntax errors (assuming the forum didn’t mangle your code somehow)

First, on line 2, `"RecipeTags/Density" =` is trying to assign (the `=` operator) something to a literal string. If you’re trying to define a variable, leave _out_ the double quotes. Meanwhile, in system.db.runQuery, you have the opposite problem - `system.db.runQuery(select Density from Travis where RecipeName = 'recipeselected')` is trying to look for variables called `select`, `Density`, etc, which doesn’t work. You need to create a `RecipeTags/Density` variable, but `/` is not a legal Python identifier, so swap it for an underscore or similar. Then wrap the actual SQL query in double quotes because it **is** a literal string:  
`RecipeTags_Density = system.db.runQuery("select Density from Travis where RecipeName = 'recipeselected'")`  
Python lets you use either single or double quotes to identify strings, which is useful.

Finally, the code on the last line is doing literally nothing - we have no way of knowing that `"Controller:Global/Ingredient/Ingredient_0_"` is meant to be a tag - you’re just defining a string. To write to a tag, use `system.tag.write()` (or `system.tag.writeBlocking`, in 8.0, although `write` will continue to work), like this:  
`system.tag.write("Controller:Global/Ingredient/Ingredient_0_", RecipeTags_Density)`  
The first argument to `system.tag.write()` is the _path_ to the tag - this can be another variable, OR a literal string. `RecipeTags_Density` is a reference to the variable defined in the above code (assuming you’ve made the changes I pointed out).

---

<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:** [May 31, 2019, 10:16pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/3 "2019-05-31T22:16:18Z")

</div>

You need to use system.tag.write() and friends. Putting a tag name into a string on the left side of an assignment is not valid python.

---

<div class="post-metadata">

**Author:** ![bchristenson](https://avatars.discourse-cdn.com/v4/letter/b/ecccb3/32.png) [@bchristenson](https://forum.inductiveautomation.com/u/bchristenson)\
**Post date:** [June 3, 2019, 2:56pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/4 "2019-06-03T14:56:26Z")

</div>

PGriffith,

Your suggested changes worked. But now when I run the script it fails. My code as I have rewritten it:

# Set row that I want to pull data from

recipeselected = event.source.parent.getComponent(‘Recipe Select’).selectedStringValue

# Run Query to grab data and set it to an memory tag

RecipeTags\_Density = system.db.runQuery(“select Density from Travis where RecipeName = ‘recipeselected’”)

# Set Memory Tag equal to my PLC Tag

system.tag.write(“Controller:Global/Ingredient/Ingredient\_0\_”, RecipeTags\_Density)

The error message that is now coming up when ever I run the script:

Traceback (most recent call last):

File “event:actionPerformed”, line 4, in

java.lang.Exception: java.lang.Exception: Error executing system.db.runQuery(SELECT Density FROM Travis WHERE RecipeName = ‘recipeselected’, , )

```
caused by Exception: Error executing system.db.runQuery(SELECT Density FROM Travis WHERE RecipeName = 'recipeselected', , )
caused by GatewayException: Table 'test.travis' doesn't exist
caused by MySQLSyntaxErrorException: Table 'test.travis' doesn't exist

```

Ignition v7.9.10 (b2018112821)  
Java: Oracle Corporation 1.8.0\_201

I am not sure why it is looking for a table name of “test.travis” I have it defined correctly as just “Travis”.

I also know that my connection to the database is working correctly because I can see the data in the table Travis.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 3, 2019, 3:26pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/5 "2019-06-03T15:26:46Z")

</div>

Do you have the correct database/schema specified in your MySQL connection URL?

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/1/1b862390c24dce30777f15a224f1b4bbe867bc3d.png)  
`/test` should be the appropriate schema in your database.

---

<div class="post-metadata">

**Author:** ![bchristenson](https://avatars.discourse-cdn.com/v4/letter/b/ecccb3/32.png) [@bchristenson](https://forum.inductiveautomation.com/u/bchristenson)\
**Post date:** [June 3, 2019, 3:34pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/6 "2019-06-03T15:34:28Z")

</div>

Yes the database as far as I know is set up correctly. I have been able to put data into the database and pull information out.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/6/6aabd9e915becd0381ade69c0df554c8a9160ab5.png)

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/23e9224dde4c2cb7095c63c0c690335552eca604.png)

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 3, 2019, 3:35pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/7 "2019-06-03T15:35:37Z")

</div>

> [@bchristenson](#):
>
> caused by MySQLSyntaxErrorException: Table 'test.travis' doesn't exist

Something's not lining up - your error message is indicating a connection configured using MySQL, but your connect URL and DB settings are indicating Microsoft SQL Server.

---

<div class="post-metadata">

**Author:** ![bchristenson](https://avatars.discourse-cdn.com/v4/letter/b/ecccb3/32.png) [@bchristenson](https://forum.inductiveautomation.com/u/bchristenson)\
**Post date:** [June 3, 2019, 3:40pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/8 "2019-06-03T15:40:10Z")

</div>

Yes I have been using the Microsoft SQL throughout this testing phase.

This is the database with my test data in it.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/8/8b791e72de6a1368ded01d0f1da87b59223efa91.png)

I have been able to put data in and out of the database. Now when I want to run the script that will look at which recipe name I have selected and then put the information in that row in to the proper PLC tags is where I have run into this error.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 3, 2019, 3:41pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/9 "2019-06-03T15:41:13Z")

</div>

Okay, then I would check your project properties in the designer and make sure you have the correct database connection selected as the ‘default database’.

---

<div class="post-metadata">

**Author:** ![bchristenson](https://avatars.discourse-cdn.com/v4/letter/b/ecccb3/32.png) [@bchristenson](https://forum.inductiveautomation.com/u/bchristenson)\
**Post date:** [June 3, 2019, 3:45pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/10 "2019-06-03T15:45:40Z")

</div>

Okay, changing the default database worked. No the issue is I am guessing when I am pulling this data out of the SQL database it is converting to a string. In the last line where I move it into the PLC tag it is trying to write a string into a real. I am getting the error:

Error writing to tag Controller:Global/Ingredient/Ingredient\_0\_ Invalid data type, unable to convert value ’ ’ to data type Float4

Is there a way to convert the string into a real before I send it to my PLC tag?

---

<div class="post-metadata">

**Author:** ![bchristenson](https://avatars.discourse-cdn.com/v4/letter/b/ecccb3/32.png) [@bchristenson](https://forum.inductiveautomation.com/u/bchristenson)\
**Post date:** [June 3, 2019, 4:01pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/11 "2019-06-03T16:01:07Z")

</div>

Now I am really confused. I added a float() argument to the script before I move it into my PLC tag and I am getting the error message:

Traceback (most recent call last):

File “event:actionPerformed”, line 6, in

TypeError: float() argument must be a string or a number

Ignition v7.9.10 (b2018112821)  
Java: Oracle Corporation 1.8.0\_201

My code as of now:

# Set row that I want to pull data from

recipeselected = event.source.parent.getComponent(‘Recipe Select’).selectedStringValue

# Run Query to grab data and set it to an memory tag

RecipeTags\_Density = system.db.runQuery(“select Density from Travis where RecipeName = ‘recipeselected’”)

# Take String and convert to Real

float(RecipeTags\_Density)

# Set Memory Tag equal to my PLC Tag

system.tag.write(“Controller:Global/Ingredient/Ingredient\_0\_”, RecipeTags\_Density)

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 3, 2019, 4:03pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/12 "2019-06-03T16:03:39Z")

</div>

> [@bchristenson](#):
>
> `select Density from Travis where RecipeName = 'recipeselected'`

Will this always return a single value? If so, you can switch to [`system.db.runScalarQuery`](https://docs.inductiveautomation.com/display/DOC80/system.db.runScalarQuery), which will return a single value (rather than a dataset/pydataset) which should match the type of the value in the database (which, if it's numeric, should be able to write directly to the tag).

The error you're currently getting (`TypeError: float() argument must be a string or a number`) is because `system.db.runQuery` is returning a pydataset containing your results (even if there's only one) - try `print RecipeTags_Density` to see the value. Switching to `runScalarQuery` should work.

---

<div class="post-metadata">

**Author:** ![bchristenson](https://avatars.discourse-cdn.com/v4/letter/b/ecccb3/32.png) [@bchristenson](https://forum.inductiveautomation.com/u/bchristenson)\
**Post date:** [June 3, 2019, 4:13pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/13 "2019-06-03T16:13:47Z")

</div>

Once I changed to runScalarQuery. I am now getting this error:

Error writing to tag Controller:Global/Ingredient/Ingredient\_0\_ The value supplied for the attribute is not of the same type as the attribute’s value.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/297eb6e546db780bd5bbe8643c64c6e6752926d7.png)

If the runScalarQuery is returning a float value then it should be able to go into this tag as it is set up correct?

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 3, 2019, 4:38pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/14 "2019-06-03T16:38:25Z")

</div>

Try adding `print RecipeTags_Density, type(RecipeTags_Density)` to your script, before the system.tag.write() call.

---

<div class="post-metadata">

**Author:** ![bchristenson](https://avatars.discourse-cdn.com/v4/letter/b/ecccb3/32.png) [@bchristenson](https://forum.inductiveautomation.com/u/bchristenson)\
**Post date:** [June 3, 2019, 4:42pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/15 "2019-06-03T16:42:01Z")

</div>

Like this I get the same error.

# Set row that I want to pull data from

recipeselected = event.source.parent.getComponent(‘Recipe Select’).selectedStringValue

# Run Query to grab data and set it to an memory tag

RecipeTags\_Density = system.db.runScalarQuery(“select Density from Travis where RecipeName = ‘recipeselected’”)

print RecipeTags\_Density, type(RecipeTags\_Density)

# Set Memory Tag equal to my PLC Tag

system.tag.write(“Controller:Global/Ingredient/Ingredient\_0\_.”, RecipeTags\_Density)

Error:  
Error writing to tag Controller:Global/Ingredient/Ingredient\_0\_ The value supplied for the attribute is not of the same type as the attribute’s value.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 3, 2019, 4:45pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/16 "2019-06-03T16:45:54Z")

</div>

Yes, the print statement won’t fix the error, but check the console (Tools -\> Output Console) to see what it’s printing out.

---

<div class="post-metadata">

**Author:** ![bchristenson](https://avatars.discourse-cdn.com/v4/letter/b/ecccb3/32.png) [@bchristenson](https://forum.inductiveautomation.com/u/bchristenson)\
**Post date:** [June 3, 2019, 4:49pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/17 "2019-06-03T16:49:31Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/a/ab9d7684a3704108a355af2235bfe15ae4347bbe.png)

Is what the console tells me.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 3, 2019, 5:06pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/18 "2019-06-03T17:06:24Z")

</div>

Okay, so your SQL query is returning _nothing_, which can’t be written to the tag (how would you write a _null_ value to the PLC?) - so, what you need to do now is identify why that SQL query isn’t returning anything for the values/where clause you gave it.

---

<div class="post-metadata">

**Author:** ![bchristenson](https://avatars.discourse-cdn.com/v4/letter/b/ecccb3/32.png) [@bchristenson](https://forum.inductiveautomation.com/u/bchristenson)\
**Post date:** [June 3, 2019, 6:31pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/19 "2019-06-03T18:31:09Z")

</div>

This is what I am basing the script on.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/f/f2fec87d5b9da0a50bddf77da23d995c11f5c219.png)  
I Display the value in a numeric display field.

My quick fix for this has been to take this value and then set it equal to the PLC tag. I was hoping there would be a way to not have to set the value in a numeric display and then set that equal to the PLC tag.

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [May 16, 2020, 6:26pm UTC](https://forum.inductiveautomation.com/t/sql-query-error/25478/20 "2020-05-16T18:26:37Z")

</div>

Late to the party, but..  
Your sql query is returning nothing, probably because your sql query isn't dynamic, and is looking for a where condition that doesn't find any results.

> [@bchristenson](#):
>
> RecipeTags\_Density = system.db.runScalarQuery(“select Density from Travis where RecipeName = ‘recipeselected’”)

This is a static query; its result will never change. To make it _dynamically_ look for your selected recipe, you have to provide the variable into the string.

`RecipeTags_Density = system.db.runScalarQuery(“select Density from Travis where RecipeName = ‘%s’” % (recipeselected))`