From 7829aba61ee098e59768600c532c8a31cf25ee34 Mon Sep 17 00:00:00 2001 From: Rafael Lima Date: Fri, 1 Dec 2023 21:08:22 +0100 Subject: Document SF Database transaction handling methods Change-Id: Id5eadea4d55cfba1b240967f76096142af04d00e Reviewed-on: https://gerrit.libreoffice.org/c/help/+/160174 Tested-by: Jenkins Reviewed-by: Jean-Pierre Ledure --- source/text/sbasic/shared/03/sf_database.xhp | 202 ++++++++++++++++++++++++++- 1 file changed, 200 insertions(+), 2 deletions(-) (limited to 'source/text') diff --git a/source/text/sbasic/shared/03/sf_database.xhp b/source/text/sbasic/shared/03/sf_database.xhp index feaac92aa4..cc2ae5c00a 100644 --- a/source/text/sbasic/shared/03/sf_database.xhp +++ b/source/text/sbasic/shared/03/sf_database.xhp @@ -44,6 +44,90 @@ The provided interfaces include simple tables and queries lists, as well as access to database data. To make SQL statements more readable, you may use square brackets "[ ]" to enclose names of tables, queries and fields instead of using other enclosing characters that may be exclusive to certain Relational Database Management Systems (RDBMS). But beware that enclosing characters are mandatory in this context. +
+

Transaction handling

+ + Database service; Transaction handling + + By default the database handles transactions in auto-commit mode, meaning that a commit is done after every SQL statement. + Use the SetTransactionMode method to change the default behavior, which allows for manual commits and rollbacks. + The methods Commit and Rollback are used to delimit transactions. + In %PRODUCTNAME, there are five types of transaction isolation modes, as defined in the com.sun.star.sdbc.TransactionIsolation constant group: +
+ + + + Constant + + + Value + + + Interpretation + + + + + NONE + + + 0 + + + Transaction handling is disabled and the database is set to the default auto-commit mode. + + + + + READ_UNCOMMITTED + + + 1 + + + Dirty reads, non-repeatable reads and phantom reads can occur. + If a row is changed by a transaction, another transaction will be able to read these changes even if they have not been committed. + + + + + READ_COMMITTED + + + 2 + + + Dirty reads are prevented, however non-repeatable reads and phantom reads can occur. + This level prevents that rows with uncommitted changes are read. + + + + + REPEATABLE_READ + + + 4 + + + Dirty reads and non-repeatable reads are prevented. However, phantom reads can occur. + Besides preventing uncommitted data from being read, it also prevents that two read operations in the same transaction return different results. + + + + + SERIALIZABLE + + + 8 + + + Dirty reads, non-repeatable reads and phantom reads are prevented. + In addition to the constraints of the previous level, it also ensures that the set of records that match a WHERE clause remains unchanged inside the same transaction. + + +
+ Read the Wikipedia page on Isolation in Database Systems to learn more about transaction integrity. +

Service invocation

Before using the Database service the ScriptForge library needs to be loaded or imported: @@ -193,6 +277,7 @@ CloseDatabase
+ Commit
CreateDataset
DAvg
DCount
@@ -206,14 +291,16 @@ DLookup
GetRows
OpenFormDocument
+ OpenQuery
 - OpenQuery
OpenSql
OpenTable
- RunSql

+ Rollback
+ RunSql
+ SetTransactionMode

 @@ -240,6 +327,49 @@ +
+ Commit --------------------------------------------------------------------------------------------- + + Database Service;Commit + +

Commit

+ Commits all updates done since the previous Commit or Rollback call. + This method is ignored if commits are done automatically after each SQL statement, i.e. the database is set to the default auto-commit mode. + + + db.Commit() + + + + + ' Set the REPEATABLE_READ transaction level + myDB.SetTransactionMode(4) + myDB.RunSql("UPDATE ...") + myDB.Commit() + myDB.RunSql("DELETE ...") + ' Test some condition before commiting + If bSomeCondition Then + myDB.Commit() + Else + myDB.Rollback() + End If + ' Restore auto-commit mode + myDB.SetTransactionMode() + + + + myDB.SetTransactionMode(4) + myDB.RunSql("UPDATE ...") + myDB.Commit() + myDB.RunSql("DELETE ...") + if some_condition: + myDB.Commit() + else: + myDB.Rollback() + myDB.SetTransactionMode() + +
+
CreateDataset -------------------------------------------------------------------------------------- @@ -513,6 +643,37 @@
+
+ Rollback ------------------------------------------------------------------------------------------- + + Database Service;Rollback + +

Rollback

+ Cancels all changes made to the database since the last Commit or Rollback call. + + + db.Rollback() + + + + + myDB.SetTransactionMode(1) + myDB.RunSql("UPDATE ...") + ' ... + If bSomeCondition Then + myDB.Rollback() + End If + + + + myDB.SetTransactionMode(1) + myDB.RunSql("UPDATE ...") + # ... + if bSomeCondition: + myDB.Rollback() + +
+
RunSql -------------------------------------------------------------------------------------------- @@ -540,6 +701,43 @@
+
+ SetTransactionMode --------------------------------------------------------------------------------- + + Database Service;SetTransactionMode + +

SetTransactionMode

+ Defines the level of isolation in database transactions. + By default databases manage transactions in auto-commit mode, which means that a Commit is automatically performed after every SQL statement. + Use this method to manually determine the isolation level of transactions. When a transaction mode other than NONE is set, the script has to explicitly call the Commit method to apply the changes to the database. + This method returns True when successful. + Changing the transaction mode closes all Dataset instances created from the current database. + + + db.SetTransactionMode(transactionmode: int = 0): bool + + + transactionmode: Specifies the transaction mode. This argument must be one of the constants defined in com.sun.star.sdbc.TransactionIsolation (Default = NONE) + Read the section Transaction handling above to learn more about the transaction isolation levels used in %PRODUCTNAME. + + + + myDB.SetTransactionMode(com.sun.star.sdbc.TransactionIsolation.REPEATABLE_READ) + oDataset = myDB.CreateDataset("SELECT ...") + ' ... + ' Reset the transaction mode to default + myDB.SetTransactionMode() + + + + from com.sun.star.sdbc import TransactionIsolation + myDB.SetTransactionMode(TransactionIsolation.REPEATABLE_READ) + dataset = myDB.CreateDataset("SELECT ...") + # ... + myDB.SetTransactionMode() + +
+
-- cgit