Transact-SQL (TSQL) Migration Guide
InterSystems TSQL Constructs
InterSystems TSQL supports table references with the InterSystems IRIS Data Platform™ SQL format:
The only mandatory table reference component is table
Other forms of Transact-SQL may use table references with up to four components, separated by dots. Here is how a Transact-SQL table reference is processed:
prefix, if present, is ignored.
prefix, if present, is removed. InterSystems IRIS only supports one database name: 'master'.
prefix, if present, is mapped to the schema
For the purposes of name translation, a field name has the field suffix removed while translation is performed and then replaced afterwards.
InterSystems TSQL supports #tablename
temporary tables. A #tablename
temporary table is visible to the current procedure of the current process. It is also visible to any procedure called from the current procedure. #tablename
syntax is only supported in TSQL procedures (class methods projected as procedures with language tsql).
A temporary table is defined by using CREATE TABLE
with a table name starting with "#". The temporary table is created at runtime. A #tablename
table definition goes out of scope when you exit the procedure. All temporary table definitions go out of scope when the connection is dropped. You can also explicitly delete a temporary table using DROP TABLE
However, if a temporary table is referenced by an active result set, the temporary table may become invisible to the process, but the data and definition are retained until the result set goes out of scope.
temporary table is visible both to the creating procedure and to any procedures called from that procedure. Temporary tables are visible to nested procedure calls. It is not necessary to declare the temporary table in the called procedure. If the called procedure also creates a temporary table with the same name, InterSystems IRIS uses the most recently created table definition. Because a temporary table is defined using an ObjectScript local variable, the creation, modification, and deletion of these tables are not journaled transaction events; rolling back the transaction has no effect on these operations.
Code generated for BEGIN TRAN
uses explicit transaction mode, but following a transaction TSQL always restores the mode which was active before the BEGIN TRAN
statement. TSQL restores this mode when the procedure is exited from, or when a COMMIT
is issued, whichever comes first.
You can declare the same cursor more than once, so long as only one version of the cursor is open at runtime. If the same cursor is declared more than once in a stored procedure, all but the first declaration are associated with renamed cursors. OPEN
, and DEALLOCATE
statements are assumed to refer to the most recent DECLARE
for the given cursor. Note that the lexical position of a statement within a stored procedure is all that is used to match up a cursor name with its DECLARE
no account is taken of runtime paths through the code.
Cursors inside queries are named using an extension of the scheme used in InterSystems SQL queries. For example:
DECLARE C CURSOR FOR SELECT A FROM B
DECLARE C CURSOR FOR SELECT D FROM E
Would be effectively translated to:
DECLARE C CURSOR FOR SELECT A FROM B
DECLARE Cv2 CURSOR FOR SELECT D FROM E
Supported, with the following limitations:
System tables exist per InterSystems IRIS namespace.
Commonly, an application will have setup procedures that create tables, views, and the metadata for the application environment. Such procedures will have expressions like:
IF EXISTS (SELECT * FROM SYSOBJECTS
WHERE ID = OBJECT_ID('People'))
This determines if a table exists, in this example. It’s usually followed by a DROP
statement to reestablish the table metadata.
TSQL procedures and triggers can reference the SYSOBJECTS system table. InterSystems TSQL supports the following columns in the SYSOBJECTS table (%TSQL.sys.objects
||Object type: can be one of the following values: K=PRIMARY KEY or UNIQUE constraint; P=stored procedure; RI=FOREIGN KEY constraint; S=system table; TR=trigger; U=user table; V=view.
||Object ID of a delete trigger if the entry is a table. Table ID of a table if the entry is a trigger.
||Object ID of a table’s insert trigger if the entry is a table.
||Object ID of a table’s update trigger if the entry is a table.
||Object identification number of parent object. For example, the table ID if a trigger or constraint.
||Name of the schema in which the object resides.
||Object name of parent_obj. If parent_obj=0, parent_obj_name is the same as name.
The SYSOBJECTS table is read-only. The SYSOBJECTS table may be referenced from outside a TSQL procedure or trigger by the name %TSQL_sys.objects. SYSOBJECTS is not supported for tables mapped across namespaces.