Written by

Clinical Product Manager at InterSystems

STAFF

Question Jonathan Wald · Oct 28, 2023

escape bracket in SQL

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)0

Add reply

Comments

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

Robert Cemper · Oct 28, 2023

Are you sure the 2nd parameter of the stored procedure is of type %String(MAXLEN="") without length limit?

0 0

Jonathan Wald · Oct 28, 2023

this screenshot shows the function working (without brackets).

This shows the error when [0] is added (or [[0]] )

0 0

Jonathan Wald Oct 28, 2023 to Jonathan Wald

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.

0 0

Robert Cemper Oct 28, 2023 to Jonathan Wald

Congrats!
I suspected the error to be outside SQL
👍

0 0