Xojo Documentation Xojo Documentation

Property (As Boolean )

aSQLiteDatabase.WriteAheadLogging = newBooleanValue
or
BooleanValue = aSQLiteDatabase.WriteAheadLogging

New in 2019r2

Supported for all project types and targets.

Enables the SQLite Write-Ahead Logging (WAL) mode which can improve performance of database writes. This is especially useful when multiple users are writing to the database, as can be the case with web applications.

Notes

The SQLite organization does not recommend using SQLite on a network drive, even with WAL enabled. There is a high risk of database corruption. If your desktop app needs a multi-user database, it should use a database server.

If you want to use WAL, you need to set this property to True after connecting to the database by calling Connect.

WAL is faster than normal mode (called Journaled) because there is less disk writing. With WAL, a database change writes once to the write-ahead log. With Journaling, a database change writes to a rollback file and to the original database file.

Although faster, WAL does have some limitations:

• SQLite 3.7.0 or later is required to open WAL database
• WAL database cannot be opened in read-only mode
• Rolling back large transaction (over 100MB) can be slow
• Two extra files are created (*.sqlite-wal and *.sqlite-shm) alongside the main database file
• The processes using the database must be on the same host computer
• WAL does not work over a network filesystem

For more information about WAL, refer to the SQLite documentation: http://www.sqlite.org/draft/wal.html.

Example

This example sets WriteAheadLogging to True for a SQLite database:

Var dbFile As GetFolderItem("MyDB.sqlite")

Var db As New SQLiteDatabase
db.DatabaseFile = dbFile
Try
db.Connect
db.WriteAheadLogging = True
MessageBox("Connected to database.")
Catch error As DatabaseException
MessageBox("Connection error: " + error.Message)
End Try