Caché Transact-SQL (TSQL) Migration Guide
TSQL Settings
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Settings are used to tailor the behavior of the compiler and colorizer. The TSQL configuration options are part of the standard Caché configuration.

Caché supports the following TSQL settings:
These values are used to set the corresponding ^%SYS("tsql","SET",...) global array values.
For further details, see TSQL Compatibility Settings in the Caché Additional Configuration Settings Reference.
You can view and modify these settings using the Caché Management Portal or %SYSTEM.TSQL class methods.
DIALECT
The DIALECT configuration option allows you to select the Transact-SQL dialect. The available options are Sybase and MSSQL. The default is Sybase. This option is set using the Caché Management Portal.
If DIALECT=MSSQL: a DECLARE statement binds host variable values.
If DIALECT=Sybase: host variable values are refreshed for each cursor OPEN.
ANSI_NULLS
The ANSI_NULLS configuration option allows you to specify whether comparisons to a null value return true or false. The default is OFF.
You can determine the current ANSI_NULLS setting using %SYSTEM.TSQL class methods, or from the TSQLAnsiNulls property, as follows:
  SET context=##class(%SYSTEM.Context.SQL).%New()
  WRITE "ANSI_NULLS is = ",context.TSQLAnsiNulls
 
You can activate (ON) or deactivate (OFF) ANSI_NULLS systemwide using either of the following ObjectScript commands:
  WRITE ##class(%SYSTEM.TSQL).SetAnsiNulls("Sybase","OFF")
  SET ^%SYS("tsql","SET","ANSI_NULLS")="OFF"
CASEINSCOMPARE
The CASEINSCOMPARE setting specifies non-case-sensitive equality comparisons, such as 'A'='a'. The default is OFF. If this option is set to ON, the comparison operators = and <> operate without regard to case in most contexts. However, there are a few contexts where such insensitivity does not apply:
These exceptions exist because Caché SQL does not accept the %SQLUPPER operator in these contexts.
You can determine the current CASEINSCOMPARE setting using %SYSTEM.TSQL class methods, or from the TSQLCaseInsCompare property, as follows:
  SET context=##class(%SYSTEM.Context.SQL).%New()
  WRITE "ANSI_NULLS is = ",context.TSQLCaseInsCompare
 
You can activate (ON) or deactivate (OFF) CASEINSCOMPARE systemwide using either of the following ObjectScript commands:
  WRITE ##class(%SYSTEM.TSQL).SetCaseInsCompare("Sybase","OFF")
  SET ^%SYS("tsql","SET","CASEINSCOMPARE")="OFF"
QUOTED_IDENTIFIER
The QUOTED_IDENTIFIER configuration option allows you to select whether quoted identifiers are supported. The default is OFF (not supported). This option is set using the Caché Management Portal. When QUOTED_IDENTIFIER is on, double quotes are parsed as delimiting an identifier. When QUOTED_IDENTIFIER is off, double quotes are parsed as alternative delimiters for string literals. The preferable delimiters for string literals are single quotes.
You can determine the current QUOTED_IDENTIFIER setting using %SYSTEM.TSQL class methods, or from the TSQLQuotedIdentifier property, as follows:
  SET context=##class(%SYSTEM.Context.SQL).%New()
  WRITE "ANSI_NULLS is = ",context.TSQLQuotedIdentifier
 
You can activate (ON) or deactivate (OFF) QUOTED_IDENTIFIER systemwide using either of the following ObjectScript commands:
  WRITE ##class(%SYSTEM.TSQL).SetQuotedIdentifier("Sybase","OFF")
  SET ^%SYS("tsql","SET","QUOTED_IDENTIFIER")="OFF"
TRACE
The TRACE configuration option is not available from the Management Portal. It controls the behavior of the TSQL compiler. When a method is compiled with TRACE active, running this method will log traced messages to the active log file, by default. The active log file is located in the same namespace as CACHE.DAT, and is named using the current process number.
You can activate (ON) or deactivate (OFF) TRACE systemwide using the following ObjectScript command:
  SET ^%SYS("tsql","SET","TRACE")="ON"
The default value is ON (TRACE active).