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:
CASEINSCOMPARE (String comparison is not case-sensitive.)
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 window 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 window method to display the settings:
DO ##class(%SYSTEM.TSQL).CurrentSettings()Copy code to clipboard
You can use %SYSTEM.TSQLOpens in a new window 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.
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:
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.
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.
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:
This method returns the prior ANSI_NULLS setting.
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.
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:
This method returns the prior CASEINSCOMPARE setting.
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.
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:
This method returns the prior QUOTED_IDENTIFIER setting.
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:
To return the current trace setting:
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.