- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Collision in SQL Select-statement between special escape characters (bracket and backslash) and bracket in column name with quotation marks around it
- 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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: 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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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); // "table"."column[unit]"n
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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]\!" ... ]\
);"
)));
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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!