topic Collision in SQL Select-statement between special escape characters (bracket and backslash) and bracket in column name with quotation marks around it in Discussions

Collision in SQL Select-statement between special escape characters (bracket and backslash) and bracket in column name with quotation marks around it

Dape — Tue, 15 Oct 2024 09:10:39 GMT

I'm running into a problem with an SQL Select-statement using column names that are including brackets, because the end brackets of the column names end up next to backslashes (due to using quotation marks within quotation marks: " \!" column name [ units ] \!" " ) and this interferes with the surrounding special escape characters (bracket and backslash: \[ ]\ ).

The SQL-statement is written between two brackets and slashes ( \[ Select ... ]\ ) and I'm using Eval(Parse(Eval Insert())) around the whole statement to evaluate a variable (filtering by a start date).

The command is structured like this:

Eval(Parse(Eval Insert(

"dt = Open Database( settings,

\[Select \!"table1\!".\!"column_name[units]\!" ... ]\

);"

)));

As you see, the end bracket after column_name[units] ends up next to a backslash for the quotation mark around the column name, effectively making up an unwanted special escape character ( ]\ ).

I've tried using single quotation marks around the column names, and the command executed but no data was retrieved. When searching for discussion or documentation on single quotation marks I wasn't able to find any.
I've tried using the eval insert replacement string for a quation mark ( both " and \!" ) but it wasn't successful.
I've looked through several discussion on this but wasn't able to find a solution, yet.

I would be grateful for any suggestions on how this situation can be solved, what I should be trying or what I need to read up on?

Re: Collision in SQL Select-statement between special escape characters (bracket and backslash) and bracket in column name with quotation marks around it

jthi — Tue, 15 Oct 2024 11:31:19 GMT

First I would suggest that you don't utilize Eval(Parse()). I think you should be able to use ]" inside strings

Names Default To Here(1);

str = "\[abc]"]\";
write(str); // abc]"

If not then then might give some additional ideas.

Re: Collision in SQL Select-statement between special escape characters (bracket and backslash) and bracket in column name with quotation marks around it

Dape — Tue, 15 Oct 2024 12:09:11 GMT

I tried removing Eval(Parse()) and just creating the string to start with. The special escape characters are still creating a problem, it seems, or it's something else. But the problem disappears when I remove the special escape characters ( \[ ]\ ) and it also disappears when I change the brackets in the column name to parentheses.

str = "\[\!"table\!".\!"column[unit]\!"n]\";

Thanks for your response!

Re: Collision in SQL Select-statement between special escape characters (bracket and backslash) and bracket in column name with quotation marks around it

jthi — Tue, 15 Oct 2024 12:18:10 GMT

Which JMP version are you using? I don't currently have any databases on my PC so I cannot test using Open Database but I can do simple tests using JMP tables and New SQL Query()

Names Default To Here(1);

dt1 = Open("$SAMPLE_DATA/Big Class.jmp");
Column(dt1, 1) << Set Name("test[na me]");

dt = New SQL Query(
	Connection("JMP"),
	JMP Tables(["Big Class" => "$SAMPLE_DATA/Big Class.jmp"]),
	Custom SQL("\[Select "test[na me]" from "Big Class"]\")
) << Run Foreground;

Close(dt1, No save);

Re: Collision in SQL Select-statement between special escape characters (bracket and backslash) and bracket in column name with quotation marks around it

Dape — Tue, 15 Oct 2024 13:02:51 GMT

I'm working on JMP 17. I see, changing to New SQL Query probably solves these problems, I think that's a good suggestion. I'm not using it here because I'm building on some older scripts.

If there's a way to handle the string management I'm all ears for that but I think I'll try changing command.

Thank you!

Re: Collision in SQL Select-statement between special escape characters (bracket and backslash) and bracket in column name with quotation marks around it

jthi — Tue, 15 Oct 2024 13:14:50 GMT

If you are already escaping the string using "\[]\" you don't need to do it again

Names Default To Here(1);

str = "\["table"."column[unit]"n]\";
Write(str); // "table"."column[unit]"n

Re: Collision in SQL Select-statement between special escape characters (bracket and backslash) and bracket in column name with quotation marks around it

Dape — Tue, 15 Oct 2024 13:34:17 GMT

Not sure if I understand your comment, but I think it's a misunderstanding (anyway thanks for the suggestion). I'm only escaping using "\[]\" once, the second time the symbols appear it's because of the column name with a bracket ending up next to a backslash which I can't avoid using.

Re: Collision in SQL Select-statement between special escape characters (bracket and backslash) and bracket in column name with quotation marks around it

jthi — Tue, 15 Oct 2024 13:57:19 GMT

Yeah, it might be just some simple confusion as I'm basing my comments on your original script utilizing Eval(Parse()) which will then end up looking like

Eval(Parse(Eval Insert(
   "dt = Open Database( settings,
      \[Select \!"table1\!".\!"column_name[units]\!" ... ]\
   );"
)));

when it maybe should look like

Names Default To Here(1);

mydate = "1.1.2000";

dt = Open Database(settings,
    Eval Insert("\[Select "table1"."column_name[units]"... ^mydate^]\")
);

with some more separation

Names Default To Here(1);

mydate = "1.1.2000";
sql_template = "\[Select "table1"."column_name[units]"... ¤mydate¤]\";
sql_str = Eval Insert(sql_template, "¤");

dt = Open Database(settings,
    sql_str
);

https://www.jmp.com/support/help/en/18.0/index.shtml#page/jmp/jsl-syntax-rules.shtml

Re: Collision in SQL Select-statement between special escape characters (bracket and backslash) and bracket in column name with quotation marks around it

Dape — Tue, 15 Oct 2024 14:44:56 GMT

It's great to see better structuring of the code, thanks!