SQL Delimited Identifiers with double quotes

objectscriptQuality release 
2.1.0
Id 
OS0091
Rule type 
Bug
Severity 

Blocker

Blocker
SQALE characteristic 
  • Reliability
    • Instruction
Tags 
bug, runtime-failure
Remediation function 
Constant/issue
Remediation cost 
10min

Cannot use double quotes ("") within any SQL statement.

Non-compliant Code Example for SQL.Statement

Set stmt = ##CLASS(%SQL.Statement).%New()
Set query = "Select Val1, Val2 FROM Table WHERE Val1=""SomeCondition"""
set tStatus =  stmt .%Prepare(query)
    

Compliant Solution for SQL.Statement

Set stmt = ##CLASS(%SQL.Statement).%New()
Set query = "Select Val1, Val2 FROM Table WHERE Val1='SomeCondition'"
set tStatus =  stmt .%Prepare(query)
    

Non-compliant Code Example for embedded SQL

On next code, the use of delimited identifiers causes the condition to error because you cannot use a double quote in SQL.

&SQL(SELECT Val1, Val2
                INTO :val1, :val2
                FROM Table
                WHERE Val1="SomeCondition")
    

Compliant Solution for embedded SQL

&SQL(SELECT Val1, Val2
                INTO :val1, :val2
                FROM Table
                WHERE Val1='SomeCondition')
    

Non-compliant Code Example

There exists an additional case in which double quotes are allowed but coding guide lines dismmiss its usage in favour to single quotes, as shown below.

Query Example() As %SQLQuery(CONTAINID = 1, ROWSPEC = "ID,Corporation:%Integer,IDNumber:%Integer,Name:%String, DisplayName:%String")
{
  SELECT ID, Corporation->CorporationID, IDNumber, Name, (IDNumber || " - " || Name) As
    FROM General.Contacts
  ORDER BY Name
}
    

Compliant Solution

Query Example() As %SQLQuery(CONTAINID = 1, ROWSPEC = "ID,Corporation:%Integer,IDNumber:%Integer,Name:%String, DisplayName:%String")
{
  SELECT ID, Corporation->CorporationID, IDNumber, Name, (IDNumber || ' - ' || Name) As
    FROM General.Contacts
  ORDER BY Name
}