TSQL Settings

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:

DIALECT (default is Sybase)
ANSI_NULLS
CASEINSCOMPARE (String comparison is not case-sensitive.)
QUOTED_IDENTIFIER
TRACE

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.TSQLOpens in a new tab class methods.

Go into the Caché Management Portal. Go to System Administration, Configuration, SQL and Object Settings, TSQL Compatibility. Here you can specify the DIALECT (Sybase or MSSQL, default is Sybase), and turn on or off the ANSI_NULLS, CASEINSCOMPARE, and QUOTED_IDENTIFIER settings.
If you change one or more configuration options, the TSQL Settings heading will be followed by an asterisk, indicating that changes have been made but not yet saved. You must press the Save button for configuration changes to take effect.
Invoke the $SYSTEM.TSQL.CurrentSettings()Opens in a new tab method to display the settings:
```
  DO ##class(%SYSTEM.TSQL).CurrentSettings()
```
You can use %SYSTEM.TSQLOpens in a new tab class methods to get or set the ANSI_NULLS, CaseInsCompare, and Quoted_Identifier settings. While these methods take a dialect string, there are not separate settings for the TSQL dialects. For example, changing CaseInsCompare changes this configuration setting for both Sybase and MSSQL.

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.

ON: All comparisons to a null value evaluate to Unknown. For example, Age = Null returns false, even when Age is null. Null is unknown, so it is false/unknown to specify null=null.
OFF: Comparisons of a non-Unicode value to a null value evaluates to True if both values are null. For example: Age = Null returns true for null values for Age.

You can determine the current ANSI_NULLS setting using %SYSTEM.TSQLOpens in a new tab class methods, or from the TSQLAnsiNullsOpens in a new tab 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 system-wide 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:

Where a comparison is the ON condition for a JOIN.
Where either operand is a subquery.

These exceptions exist because Caché SQL does not accept the %SQLUPPER operator in these contexts.

You can determine the current CASEINSCOMPARE setting using %SYSTEM.TSQLOpens in a new tab class methods, or from the TSQLCaseInsCompareOpens in a new tab property, as follows:

  SET context=##class(%SYSTEM.Context.SQL).%New()
  WRITE "ANSI_NULLS is = ",context.TSQLCaseInsCompare

You can activate (ON) or deactivate (OFF) CASEINSCOMPARE system-wide 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.TSQLOpens in a new tab class methods, or from the TSQLQuotedIdentifierOpens in a new tab 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 system-wide 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 system-wide using the following ObjectScript command:

  SET ^%SYS("tsql","SET","TRACE")="ON"

The default value is ON (TRACE active).