User bio

I have worked for or worked with InterSystems platforms for 35 years, 20 of them at InterSystems. I spent much of my career as a Sales Engineer spreading the gospel of Cache, Ensemble and now IRIS. I have written a number of applications over the years, many of which are still running. from 2009 onwards I wrote a number of Interfaces to LabTrak and those interfaces have been copied a multitude of times by other developers. In 2015 I was introduced to Pharmacy Robotics and for the next 4 years, I wrote several versions of the interface with increasing comp;lexity and functinality. I started a company NiPaRobotica with my friend Paul Hula with the aim of marketing the Pharmacy Robotics but my longer-term vision is an "Any Application to Any Robot", almost NO-CODE product. I have a strong background in HL7, FHIR, Instruments, Labs, HIS. My current passions are Raspberry Pi, Ubuntu, Python, R, PiCar Pro robot kit. I am currently working as a contractor.

Show all
Member since Oct 17, 2021
Replies:

Hi

In Cache/Ensemble/IRIS there is a %NOLOCK clause that you can use in an Insert, Update or Delete. But that disables locking in these oerations.

According to the documentation for TSQL the following documentation exists for LOCK TABLE:

Lock Table

Enables the current user to lock a table.

LOCK TABLE tablename IN {SHARE | EXCLUSIVE} MODE [WAIT  numsecs | NOWAIT]

The LOCK TABLE
statement locks all of the records in the specified table. You can lock a table in SHARE MODE or in EXCLUSIVE MODE. The optional WAIT clause specifies the number of seconds to wait in attempting to acquire the table lock. The LOCK TABLE statement immediately releases any prior lock held by the current user on the specified table.

LOCK TABLE
is only meaningful within a transaction. It locks the table for the duration of the current transaction. When not in a transaction, LOCK TABLE performs no operation.

Specify tablename as described in Table References. LOCK TABLE supports locking a single table; it does not support locking multiple tables.

LOCK TABLE supports SHARE and EXCLUSIVE modes; it does not support WRITE mode.

LOCK TABLE does not support the WITH HOLD clause.

WAIT time is specified as an integer number of seconds; LOCK TABLE does not support WAIT time specified as clock time.

The Documentation on Table Names reads a follows:

Table References

InterSystems TSQL supports table references with the InterSystems IRIS® data platform SQL format:

schema.table

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.

 

Temporary Tables

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

System tables exist per InterSystems IRIS namespace.

Systypes

Partially supported.

Nigel

Certifications & Credly badges:
Nigel has no Certifications & Credly badges yet.
Global Masters badges:
Followers:
Following:
Nigel has not followed anybody yet.