SQL query working in query browser, not as a binding

Ignition
1

Hey guys

I have the following query

SELECT
param_code as name,
“station_forCanvas” as template,
200+200*COS(PI()param_angle/180.0) as x,
200+200SIN(PI()*param_angle/180.0) as y,
50 as width,
50 as height,
10 as z,
CONCAT( “{”, char(34), “station”, char(34), “:” , char(34), fullpath , char(34), “}” ) as parameters
FROM
tw_data_list_stations
WHERE
id_machine = 2
AND
tw_data_list_stations.table = 1

It works great in Query Browser. However when used as a binding, I get the message “… not well formed”. I found it is linked to the usage of “{” character (want to build a dictionnary for template canvas).

Some ideas ?
It is annoying as I have to use an interim table, and complete the job with python programming. The most funny part being that it works in query browser :frowning:

thanks !:slight_smile:

2

Does it help if you use single quotes in order to be able to get rid of the char(34) in the CONCAT expression? e.g.

SELECT 
param_code as name,
'station_forCanvas' as template,
200+200*COS(PI()*param_angle/180.0) as x,
200+200*SIN(PI()*param_angle/180.0) as y,
50 as width,
50 as height,
10 as z,
CONCAT( '{"station": "', fullpath, '"}' ) as parameters
FROM
tw_data_list_stations
WHERE
id_machine = 2
AND
tw_data_list_stations.table = 1

Perhaps there is something getting confused in the binding usage? btw, you can get better syntax highlighting in here for code sections by surrounding them with three back-ticks (to the left of the 1 key):

```
code here
```

3

Standard SQL doesn’t allow double-quote characters as string delimiters at all. You must use single quotes.

4

Edit: Re-looking at this I am seeing that this is indeed using the CONCAT function in SQL. My brain was thinking that it was an Ignition expression function. Hopefully the adjusted form I posted will work then for ya… Thanks @pturmel.

5

Thanks guys.
But there is something else.

Try select “{”+“test”+"}" as mystring in query browser => works :slight_smile:

Try now to put a text in a window, and bind it to the text => “not well formed” :disappointed:

I have the impression the interpreter does not go the same route, and the { are replaced at some point.
It is very annoying (even more if you use JSON type in MySQL tables).

6

The query browser appears to be passing the SQL straight to MySQL, which does allow double-quotes for string literals. In standard SQL, double quotes are for identifiers like table names and column names that would otherwise be mangled. Consider turning on ANSI_QUOTES in your MySQL server to help enforce proper quoting.
Anywhere Ignition needs to pre-parse SQL, it’ll be using standard rules.