805548_en-US

liliconcom

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

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?

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

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 How do you replace square brackets "[" "]" in a string with Regex? 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

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

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

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

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);&nbsp;//&nbsp;"table"."column[unit]"n<BR />

 

 

 

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

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

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]\!" ... ]\
   );"
)));

</CODE>when it maybe should look like</P> <PRE><CODE class=" language-jsl">Names Default To Here(1); mydate = "1.1.2000"; dt = Open Database(settings, Eval Insert("\[Select "table1"."column_name[units]"... ^mydate^]\") );

</CODE>with some more separation</P> <PRE><CODE class=" language-jsl">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

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