Overview

InterSystems TSQL is an implementation of Transact-SQL which supports many of the features of both the Microsoft and Sybase implementations. Transact-SQL is used with Microsoft SQL Server (MSSQL) and Sybase Adaptive Server.

InterSystems TSQL also contains a few proprietary extensions not found in either of these implementations. These are described in the Commands chapter.

Regardless of which Caché interface is used, TSQL code is used to generated corresponding Caché SQL executable code. Caché does not provide system-level support for native TSQL.

This document will help you to quickly migrate schemas and stored procedures from Microsoft or Sybase databases and it will provide you with an understanding of the TSQL (Transact-SQL) implementation for InterSystems Caché™.

Microsoft provides good TSQL reference material at: https://docs.microsoft.com/en-us/sql/sql-server/?view=sql-server-ver15Opens in a new tab.

Migrating Existing TSQL Applications

To migrate existing TSQL applications to InterSystems TSQL, you need to perform three operations: configure Caché for TSQL, migrate the TSQL source code, and migrate the data.

Configuring TSQL

To configure your system for TSQL:

Go into the Caché Management Portal. Select System Administration, Configuration, SQL and Object Settings, then select TSQL Compatibility. Here you can specify the system-wide default DIALECT (Sybase or MSSQL), and turn on or off the ANSI_NULLS, CASEINSCOMPARE, and QUOTED_IDENTIFIER settings for TSQL system-wide. The default for all three is “off”. These values are used to set the ^%SYS(“tsql”,”SET”,...) global array values.
From the Management Portal, select System Administration, Configuration, SQL and Object Settings, then SQL. From here, you can set the Default Schema. This is the default schema name (which maps to a package) for all unqualified DDL entities.
From the Management Portal, select System Administration, Configuration, SQL and Object Settings, then User DDL Mappings. You can use this option to map any needed user-defined data types.

Migrating Source Code

The initial application migration is simple:

Import the DDL: Import table and view definitions using either the %SYSTEM.SQL.DDLImport()Opens in a new tab method (for single files) or the %SYSTEM.SQL.DDLImportDir()Opens in a new tab method (for multiple files in a directory). Within these methods, you set the DDLMode parameter to either "MSSQLServer" or "Sybase". These methods import DDL statements, as well as DML statements such as INSERT, convert them to equivalent Caché SQL, and execute them. For further details, see Importing SQL Code.
Alternatively, you can invoke the $SYSTEM.SQL.Sybase()Opens in a new tab or $SYSTEM.SQL.MSSQLServer()Opens in a new tab method to import the schema. For further details, see Importing SQL Code.
If the TSQL source contains CREATE PROC statements, then a class method containing the CREATE PROC source is created. Caché places this class method in either an existing class or in a new class whose name is based on the schema and procedure name. If the procedure already exists, then the existing version is replaced by the new version. If a class matching the class name generated from the schema and procedure already exists, then this class name is used — if it was previously generated by the TSQL utility. If not, then a unique class name is generated, based on the schema and procedure name. The resulting class is compiled once the procedure has been successfully created. If logging is requested then the source statements are logged along with the name of the containing class, class method, and the formal arguments generated. Any errors encountered by the process are also reported in the log. If an error is detected during CREATE PROC processing, Caché deletes any new class that was generated for that procedure.
Inspect the log file for errors: Search by Error #. A summary count of errors and successful imports will appear at the end of the log. In most cases, errors can be worked around or addressed by using information found in this document.
Compile: When you import DDL, table and view definition compilation is automatically performed. To compile other TSQL source code, it is best to use the command as follows:
```
  DO $SYSTEM.OBJ.CompileAll("-l")
```
The lowercase “L” qualifier flag specifies that locking is not applied for the duration of the compile. For a full list of flag qualifiers, call DO $SYSTEM.OBJ.ShowFlags().

Migrating the Data

In the Management Portal select System Explorer, SQL, then from the Wizards drop-down list select the Data Migration Wizard. This runs a wizard to migrate data from an external source and creates a Caché class definition to store it.

