InterSystems TSQL Constructs
InterSystems TSQL supports table references with the InterSystems IRIS® data platform SQL format:
The only mandatory table reference component is table. If the schema is omitted, TSQL uses the default schema name.
Other forms of Transact-SQL may use table references with up to four components, separated by dots: server.database.owner.table. Here is how a Transact-SQL table reference is processed:
The server. component, if present, is ignored.
If the database. component is present and the owner. component is omitted, database is mapped to the schema name. Therefore, database..table maps to schema.table. This conversion is not performed if the database name is 'master'.
If the owner. component is present, it is mapped to the schema name.
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.
A #tablename 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.
System tables exist per InterSystems IRIS namespace.
Code generated for BEGIN TRAN, COMMIT and ROLLBACK 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 or ROLLBACK is issued, whichever comes first.
Cursor Name Management
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, FETCH, CLOSE, 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 -- OPEN C FETCH C CLOSE C DEALLOCATE C -- DECLARE C CURSOR FOR SELECT D FROM E -- OPEN C FETCH C CLOSE C DEALLOCATE C
Would be effectively translated to:
DECLARE C CURSOR FOR SELECT A FROM B -- OPEN C FETCH C CLOSE C DEALLOCATE C -- DECLARE Cv2 CURSOR FOR SELECT D FROM E -- OPEN Cv2 FETCH Cv2 CLOSE Cv2 DEALLOCATE Cv2
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 and CREATE 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.objectsOpens in a new window class properties):
|nameOpens in a new window||Object name.|
|idOpens in a new window||Object Id.|
|typeOpens in a new window||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.|
|deltrigOpens in a new window||Object ID of a delete trigger if the entry is a table. Table ID of a table if the entry is a trigger.|
|instrigOpens in a new window||Object ID of a table’s insert trigger if the entry is a table.|
|updtrigOpens in a new window||Object ID of a table’s update trigger if the entry is a table.|
|parent_objOpens in a new window||Object identification number of parent object. For example, the table ID if a trigger or constraint.|
|schemaOpens in a new window||Name of the schema in which the object resides.|
|parent_obj_nameOpens in a new window||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.