Named Query binding error

Ignition
,
1

I’ve created a Named Query with a single string parameter, and I’m trying to bind a drop-down menu in perspective to it based on the value of another drop-down in the same view:

image
image1250×685 260 KB

I’m getting an SQL error that I don’t understand, though:
image

I tested the query before using it, and it seemed to work fine. Not sure what’s causing this.

2

The props.value property of the drop down might be providing a number and not a string, I am not positive but maybe there is a chance that its not typecasting it correctly in the query. You could try casting it to a string in the expression and see if that fixes it:
toStr({../Department choose.props.value})

Not saying thats expected behavior, just a guess as it looks like it doesnt like the syntax for the “department” parameter

3

Hmm, toStr() didn’t work.
Could it be that JSON uses “foo” (double quotes) for strings, and MySQL uses ‘foo’ (single quotes) ?

1 Like
4

Potentially, you could try explicitly qualifying :department with single quotes?

5

No dice, same error occurs.
Edit: I double checked with the Database Query Browser, double quotes do not seem to be an issue with our MySQL server. Something weirder going on here…

6

Never do this with colon-marked value replacement parameters. (Or question-mark parameters in scripted "Prep" queries.) Value query parameters are delivered in binary in a secure side-channel, not in the text of the query itself.

Consider turning on query logging on the DB side so you can see the actual query submitted.

1 Like
7

@pturmel Thanks for the DB log tip.

I let it marinate in my head over the weekend and came up with a couple ideas to troubleshoot, but it seems to have resolved itself?

I’m so confused.

8

There are a couple of likely causes here:

  1. Your value is actually evaluating as a None or null value before being supplied to the Named Query.
  2. The most likely culprit is actually an idiosyncrasy of committing values through the table dialog present in your screenshot: selecting a property from the property selector does not “commit” the selected property until you hit Enter to escape the field.

I’m betting you could replicate the behavior by configuring an identical binding elsewhere but with following these steps:

  • When selecting the {../Department choose.props.value} property, click to select the property and then just leave the cursor blinking in the Value field and let the binding evaluate.
  • It’ll probably appear just like your screenshot.
  • Now press Enter while in the Value field.
  • Your binding should be fine now.