I need a little help with Drop Down List.
I have created a simple table with 3 columns. user_test_ndx,First,Last with a table name of ‘user_test’
In a window… I used a table component that is bound to the tabel and can view ALL the data that is in the table user_test.
I then placed a Drop Down List component on the window so I can select from one of the Users from the table. When I try to bind the drop down list from the table, I get an error that says that the column ‘Last’ does not exist. This does not make since. I am using the DB Browse to bind the table to the Drop Down List data set. Below is the SQL statement and the error that returns when the query runs.
Anyone got any ideas where I went wrong??
SELECT user_test_ndx, Last FROM user_test
java.lang.Exception: Error running query:
“SELECT user_test_ndx, Last FROM user_test”@10000ms
On: Window (2).Root Container.Dropdown.data
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:337)
at java.lang.Thread.run(Unknown Source)
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Gateway Error 500: ERROR: column “last” does not exist
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:236)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:211)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:201)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:515)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runQuery(GatewayInterface.java:482)
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$SQLQuery.execute(QueryManager.java:695)
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:335)
The thing is… created the table using a standard group. When I use the TABLE componet it show the data of all coulums in the dataset. When I use the Drop Down componet is when I get the error.
Also I have tried this on 2 different Installations of Ignition on different servers and get the same error messages. On one of the servers I named the colums ,First_Name, Last_Name, and still get the same error.
I got the same error. I just don't understand why the error says that the column does not exist.
I used the Database Query Browser and I can see the table user_test and it shows all the columns. And the column that I am trying to bind to DOES exist because I can see it. I even tried a couple other columns and had the same result [color=#FF0040]UNTIL[/color] I selected a column that was named from something not in the standard group. (Meaning...when the table was created, the system added the index column and the time stamp column) I have found that if I try to bind only to the t_stamp and/or user_test_ndx columns, it will work fine. But as soon as I try to bind it to any other column name, I get the error that that column name does not exist.
I have had similar issues with Oracle, and the problem was that Ignition creates tables with double quotes around each column name, which makes the column names case sensitive when being referenced. This means that in queries, I had to use the exact case and wrap it in double quotes, for example:
select Last_Name from mytable; --won’t work, query is looking for LAST_NAME instead of Last_Name
select “Last_Name” from mytable; --works
(this behavior also occurs with table names, if they are created in double quotes)
If I go into Oracle and alter the table and rename the column without the double quotes, everything still works fine from the Ignition side and I no longer have to use double quotes when writing queries. Also, if I define my colummn/table names with all upper-case letters in transaction groups, I usually don’t find any problems.
Anyway, it appears Postgres may exhibit similar behavior to Oracle in this regard. By default, Oracle will make everything upper-case in the absence of quotes. Looking at your error, you used Last in the select statement, and received the error Gateway Error 500: ERROR: column “last” does not exist. I think Postgres defaults to lower case. A little googling seems to back this up:
The ndx and t_stamp columns work because they are lower case, as well as columns you created manually outside of Ignition because you probably didn’t use quotes. Try wrapping the other column names in quotes or altering the table to make the column names all lower case and see if it works for you.
I’m glad I was able to help. I was about to say there should be a feature request to handle this, but I just looked in the manual and saw an option in the Database Translator for Column Quote Character. I’ll have to try that out with a blank tomorrow. The trade-off is being unable to use database keywords as column names, which I actually think is good policy anyway.
Here’s the link to the section of the manual addressing this:
