Modifying the Database
You can use either SQL statements against an existing table or ObjectScript operations on the corresponding persistent class to modify the contents of an InterSystems IRIS® data platform database. You cannot modify a persistent class (table) that is defined as READONLYOpens in a new tab.
Using SQL commands provides automatic support for maintaining the integrity of the data. An SQL command is an atomic (all or nothing) operation. If there are indexes defined on the table, SQL will automatically update them to reflect the changes. If there are any data or referential integrity constraints defined, SQL will automatically enforce them. If there are any defined triggers, performing these actions will pull the corresponding insert, update, or delete trigger.
Inserting Data
You can insert data into a table either by using the SQL statements or by setting and saving persistent class properties.
Insert Data Using SQL
The INSERT statement inserts a new record into an SQL table. You can insert a single record or multiple records.
The following example inserts a single record. It is one of several available syntax forms to insert a single record:
INSERT INTO MyApp.Person
(Name,HairColor)
VALUES ('Fred Rogers','Black')
The following example inserts multiple records by querying data from an existing table:
INSERT INTO MyApp.Person
(Name,HairColor)
SELECT Name,Haircolor FROM Sample.Person WHERE Haircolor IS NOT NULL
For more details on how to insert various types of data using the INSERT command, see the INSERT command reference page.
You can also issue an INSERT OR UPDATE statement. This statement inserts a new record into an SQL table if the record does not already exist. If the record exists, this statement updates the record data with the supplied field values.
Insert Data Using Object Properties
You can use ObjectScript to insert one or more records of data. Create an instance of an existing persistent class, set one or more property values, then use %Save() to insert the data record:
The following example inserts a single record:
SET oref=##class(MyApp.Person).%New()
SET oref.Name="Fred Rogers"
SET oref.HairColor="Black"
DO oref.%Save()
The following example inserts multiple records:
SET nom=$LISTBUILD("Fred Rogers","Fred Astare","Fred Flintstone")
SET hair=$LISTBUILD("Black","Light Brown","Dark Brown")
FOR i=1:1:$LISTLENGTH(nom) {
SET oref=##class(MyApp.Person).%New()
SET oref.Name=$LIST(nom,i)
SET oref.HairColor=$LIST(hair,i)
SET status = oref.%Save() }
UPDATE Statements
The UPDATE statement modifies values in one or more existing records within an SQL table:
UPDATE MyApp.Person
SET HairColor = 'Red'
WHERE Name %STARTSWITH 'Fred'
Computed Field Values on INSERT or UPDATE
When you define a computed field, you can specify code to compute a data value for that field. This data value can be computed when the row is inserted, updated, both inserted and updated, or when queried. The following table shows the keywords required for each type of compute operation and a field/property definition example:
Compute Type | DDL SQL Keywords to Specify | Persistent Class Keywords to Specify |
---|---|---|
On INSERT only |
COMPUTECODE Birthday VARCHAR(50) COMPUTECODE {SET {Birthday}=$PIECE($ZDATE({DOB},9),",")_" changed: "_$ZTIMESTAMP } |
SqlComputeCode and SqlComputed Property Birthday As %String(MAXLEN = 50) [ SqlComputeCode = {SET {Birthday}=$PIECE($ZDATE({DOB},9),",")_" changed: "_$ZTIMESTAMP}, SqlComputed ]; |
On UPDATE only |
DEFAULT, COMPUTECODE, and COMPUTEONCHANGE Birthday VARCHAR(50) DEFAULT ' ' COMPUTECODE {SET {Birthday}=$PIECE($ZDATE({DOB},9),",")_" changed: "_$ZTIMESTAMP } COMPUTEONCHANGE (DOB) |
n/a |
On both INSERT and UPDATE |
COMPUTECODE and COMPUTEONCHANGE Birthday VARCHAR(50) COMPUTECODE {SET {Birthday}=$PIECE($ZDATE({DOB},9),",")_" changed: "_$ZTIMESTAMP } COMPUTEONCHANGE (DOB) |
SqlComputeCode, SqlComputed, and SqlComputeOnChange Property Birthday As %String(MAXLEN = 50) [ SqlComputeCode = {SET {Birthday}=$PIECE($ZDATE({DOB},9),",")_" changed: "_$ZTIMESTAMP}, SqlComputed, SqlComputeOnChange = DOB ]; |
On query |
COMPUTECODE and CALCULATED or TRANSIENT Birthday VARCHAR(50) COMPUTECODE {SET {Birthday}=$PIECE($ZDATE({DOB},9),",")_" changed: "_$ZTIMESTAMP } CALCULATED |
SqlComputeCode, SqlComputed, and Calculated orTransient Property Birthday As %String(MAXLEN = 50) [ SqlComputeCode = {SET {Birthday}=$PIECE($ZDATE({DOB},9),",")_" changed: "_$ZTIMESTAMP}, SqlComputed, Calculated]; |
The DDL DEFAULT keyword takes precedence over computing a data value upon insert. DEFAULT must take a data value, such as an empty string; it cannot be NULL. In a persistent class definition, the InitialExpression property keyword does not override an SqlComputed data value upon insert.
The DDL COMPUTEONCHANGE keyword can take a single field name or a comma-separated list of field names. These field names specify the fields which when updated will trigger the compute of this field; the listed field names must exist in the table, but they do not have to appear in the compute code. You must specify actual field names; you cannot specify asterisk syntax.
You can use the ON UPDATE keyword phrase to set a field to a literal or a system variable (such as the current timestamp) when a record is modified, rather than using COMPUTECODE and COMPUTEONCHANGE. The ON UPDATE phrase modifies on both INSERT and UPDATE; to modify only on UPDATE, use the DEFAULT phrase and the ON UPDATE phrase.
The DDL CALCULATED or TRANSIENT keyword computes a data value each time the field is accessed by a query. The field does not need to be specified in the select list. For example, SELECT Name FROM MyTable WHERE LENGTH(Birthday)=36 computes the Birthday field before evaluating the condition expression. The Management Portal Open Table option performs a query, and therefore computes CALCULATED and TRANSIENT data values.
Computed field restrictions:
-
UPDATE that doesn’t update: An UPDATE that supplies the same values to the fields in a record as their prior values does not actually update the record. COMPUTEONCHANGE is not invoked if no real update is performed for a record. ON UPDATE is invoked on an update operation even if no real update is performed for a record. If you wish to always recalculate a computed field upon update regardless of whether the record was actually updated, use an update trigger.
-
User-specified explicit value for a computed field:
-
INSERT: On INSERT you can always supply an explicit value to a COMPUTECODE, DEFAULT, or ON UPDATE field. InterSystems SQL always takes the explicit value rather than the generated value.
-
UPDATE COMPUTEONCHANGE: An UPDATE operation can supply an explicit value to a COMPUTEONCHANGE field. InterSystems SQL always takes the explicit value rather than the computed value.
-
UPDATE ON UPDATE: An UPDATE operation cannot supply an explicit value to an ON UPDATE field. InterSystems SQL ignores the user-supplied value and takes the ON UPDATE generated value. However, InterSystems SQL does perform field validation on the explicit value and can, for example, generate an SQLCODE -104 error if the supplied value is longer than the maximum data size.
-
CALCULATED or TRANSIENT: An INSERT or UPDATE operation cannot supply an explicit value to a CALCULATED or TRANSIENT field, because a CALCULATED or TRANSIENT field does not store data. However, InterSystems SQL does perform field validation on the explicit value and can, for example, generate an SQLCODE -104 error if the supplied value is longer than the maximum data size.
-
Validating Data
The insert and update operations described on this page automatically perform data validation. They prevent invalid data being stored in a table. Data that is stored in a table by other means may not be validated. You can use the $SYSTEM.SQL.Schema.ValidateTable() method to validate the data in a table. The table name can be qualified ("schema.table"), or unqualified ("table"). An unqualified table name takes the default schema name; schema search path values are not used.
ValidateTable() returns a result set containing a row for each validation issue found in the table’s data. This method performs the following data validations:
-
Validates each data value against the field’s data type, using the data type IsValid() method.
-
Validates that any field with a Required constraint does not have a null value.
-
Validates that any field with a Unique constraint does not have a duplicate value.
-
Validates that any Foreign Key field references a valid row in the referenced table.
The validation result set is held in the %sqlcontext object. DO %sqlcontext.%Display() will display the data validation results to the current device, as shown in the following Terminal example:
USER>DO $SYSTEM.SQL.Schema.ValidateTable("Sample.MyTable")
USER>DO %sqlcontext.%Display()
Dumping result #1
Row(s) With an Issue Field Or Constraint Name Error
%ID 3 Home_City Cannot be null
%ID 14 Home_City Cannot be null
%ID 10 Home_City Cannot be null
%ID 13 Home_City Cannot be null
%ID 6 Home_PostalCode Value is invalid: BadZip
%ID 8 Home_PostalCode Value is invalid: WhoKnows
%ID 9 Home_PostalCode Value is invalid: BadZip
%ID 10 Home_PostalCode Value is invalid: WhoKnows
%ID 11 Home_PostalCode Value is invalid: BadZip
%ID 9 Home_State Cannot be null
%ID 3 Home_State Cannot be null
%ID 10 Home_State Cannot be null
12 Rows(s) Affected
USER>
Fields are listed in alphabetical order. In this example, Home_City and Home_State fields failed required value validation; Home_PostalCode (%Integer data type) fields failed data type validation.
You can also invoke this data validation operation from SQL by calling the ValidateTable stored procedure, as shown in the following example:
CALL %SYSTEM.ValidateTable('Sample.MyTable')
If the table is sharded, ValidateTable() should be called on the shard master table.
ValidateTable() does not perform locking. Therefore, if it is run against a table on a live system with concurrent operations you could receive false-positive error reports.
DELETE Statements
The DELETE statement removes one or more existing records from an SQL table:
DELETE FROM MyApp.Person
WHERE HairColor = 'Aqua'
You can issue a TRUNCATE TABLE command to delete all records in a table. You can also delete all records in a table using DELETE. DELETE (by default) pulls delete triggers; TRUNCATE TABLE does not pull delete triggers. Using DELETE to delete all records does not reset table counters; TRUNCATE TABLE resets these counters.
Transaction Processing
A transaction is a series of INSERT, UPDATE, DELETE, INSERT OR UPDATE, and TRUNCATE TABLE data modification statements that comprise a single unit of work.
The SET TRANSACTION command can be used to set the transaction parameters for the current process. The same parameters can also be set using the START TRANSACTION command. These transaction parameters continue in effect across multiple transactions until explicitly changed.
A START TRANSACTION command explicitly starts a transaction. This command is generally optional; if transaction %COMMITMODE is either IMPLICIT or EXPLICIT, a transaction begins automatically with the first database modification operation. If transaction %COMMITMODE is NONE, you must explicitly specify START TRANSACTION to initiate transaction processing.
If a transaction succeeds, committing its changes can be implicit (automatic) or explicit; the %COMMITMODE value determines whether you need to explicitly use the COMMIT statement to permanently add the data modifications to the database and release resources.
If a transaction fails, you can use the ROLLBACK statement to undo its data modifications so that these do not go into the database.
SQL transaction statements are not supported when running SQL through the Management Portal Execute SQL Query interface. This interface is intended as a test environment for developing SQL code, not for modifying actual data.
Transactions and Savepoints
In InterSystems SQL, you can perform two kinds of transaction processing: full transaction processing and transaction processing using savepoints. With full transaction processing, a transaction begins with START TRANSACTION statement (explicit or implicit) and continues until either a COMMIT statement (explicit or implicit) concludes the transaction and commits all work, or a ROLLBACK statement reverses all work done during the transaction.
With savepoints, InterSystems SQL supports levels within a transaction. You begin a transaction with a START TRANSACTION statement (explicit or implicit). Then during the transaction you use SAVEPOINT to specify one or more named savepoints within the program. You can specify a maximum of 255 named savepoints in a transaction. Adding a savepoint increments the $TLEVEL transaction level counter.
-
A COMMIT commits all work performed during the transaction. Savepoints are ignored.
-
A ROLLBACK rolls back all work performed during the transaction. Savepoints are ignored.
-
A ROLLBACK TO SAVEPOINT pointname rolls back all work performed since the SAVEPOINT specified by pointname and decrements an internal transaction level counter by the appropriate number of savepoint levels. For example, if you established two savepoints, svpt1 and svpt2, and then rolled back to svpt1, the ROLLBACK TO SAVEPOINT svpt1 reverse the work done since svpt1 and, in this case, decrements the transaction level counter by 2.
Non-transaction Operations
While a transaction is in effect, the following operations are not included in the transaction and therefore cannot be rolled back:
-
The IDKey counter increment is not a transaction operation. The IDKey is automatically generated by $INCREMENT (or $SEQUENCE), which maintains a count independent of the SQL transaction. For example, if you insert records with IDKeys of 17, 18, and 19, then rollback this insert, the next record to be inserted will have an IdKey of 20.
-
Cached query creation, modification, and purging are not transaction operations. Therefore, if a cached query is purged during a transaction, and that transaction is then rolled back, the cached query will remain purged (will not be restored) following the rollback operation.
-
A DDL operation or a Tune Table operation that occur within a transaction may create and run a temporary routine. This temporary routine is treated the same as a cached query. That is, the creation, compilation, and deletion of a temporary routine are not treated as part of the transaction. The execution of the temporary routine is considered part of the transaction.
For non-SQL items rolled back or not rolled back, refer to the ObjectScript TROLLBACK command.
Transaction Locks
A transaction uses locks to safeguard unique data values. For example, if a process deletes a unique data value, this value is locked for the duration of the transaction. Therefore, another process could not insert a record using this same unique data value until the first transaction completed. This prevents a rollback resulting in a duplicate value for a field with a uniqueness constraint. These locks are automatically applied by the INSERT, UPDATE, INSERT OR UPDATE, and DELETE statements, unless the statement includes a %NOLOCK restriction argument.
Transaction Size Limitations
There is no limitation on the number of operations you can specify in a transaction, other than space availability for journal files. The size of the lock table does not normally impose a limit, because InterSystems IRIS provides automatic lock escalation.
There is a default lock threshold of 1000 locks per table. A table can have 1000 unique data value locks for the current transaction. The 1001st lock operation escalates the locking for that table to a table lock for the duration of the transaction.
This lock threshold value is configurable using either of the following:
-
Invoke the $SYSTEM.SQL.Util.SetOption("LockThreshold")Opens in a new tab method. This method changes both the current system-wide value and the configuration file setting. To determine the current lock escalation threshold, use the $SYSTEM.SQL.Util.GetOption("LockThreshold")Opens in a new tab method.
-
Go to the Management Portal. From System Administration, select Configuration, then SQL and Object Settings, then SQL. On this screen you can view and edit the current setting of Lock Threshold.
There is no limit on the number of subnodes (child tables) that can be killed. All subnode kills are journaled, and thus can be rolled back.
Reading Uncommitted Data
You can specify the read isolation level by setting SET TRANSACTION or START TRANSACTION for the process issuing the query.
-
ISOLATION LEVEL READ UNCOMMITTED: Uncommitted inserts, updates, and deletes to data are visible for query (read only) access by other users. This is the default if no transaction is specified.
-
ISOLATION LEVEL READ VERIFIED: Uncommitted inserts, updates, and deletes to data are visible for query (read only) access by other users. Provides re-checking of data used by query conditions and displayed by the query.
-
ISOLATION LEVEL READ COMMITTED: Changes made to the data by uncommitted inserts and updates are not shown in the query result set. The query result set only contains inserts and updates that have been committed. However, changes made to the data by uncommitted deletes are shown in the query result set.
The following SELECT command clauses always return uncommitted data, regardless of the current isolation level: an aggregate function, a DISTINCT clause, a GROUP BY clause, or a SELECT with the %NOLOCK keyword. For further details, refer to Isolation Level.
ObjectScript Transaction Commands
ObjectScript and SQL transaction commands are fully compatible and interchangeable, with the following exception:
ObjectScript TSTART and SQL START TRANSACTION both start a transaction if no transaction is current. However, START TRANSACTION does not support nested transactions. Therefore, if you need (or may need) nested transactions, it is preferable to start the transaction with TSTART. If you need compatibility with the SQL standard, use START TRANSACTION.
ObjectScript transaction processing provides limited support for nested transactions. SQL transaction processing supplies support for savepoints within transactions.