Skip to main content

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.

InterSystems 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 in the Configuration Parameter File Reference.

You can view and modify these settings using the Caché Management Portal or the %SYSTEM.TSQLOpens in a new tab Get and Set 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. These methods take a dialect string and change both the current dialect and the specified setting. There are not separate ANSI_NULLS, CaseInsCompare, and Quoted_Identifier settings for each TSQL dialect. 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 system-wide using the Caché Management Portal or by using the following method:

  WRITE ##class(%SYSTEM.TSQL).SetDialect("Sybase")

This method returns the prior Dialect setting.

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 the following method:

  WRITE ##class(%SYSTEM.TSQL).SetAnsiNulls("Sybase","OFF")

This method returns the prior ANSI_NULLS setting.

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 InterSystems 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 the following method:

  WRITE ##class(%SYSTEM.TSQL).SetCaseInsCompare("Sybase","OFF")

This method returns the prior CASEINSCOMPARE setting.

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 the following method:

  WRITE ##class(%SYSTEM.TSQL).SetQuotedIdentifier("Sybase","OFF")

This method returns the prior QUOTED_IDENTIFIER setting.

TRACE

The TRACE configuration option creates a log file of the execution of TSQL procedures. When a TSQL stored procedure (or method) is compiled with TRACE active, running a TSQL procedure will log trace messages to the active tsql log file.

A separate tsql trace log file is created for each process from which TSQL procedures are run. Trace is activated system-wide; trace log files are namespace-specific.

TRACE is not set using the Management Portal. It You can activate (1) or deactivate (0) TRACE system-wide using the following ObjectScript command:

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

To return the current trace setting:

  WRITE ^%SYS("tsql","TRACE")

The TRACE log file is created in your Caché instance in the mgr directory, in the subdirectory for the current namespace. It is named using the current process number. For example: Cache/mgr/user/ tsql16392.log.

FeedbackOpens in a new tab