Hi,
I use Write Ahead Log (WAL) files for most of my sqLite data bases as this makes for fast writes and robust handling of power failures. Richard Hpp of sqLite seems to me to reccommend this method.
When I close an app I like do do a checkpoint(TRUNCATE) on the db to commit all writes to the main file so the Wal file has no transactions. This ensures that the single database file contains all the latest data. I have been doing this with B4A with no problems but when I use virtually identical code with B4J I get the following error:
Before doing the checkpoint I add a dummy resord to the only user table in the db viz:
If I then close the app and call:
The sqLite db still thinks the transaction is open and complains the db is locked and the error is thrown.
With b4A there is a Endtransation method that isn't available in B4J. Viz:
What is going on.
This seems like a bug.
The transactions are getting written correctly to the DB and are completed, so why is the checkpoint command complaining that the sqLite db in B4J is locked?
Why isn't their an end transaction command in B4J like there is in B4A.
I have attached the B4A and B4J projects as zips
I use Write Ahead Log (WAL) files for most of my sqLite data bases as this makes for fast writes and robust handling of power failures. Richard Hpp of sqLite seems to me to reccommend this method.
When I close an app I like do do a checkpoint(TRUNCATE) on the db to commit all writes to the main file so the Wal file has no transactions. This ensures that the single database file contains all the latest data. I have been doing this with B4A with no problems but when I use virtually identical code with B4J I get the following error:
B4J Error On PRAGMA wal_checkpoint(TRUNCATE):
Waiting for debugger to connect...
Program started.
DB Integrity = OK
Set Journal Mode = WAL Mode = OK
Checkpoint PASSIVE = OK: Busy = 0 Log = 1 Checkpointed = 1
Recent File Updated: \ Date: 2020-09-05 16:31:05
Recent File Updated: G:\B4J\fileEntryClass\Files\RecentFilesDB.db Date: 2020-09-05 16:31:18
DB Integrity = OK
(SQLiteException) org.sqlite.SQLiteException: [SQLITE_LOCKED] A table in the database is locked (database table is locked)
Error occurred on line: 807
java.lang.RuntimeException: Object should first be initialized (List).
at anywheresoftware.b4a.AbsObjectWrapper.getObject(AbsObjectWrapper.java:32)
at anywheresoftware.b4a.objects.collections.List.Get(List.java:105)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:632)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
at jdk.internal.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:91)
at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:98)
at anywheresoftware.b4a.debug.Debug.delegate(Debug.java:64)
at b4j.example.fileget._checkrecentfilesdb(fileget.java:219)
at b4j.example.main._mainform_closed(main.java:144)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:632)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
at jdk.internal.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:91)
at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:98)
at anywheresoftware.b4a.BA$1.run(BA.java:216)
at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:428)
at java.base/java.security.AccessController.doPrivileged(Native Method)
at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:427)
at javafx.graphics/com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:96)
at javafx.graphics/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at javafx.graphics/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:174)
at java.base/java.lang.Thread.run(Thread.java:834)Before doing the checkpoint I add a dummy resord to the only user table in the db viz:
Add or Update a record:
addOrUpdateFile(fileNameSel, pathNameSel, (pathNameSel & "\" & fileNameSel), "n", 1000)
...
'======================================================================================================
Private Sub addOrUpdateFile(fileName As String, path As String, fullPath As String, fileNotFound As Char, fileSize As Long) As Boolean
'Check if full path exists
Dim qryStr As String
Dim dateStr As String
DateTime.DateFormat = "yyyy-MM-dd"
DateTime.TimeFormat = "HH:mm:ss"
dateStr = DateTime.Date(DateTime.Now) & " " & DateTime.Time(DateTime.Now)
qryStr = _
$"SELECT COUNT(fullPath) FROM tblRecentFiles WHERE fullPath = ?"$
Dim cur As ResultSet
' sqlRecent.BeginTransaction
Try
cur = sqlRecent.ExecQuery2(qryStr, Array As String(fullPath))
Catch
Log(LastException)
End Try
' sqlRecent.TransactionSuccessful
cur.NextRow
If cur.GetLong2(0) = 0 Then
qryStr = _
$"INSERT INTO tblRecentFiles (
fileName,
path,
fullPath,
dateFirstOpened,
dateLastOpened,
fileNotFound,
fileSize)
VALUES (?,
?,
?,
?,
?,
?,
?)"$
sqlRecent.BeginTransaction
Try
sqlRecent.ExecNonQuery2(qryStr, Array As String(fileName, path, fullPath, dateStr, dateStr, fileNotFound, fileSize))
Log("Recent File Added: " & fullPath & " Date: " & dateStr)
Catch
Log(LastException)
End Try
sqlRecent.TransactionSuccessful
Else
qryStr = _
$"UPDATE tblRecentFiles
SET
dateLastOpened = ?,
fileNotFound = ?,
fileSize = ?
WHERE fullPath = ?"$
sqlRecent.BeginTransaction
Try
sqlRecent.ExecNonQuery2(qryStr, Array As String(dateStr, fileNotFound, fileSize, fullPath))
Log("Recent File Updated: " & fullPath & " Date: " & dateStr)
Catch
Log(LastException)
End Try
sqlRecent.TransactionSuccessful
End If
Return True
End SubIf I then close the app and call:
App Close - Calls checkpoint function:
Sub MainForm_Closed
' logOn, setJournal, integrityCheck, doFullCheckPoint, setAutoCheckPoint) As Boolean
If fGet.checkRecentFilesDB(True, "", True, True, -3) = False Then
'TODO
End If
End Sub
Checkpoint Functions:
'======================================================================================================
Public Sub checkRecentFilesDB(logOn As Boolean, setJournal As String, integrityCheck As Boolean, doFullCheckPoint As Boolean, setAutoCheckPoint As Int) As Boolean
'Check the database
Dim qryStr As String
Dim qryOutList As List
Dim row() As String
If integrityCheck Then
qryStr = "PRAGMA integrity_check;"
qryOutList = ExecuteMemoryTable(sqlRecent, qryStr, Null, 2)
row = qryOutList.Get(0)
If row(0).CompareTo("ok") <> 0 Then
If logOn Then Log("DB Integrity = BAD")
Return False
End If
If logOn Then Log("DB Integrity = OK")
End If
If setJournal <> "" Then
qryStr = "PRAGMA journal_mode=" & setJournal & ";"
qryOutList = ExecuteMemoryTable(sqlRecent, qryStr, Null, 2)
row = qryOutList.Get(0)
If row(0).CompareTo("wal") <> 0 Then
If logOn Then Log("Set Journal Mode = " & setJournal & " Mode = FAILED")
Return False
End If
If logOn Then Log("Set Journal Mode = " & setJournal & " Mode = OK")
End If
If doFullCheckPoint Then
qryStr = "PRAGMA wal_checkpoint(TRUNCATE);"
Try
qryOutList = ExecuteMemoryTable(sqlRecent, qryStr, Null, 2)
Catch
Log(LastException)
If logOn Then Log("Checkpoint TRUNCATE = Failed")
Return False
End Try
row = qryOutList.Get(0)
If row(0) < 0 Then
If logOn Then Log("Checkpoint TRUNCATE = Failed")
Return False
End If
If logOn Then Log("Checkpoint TRUNCATE = OK: Busy = " & row(0) & " Log = " & row(1) & " Checkpointed = " & row(2))
End If
If setAutoCheckPoint <> -3 Then
Select setAutoCheckPoint
Case -1
qryStr = "PRAGMA wal_autocheckpoint=-1;" 'No checkpoint
Case 0
qryStr = "PRAGMA wal_autocheckpoint=1000;" 'sqLite Default
Case Else
qryStr = "PRAGMA wal_autocheckpoint=" & setAutoCheckPoint & ";" 'User setting
End Select
qryOutList = ExecuteMemoryTable(sqlRecent, qryStr, Null, 2)
row = qryOutList.Get(0)
If row(0) < 0 Then
If logOn Then Log("DB Checkpoint Value Setting = Failed")
Return False
End If
If logOn Then Log("DB Checkpoint Value Setting = OK")
End If
Return True
End SubThe sqLite db still thinks the transaction is open and complains the db is locked and the error is thrown.
With b4A there is a Endtransation method that isn't available in B4J. Viz:
B4J Insert/Update record:
'======================================================================================================
Private Sub addOrUpdateFile(fileName As String, path As String, fullPath As String, fileNotFound As Char, fileSize As Long) As Boolean
'Check if full path exists
Dim qryStr As String
Dim dateStr As String
DateTime.DateFormat = "yyyy-MM-dd"
DateTime.TimeFormat = "HH:mm:ss"
dateStr = DateTime.Date(DateTime.Now) & " " & DateTime.Time(DateTime.Now)
qryStr = _
$"SELECT COUNT(fullPath) FROM tblRecentFiles WHERE fullPath = ?"$
Dim cur As ResultSet
Try
cur = sqlRecent.ExecQuery2(qryStr, Array As String(fullPath))
Catch
Log(LastException)
End Try
cur.NextRow
If cur.GetLong2(0) = 0 Then
qryStr = _
$"INSERT INTO tblRecentFiles (
fileName,
path,
fullPath,
dateFirstOpened,
dateLastOpened,
fileNotFound,
fileSize)
VALUES (?,
?,
?,
?,
?,
?,
?)"$
sqlRecent.BeginTransaction
Try
sqlRecent.ExecNonQuery2(qryStr, Array As String(fileName, path, fullPath, dateStr, dateStr, fileNotFound, fileSize))
Log("Recent File Added: " & fullPath & " Date: " & dateStr)
Catch
Log(LastException)
End Try
sqlRecent.TransactionSuccessful
sqlRecent.EndTransaction 'Fails if this commented out
Else
qryStr = _
$"UPDATE tblRecentFiles
SET
dateLastOpened = ?,
fileNotFound = ?,
fileSize = ?
WHERE fullPath = ?"$
sqlRecent.BeginTransaction
Try
sqlRecent.ExecNonQuery2(qryStr, Array As String(dateStr, fileNotFound, fileSize, fullPath))
Log("Recent File Updated: " & fullPath & " Date: " & dateStr)
Catch
Log(LastException)
End Try
sqlRecent.TransactionSuccessful
sqlRecent.EndTransaction 'Fails if this commented out
End If
Return True
End SubWhat is going on.
This seems like a bug.
The transactions are getting written correctly to the DB and are completed, so why is the checkpoint command complaining that the sqLite db in B4J is locked?
Why isn't their an end transaction command in B4J like there is in B4A.
I have attached the B4A and B4J projects as zips