Using Caché SQL
Modifying the Database
You can use SQL to modify the contents of a database. If there are indices 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 trigger.
This chapter discusses the following topics:
statement inserts a new row into an SQL table:
INSERT INTO MyApp.Product
You can also issue an INSERT OR UPDATE
statement. This statement inserts a new row into an SQL table if the row does not already exist. If the row exists, this statement updates the row data with the supplied field values.
statement modifies values in one or more existing rows within an SQL table:
SET HairColor = 'Red'
WHERE %ID = 435
statement removes one or more existing rows from an SQL table:
DELETE FROM MyApp.Person
WHERE HairColor = 'Aqua'
You can issue a TRUNCATE TABLE
command to delete all rows in a table. You can also delete all rows in a table using DELETE
(by default) pulls delete triggers; TRUNCATE TABLE
does not pull delete triggers. Using DELETE
to delete all rows does not reset table counters; TRUNCATE TABLE
resets these counters.
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 Caché 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, Caché 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.
commits all work performed during the transaction. Savepoints are ignored.
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.
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
), which maintains a count independent of the SQL transaction. For example, if you insert rows with IDKeys of 17, 18, and 19, then rollback this insert, the next row to be inserted will have an IdKey of 20.
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 Caché ObjectScript TROLLBACK
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 row 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
, 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 Caché 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:
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. This removes a numeric limit on the number of subnode kills that applied to earlier versions of Caché.
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
Caché ObjectScript Transaction Commands
Caché ObjectScript and SQL transaction commands are fully compatible and interchangeable, with the following exception:
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
Caché ObjectScript transaction processing provides limited support for nested transactions. SQL transaction processing supplies support for savepoints within transactions.