I am trying to use a string from the PLC in a where clause to update a table, but receive this error:
‘Conversion from string “GLOBAL” to type ‘Integer’ is not valid.’
I have the column named STATION and is the text/varchar type. The PLC tag is named STATION as well and FSQL is correctly reading the string.
I have tried several formats for the where clause, such as STATION = {STATION}, STATION = ‘{STATION}’, or STATION LIKE {STATION} but none fix the error.
Any ideas? Thanks.
Hmm… so as a quick sanity check, the group works correctly when set to insert rows or update the first row of a test table?
I assume “GLOBAL” is the current value of your STATION tag, but it would be very odd if it weren’t.
What version of FSQL are you using? What database?
I did a quick test with mysql and the current (4.1.3) FSQL, and I was able to get it to work with col = ‘{refitem}’
HOWEVER, I did notice a bug: If you edit the where clause and then immediately hit “start”, it will often revert to the previous value. That is, it’s not saving the where clause correctly in this case. To get around it, edit the where clause, click off of the group (causing it to save the group), and click back on. You should now be good to go. Could be that in some of your tests you weren’t actually testing what you thought you were.
Hope this helps,
For your sanity check, the group does work correctly when inserting. When I set it to update (any type), I get the error again.
Yes, GLOBAL was the value of the STATION tag.
I am using FSQL 4.3.10 and a MS Access database (2000 format).
I didn’t see any occasion where that bug might have caused a problem, but I don’t think it is contributing to the error since it happens when I set the group to update (any type of update)
Also as another note, I have FSQL set to automatically create the table, and the OPC items are the correct data types. When I check the table it has correct data types.
Edit: Here is the whole error message if it helps
Conversion from string "GLOBAL" to type 'Integer' is not valid.
at Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(String Value)
at Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(Object Value)
at FactorySQL.Groups.StandardGroup.DBItemExecutionCollection.BuildWhereClause()
at FactorySQL.Groups.StandardGroup.DBItemExecutionCollection.ExecuteItems(EvaluationToken Token)
at FactorySQL.Groups.FSQLGroup.InternalExecute(EvaluationToken EvalToken)
Input string was not in a correct format.
at Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(String Value)
at Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(Object Value)
at FactorySQL.Groups.StandardGroup.DBItemExecutionCollection.BuildWhereClause()
at FactorySQL.Groups.StandardGroup.DBItemExecutionCollection.ExecuteItems(EvaluationToken Token)
at FactorySQL.Groups.FSQLGroup.InternalExecute(EvaluationToken EvalToken)
Ok, sounds like things are generally correct. However, the fact that you can’t tell it to update the first row indicates an issue with the table index, and sure enough I was able to replicate your exact error message by changing the table’s index to a string column.
If FactorySQL created the table, there should be an autoincrementing column called tablename_ndx (with your tablename instead). It would be an integer. If you then click on Options right above where you specify the table name, and go to Column Assignments, that column should be selected as index.
It’s ok to use a different column as the index, but currently there is a requirement that it be an integer value.
Let me know if this fixes it.
Regards,
I was trying to use the text/varchar column as the table index and once I switched it back to the integer, it works.
Thanks!