New post
Question
· Oct 28, 2023

escape bracket in SQL

#Global Masters #InterSystems IRIS

I've written a stored procedure for SQL as follows. The second parameter is a search path that may use a single quote or bracket as part of its expression.

When the expression uses a single quote, I can double it within SQL, and it works well. In this example, 'testDate' is written as ' ' testDate ' ' .

 

Select HISOL_MEAS.SQLProc_JSONpath(fullAnnotation,'HbA1cTests.sort(''testDate'',false).resultValue') As reverseSortedTests

 

In the next example I'd like to use brackets (see below), which causes an error.  

Trying to escape the bracket by doubling it still produces an error.  Any suggestions how I might accomplish this?

 

'HbA1cTests.sort(''testDate'',false)[0].resultValue' As recentHbA1c

'HbA1cTests.sort(''testDate'',false)[[0]].resultValue' As recentHbA1c

Product version: IRIS 2022.1
$ZV: 2022.1.2 (Build 574_0_22407U)
Discussion (5)1
Log in or sign up to continue
Robert Cemper · Oct 28, 2023

ERROR: - OK

which error ??   pls. be specific with all details

hint for SQL Escaping

but test in SQL Shell shows no problem
 

USER>do $system-.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
 
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
USER>>select 'HbA1cTests.sort(''testDate'',false)[0].resultValue'
1.      select 'HbA1cTests.sort(''testDate'',false)[0].resultValue'
 
HostVar_1
HbA1cTests.sort('testDate',false)[0].resultValue
 
1 Rows(s) Affected
statement prepare time(s)/globals/lines/disk: 0.0002s/5/166/0ms
          execute time(s)/globals/lines/disk: 0.0002s/0/385/0ms
                          cached query class: %sqlcq.USER.cls117
---------------------------------------------------------------------------
USER>>
0 0
Jonathan Wald · Oct 28, 2023

hi robert - - i think I found the issue.....

the problem is not in the use of the bracket, but rather, that an empty set (taking the 0th element of the empty set) is leading to the error.  

If I add to the where class, 'where runid='201' and HbA1cMissing not like '1', I don't see an error.

Thank you for prompting me to take a closer look at the error. 

1 0