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:
# 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.
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).
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.
1 Like
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.
Do you have the correct database/schema specified in your MySQL connection URL?
/test should be the appropriate schema in your database.
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.
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.
Yes I have been using the Microsoft SQL throughout this testing phase.
This is the database with my test data in it.
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.
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’.
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?
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)
Will this always return a single value? If so, you can switch to 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.
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.
If the runScalarQuery is returning a float value then it should be able to go into this tag as it is set up correct?
Try adding print RecipeTags_Density, type(RecipeTags_Density) to your script, before the system.tag.write() call.
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.
Yes, the print statement won’t fix the error, but check the console (Tools -> Output Console) to see what it’s printing out.
Is what the console tells me.
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.
This is what I am basing the script on.
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.
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.
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))
