DropDown List SQL Query Binding Question

[McLendon_Automation]

I have used the following SQL Query Binding in many DropDown List Components (In Vision) to populate the DropDown List (Data) for user selection. The project I am currently working on is Ignition Version 8.1.48

I get the following error msg when using it now.

I am curious why this worked in older versions and now does not seem to function. Or what did I miss that would make this binding function?

[ryan.white]

Whatever table you are selecting from doesn’t have a column called No Selection. If you are trying to just have ‘No Selection’ as text in the second column, wrap it in single quotes ’ ’. Double quotes ” “ in SQL denote identifiers, such as table or column names.

[McLendon_Automation]

I tried the single quotes → ‘No Selection’ and then received the following error msg.

This isn’t currently affecting the project I am working on it is just an annoyance since it has worked for me so many times before. I did realize that I am now working with SQL Server and all me previous projects were in MySQL. Could that be the difference?

[ryan.white]

@McLendon_Automation

previous projects were in MySQL

Possibly, I don’t know how closely MySQL follows the spec.

In MSSQL, UNIONs require the columns in the same positions to be similar types. Try casting batch to a NVARCHAR in the ‘Label’ column

try
SELECT batch as Value, CAST(batch as nvarchar) as Label
instead of
SELECT batch as Value, batch as Label

Edit: Just looked it up, MySQL appears to allow double quotes to be used to denote strings, and uses back quotes to denote identifiers.

MySQL :: MySQL 8.4 Reference Manual :: 11.1.1 String Literals

[David_Stone]

MySQL has more standard syntax, MSSQL has customised and made so much of its query language non-standard that it’s very time consuming switching a nice working Ignition project between the two. Postgres to MySQL is a couple of minutes normally.

[ryan.white]

@David_Stone

MySQL has more standard syntax, MSSQL has customised

This is true for the majority of stuff, but even Postgres uses double quotes for identifiers, so looks like MySQL is the odd one out here in this case.

4.1. Lexical Structure

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier, never a key word.

[McLendon_Automation]

@ryan.white

Possibly, I don’t know how closely MySQL follows the spec.

In MSSQL, UNIONs require the columns in the same positions to be similar types. Try casting batch to a NVARCHAR in the ‘Label’ column

try
SELECT batch as Value, CAST(batch as nvarchar) as Label
instead of
SELECT batch as Value, batch as Label

Edit: Just looked it up, MySQL appears to allow double quotes to be used to denote strings, and uses back quotes to denote identifiers.

This makes sense now. After changing the double quote “No Selection” to single quotes, ‘No Selection’ I jumped to the conclusion that the error was still flagging the ‘No Selection’ part of the query. I tried Cast(batch as nvarchar) AS Label and received the same error msg, converting varchar to bigint. Here is another wrinkle in my first question. The SQL Query that is installed and currently functioning is the following →

Why does this one convert the bigint to varchar and as soon as I try to display some defaults like the UNION with the ‘%"‘ and ‘No Selection’ throw an error?

I’m an old man and learning new things is still fun.

[ryan.white]

@McLendon_Automation

Why does this one convert the bigint to varchar

Technically in the simpler SQL, the SQL is NOT converting anything to nvarchar. The data is being provided back as BIGINT, and java(Ignition) uses it as such. There might be some java stringify happening to make the selected label value in the dropdown a string, but that’s all on Ignition side.

Regarding the Union, I just realized you need to cast BOTH columns as NVARCHAR. % is not a valid integer value. So you would need: SELECT CAST(batch as nvarchar) as Value, CAST(batch as nvarchar) as Label

I’m guessing MySQL would look at the union columns and determines which type worked for both columns, hence no explicit casting.

[pturmel]

The SQL standard calls for double quotes around identifiers, and single quotes around values.

Out of the box, MSSQL and MySQL and MariaDB use other characters for identifier quotes, but can be configured to use double quotes. PostgreSQL and Oracle follow the standard.

[McLendon_Automation]

@ryan.white

Technically in the simpler SQL, the SQL is NOT converting anything to nvarchar. The data is being provided back as BIGINT, and java(Ignition) uses it as such. There might be some java stringify happening to make the selected label value in the dropdown a string, but that’s all on Ignition side.

Regarding the Union, I just realized you need to cast BOTH columns as NVARCHAR. % is not a valid integer value. So you would need: SELECT CAST(batch as nvarchar) as Value, CAST(batch as nvarchar) as Label

I’m guessing MySQL would look at the union columns and determines which type worked for both columns, hence no explicit casting.

Thanks again ryan.white

I appreciate your help. I only have three customers that use ignition and sometimes I have long periods where I don’t get to work with it. I’m just old enough that I forget… everthing.