|
|
|
|
|
|
|
|
|
version 11
Transactions are a set of SQL statements that are executed together. Either all of them are successful or they have no effect. Transactions use locks to preserve data integrity during their execution. If the transaction finishes successfully, you can use the COMMIT statement to permanently store its modifications. Otherwise, using the ROLLBACK statement will cancel any modifications and restore the database to its previous state.
There is no difference between a 4D transaction and an SQL transaction. Both types share the same data and process. SQL statements passed between Begin SQL/End SQL tags, the QUERY BY SQL and the ODBC commands applied to the local database are always executed in the same context as standard 4D commands.
Note: 4D provides an "Auto-commit" option which can be used to start and validate transactions automatically when using SIUD commands (SELECT, INSERT, UPDATE and DELETE) in order to preserve data integrity. For more information, please refer to the Principles for integrating 4D and the 4D SQL engine section.
The following examples illustrate the different combinations of transactions.
Neither "John" nor "Smith" will be added to the emp table:
ODBC LOGIN(SQL_INTERNAL ;"";"") `Initializes the 4D SQL engine
START TRANSACTION `Starts a transaction in the current process
Begin SQL
INSERT INTO emp
(NAME)
VALUES ('John');
End SQL
ODBC EXECUTE("START") `Another transaction in the current process
ODBC CANCEL LOAD
ODBC EXECUTE("INSERT INTO emp (NAME) VALUES ('Smith')") `This statement is executed in the same process
ODBC CANCEL LOAD
ODBC EXECUTE("ROLLBACK") `Cancels internal transaction of the pro-cess CANCEL TRANSACTION
`Cancels external transaction of the process ODBC LOGOUT
Only "John" will be added to the emp table:
ODBC LOGIN(SQL_INTERNAL ;"";"")
START TRANSACTION
Begin SQL
INSERT INTO emp
(NAME)
VALUES ('John');
End SQL
ODBC EXECUTE("START")
ODBC CANCEL LOAD
ODBC EXECUTE("INSERT INTO emp (NAME) VALUES ('Smith')")
ODBC CANCEL LOAD
ODBC EXECUTE("ROLLBACK") `Cancels internal transaction of the pro-cess VALIDATE TRANSACTION `Validates external transaction of the process ODBC LOGOUT
Neither "John" nor "Smith" will be added to the emp table. The exter-nal transaction cancels the internal transaction:
ODBC LOGIN(SQL_INTERNAL ;"";"")
START TRANSACTION
Begin SQL
INSERT INTO emp
(NAME)
VALUES ('John');
End SQL
ODBC EXECUTE("START")
ODBC CANCEL LOAD
ODBC EXECUTE("INSERT INTO emp (NAME) VALUES ('Smith')")
ODBC CANCEL LOAD
ODBC EXECUTE("COMMIT") `Validates internal transaction of the process
CANCEL TRANSACTION `Cancels external transaction of the process
ODBC LOGOUT
"John" and "Smith" will be added to the emp table:
ODBC LOGIN(SQL_INTERNAL ;"";"")
START TRANSACTION
Begin SQL
INSERT INTO emp
(NAME)
VALUES ('John');
End SQL
ODBC EXECUTE("START")
ODBC CANCEL LOAD
ODBC EXECUTE("INSERT INTO emp (NAME) VALUES ('Smith')")
ODBC CANCEL LOAD
ODBC EXECUTE("COMMIT") `Validates internal transaction of the process
VALIDATE TRANSACTION `Validates external transaction of the process
ODBC LOGOUT
|
|
|
|
|
|
|
|
|