Using blank values with named queries

My page has a few input boxes and a table. A user can enter criteria into the input boxes and the table uses that criteria to search the database and then displays the results; however, since converting to a named query I am getting errors saying that there are no values for the parameter(s). Example: (Missing value for query parameter {partNumber}).

The input box is empty, which is a totally valid option in my design.

How can I tell the named query to ignore this and to treat the input as nothing?

I’m hoping to move things over to named queries, but if there’s not an easy solution for this I’ll keep doing things the old fashion way!

Thanks.

Nothing that you’re doing wrong - unfortunately, this is just an oversight on our part. It’s on the backlog, but hasn’t yet been fixed in a particular release.

Okay. Thanks for the info.

Guess that means that I’ll revert back to the old style for now (for this table at least), and keep an eye out for the fix! Thanks.

On a somewhat related note, named queries seem to dislike concatenation with a space between fields. For example, the following query works.

SELECT CONCAT (userFirstName, userLastName)
FROM user

However, this query does not work.

SELECT CONCAT (userFirstName, " ", userLastName)
FROM user

I get this error.

GatewayException: Unable to read response from Gateway.
	caused by org.xml.sax.SAXParseException; lineNumber: 4; columnNumber: 41; Element type "column" must be followed by either attribute specifications, ">" or "/>".


Ignition v7.9.7 (b2018032914)
Java: Oracle Corporation 1.8.0_172

Standard SQL doesn’t allow double-quotes to be used for string constants. You must use single quotes. Double quotes are reserved for table names, column names, or other structural names in the query.

Interesting… I’ve used MySQL and MS SQL Server and have never had a problem using double quotes before. Thanks.

Both MySQL and SQL Server had existing syntax with other delimiters (back-quote and square brackets, respectively) for identifiers when that part of the ANSI SQL standard was finalized. Both products have a syntax compatibility mode you can turn on, if future-proofing your platform is important to you.

1 Like

Have there been any updates to @Stuart 's question? I have a project started that sounds similar to his situation in which a user is provided with a text box to perform a serial number lookup from a table. If the text box is empty, I would like to return all the serial numbers from within the table.

Named queries properly handle null parameters as of Ignition 7.9.10 - so, within a few months, once QA has run through their testing.

Thanks for the heads up!