Writing and Executing TSQL on Caché

Writing TSQL class methods and stored procedures
Create a class method stored procedure and enter the language as tsql. You can use the following template as a starting point:
```
ClassMethod MyTestMethod() As %Integer 
   [ Language = tsql, ReturnResultSets, SqlName=name, SqlProc ]
{
}
```
See the Language keyword for method definition in the Class Definition Reference.
You can write and maintain TSQL stored procedures (SPs) in Studio. A TSQL SP can be either a class method or a query. A class method takes parameters and returns a single scalar result, a query takes parameters and returns rows. If you put plain SELECT statements into a class method they will be executed but you won't be able to get the rows.
TSQL procedures are converted to Caché methods or queries with a Language type equal to TSQL. Use the following command:
```
DO ##class(%TSQL.Manager).load("sybase",<filename>,<logname>)
```
When compiling TSQL methods, ObjectScript code is generated. There is no system-level support for native TSQL. It is best to maintain the methods in TSQL to retain the familiar look of the original stored procedures.
Using Dynamic SQL
Caché Dynamic SQL can be used to execute TSQL code queries and a limited subset of other DML and DDL statements.
- You can create a Dynamic SQL statement class instance, then set the %Dialect property to Sybase or MSSQL. You then prepare and execute a TSQL command within that object instance.
- You can execute Dynamic SQL without creating a statement class instance by invoking the %SYSTEM.SQL.Execute()Opens in a new tab method, which both prepares and executes an SQL command. This method provides a Dialect parameter.
See “Using Dynamic SQL” in the Using Caché SQL manual.
Using the Management Portal SQL Interface
The SQL interface Dialect option allows you to set the SQL dialect to Cache, Sybase, or MSSQL. The default is Cache. Note that the dialect you select becomes the user customized default the next time you access the Management Portal. See “Using the Management Portal SQL Interface” in the Using Caché SQL manual.
Using the TSQL Shell
The InterSystems TSQL Shell can be used to execute Transact-SQL code from Caché. To use the TSQL Shell, invoke the TSQLShell()Opens in a new tab (or TSQL()Opens in a new tab) method from the Terminal as follows: DO $SYSTEM.SQL.TSQLShell(). This invokes the InterSystems SQL Shell and sets its DIALECT configuration parameter to the currently configured TSQL dialect (MSSQL or Sybase). The initial configuration default is MSSQL.
When entering SQL code interactively, the TSQL Shell supports, but does not require, the semicolon (;) statement delimiter at the end of each SQL statement.
You can use the Shell’s RUN command to execute a TSQL script file. The RUN command displays a series of prompts, including Please enter the end-of-statement delimiter (Default is 'GO'): GO=>. This enables you to specify the TSQL semicolon (;) as the statement delimiter in your script file, rather than the Caché default GO statement. See “Using the SQL Shell Interface” in the Using Caché SQL manual.
Using the InterSystems SQL Shell
The InterSystems SQL Shell can be used to execute lines of TSQL code by using the SET DIALECT command to set the Shell’s dialect to Sybase or MSSQL.
When the Shell’s dialect is set to Sybase or MSSQL, the SQL Shell supports, but does not require, the semicolon (;) statement delimiter at the end of each SQL statement. When the Shell’s dialect is set to Cache, a semicolon (;) statement delimiter results in an SQLCODE -25 error.
You can use the Shell’s RUN command to execute a TSQL script file. The RUN command displays a series of prompts, including Please enter the end-of-statement delimiter (Default is 'GO'): GO=>. This enables you to specify the TSQL semicolon (;) as the statement delimiter in your script file, rather than the Caché default GO statement. See “Using the SQL Shell Interface” in the Using Caché SQL manual.
Using Triggers
You can write and maintain triggers, which are sets of instructions that appear in TSQL code and that are executed in response to certain SQL events. You can use the Language=tsql class definition keyword to specify that a trigger is written in TSQL. The UpdateColumnList class definition keyword is only supported for TSQL. Row-level triggers are not supported for TSQL. See “Using Triggers” in the Using Caché SQL manual.