SQL Delimited Identifiers with double quotes

objectscriptQuality release

2.1.0

Id

OS0091

Rule type

Bug

Severity

Blocker

SQALE characteristic

Reliability
- Instruction

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
}