Difference between revisions of "Portable SQL/Statements/Transactions"
From Linuxnetworks
(inital) |
(removed backlink) |
||
| (3 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | == | + | == Starting transactions == |
| − | + | The ANSI SQL standard defines a statement for starting new transactions: | |
| + | |||
| + | START TRANSACTION | ||
| + | |||
| + | Unfortunately, database vendors use various different statements for starting transactions and there is no single string that fits for all. | ||
{| class="wikitable" | {| class="wikitable" | ||
| Line 10: | Line 14: | ||
|- | |- | ||
! MySQL | ! MySQL | ||
| − | | BEGIN | + | | BEGIN [WORK], START TRANSACTION (4.0.11) |
|- | |- | ||
! Oracle | ! Oracle | ||
| Line 16: | Line 20: | ||
|- | |- | ||
! PostgreSQL | ! PostgreSQL | ||
| − | | BEGIN | + | | BEGIN, SET TRANSACTION mode |
|- | |- | ||
! SQLite | ! SQLite | ||
| − | | BEGIN | + | | BEGIN [TRANSACTION] |
|- | |- | ||
! SQL Server | ! SQL Server | ||
| Line 27: | Line 31: | ||
| BEGIN TRANSACTION | | BEGIN TRANSACTION | ||
|} | |} | ||
| + | |||
| + | The statement which fits for the most popular Open Source databases and which is understood by MySQL, PostgreSQL and SQLite: | ||
| + | |||
| + | BEGIN | ||
== Saving changes == | == Saving changes == | ||
| Line 39: | Line 47: | ||
ROLLBACK | ROLLBACK | ||
| − | |||
| − | |||
| − | |||
| − | |||
Latest revision as of 15:06, 22 June 2008
Starting transactions
The ANSI SQL standard defines a statement for starting new transactions:
START TRANSACTION
Unfortunately, database vendors use various different statements for starting transactions and there is no single string that fits for all.
| Database | Statement |
|---|---|
| Firebird | SET TRANSACTION |
| MySQL | BEGIN [WORK], START TRANSACTION (4.0.11) |
| Oracle | SET TRANSACTION ISOLATION LEVEL READ COMMITTED |
| PostgreSQL | BEGIN, SET TRANSACTION mode |
| SQLite | BEGIN [TRANSACTION] |
| SQL Server | BEGIN TRANSACTION |
| Sybase ASE | BEGIN TRANSACTION |
The statement which fits for the most popular Open Source databases and which is understood by MySQL, PostgreSQL and SQLite:
BEGIN
Saving changes
Making the changes done within a transaction permanent is easily done with a single statement for all databases:
COMMIT
Discarding changes
To undo all changes done within a transaction is as easy as committing them. A single statement for all databases exists:
ROLLBACK
