B4J Tutorial Backup SQLite DB with VACUUM INTO

Less of a tutorial, more to point out another option which doesn't seem to have a mention anywhere on here yet....

I was searching for options to create a backup copy of an SQLite database. There are already options detailed here, and here, for example.
But another option that seemed quite attractive was to use VACUUM INTO, as detailed on sqlite.org at https://www.sqlite.org/lang_vacuum.html

The bit that stood out to me was;
The VACUUM command with an INTO clause is an alternative to the backup API for generating backup copies of a live database
It works when the database is open, with live *.wal files for example, and handily produces just a single backup file that's been shrunk to its minimum size.

Usage is pretty simple with the SQL library:
Backup SQLite using VACUUM INTO...:
Dim filePath As String = "C:\Database"
Dim fileName As String = "myDB.db"
Dim backupPath As String = "C:\DBBackup\myDB-backup.db"      'backupPath must include the filename

Dim sql As SQL
sql.InitializeSQLite(filePath, fileName, False)
sql.ExecNonQuery2("VACUUM INTO ?", Array(backupPath))

Notes:
1. VACUUM INTO requires SQLite version 3.27.0 or later (@Claudio Oliveira handily keeps a list of the latest SQLite JDBC library versions here)
2. VACUUM can not be executed from within a transaction.
 
Last edited:

amorosik

Expert
Licensed User
B4X:
Dim filePath As String = "C:\PUBLIC\ERP2\SQLITE_DATA"
Dim fileName As String = "ERP2_SQLITE.DB"
Dim backupPath As String = "C:\PUBLIC\ERP2\SQLITE_DATA\ERP2_SQLITE_BK.DB"      'backupPath must include the filename

Dim sql As SQL
sql.InitializeSQLite(filePath, fileName, False)
sql.ExecNonQuery2("VACUUM INTO ?", Array(backupPath))

With sqlite-jdbc-3.41.2.1.jar , at the 'sql.ExecNonQuery2("VACUUM INTO ?", Array(backupPath))' row, returns an error
What can cause the anomaly?

B4X:
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "INTO": syntax error)
    at org.sqlite.DB.newSQLException(DB.java:383)
    at org.sqlite.DB.newSQLException(DB.java:387)
    at org.sqlite.DB.throwex(DB.java:374)
    at org.sqlite.NativeDB.prepare(Native Method)
    at org.sqlite.DB.prepare(DB.java:123)
Error occurred on line: 3625 (Main)
    at org.sqlite.PrepStmt.<init>(PrepStmt.java:42)
    at org.sqlite.Conn.prepareStatement(Conn.java:404)
    at org.sqlite.Conn.prepareStatement(Conn.java:399)
    at org.sqlite.Conn.prepareStatement(Conn.java:383)
    at anywheresoftware.b4j.objects.SQL.ExecNonQuery2(SQL.java:199)
    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.runVoidMethod(Shell.java:673)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:240)
    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:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.debug.Debug.delegate(Debug.java:64)
    at aaa.erp2.mqtt_connector.main._rigenera_db_sqlite_per_tablet(main.java:6743)
    at aaa.erp2.mqtt_connector.main._mqtt_messagearrived(main.java:3962)
    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:629)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    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.BA.raiseEvent2(BA.java:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA$3.run(BA.java:267)
    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)
 

RickV

Member
I struggled to get this to work for a couple of days, here is the solution....
have one sql lite db for the project

Globals
Public MySql as SQL

then where-ever you want the cleanup stuff, mine is in main create first time and called BRFORE I open to use the db

B4X:
    Dim filePath As String = "C:\Data"
    Dim fileName As String = "pt.db"
    Dim backupPath As String = "C:\Data\pt-backup.db"


        Try
            File.Delete("c:\data","pt-backup.db")
        Catch
            Log(LastException)
        End Try
        MySQL.InitializeSQLite(filePath, fileName, True)
        MySQL.ExecNonQuery2("VACUUM main INTO ?", Array(backupPath))
        MySQL.Close
        
        File.Delete("c:\data","pt.db")
        File.Copy("C:\Data","pt-backup.db", filePath,fileName)
        File.Delete("c:\data","pt-backup.db")

    MySQL.InitializeSQLite(filePath, fileName, True)
 

Chris2

Active Member
Licensed User
It works for me with sqlite-jdbc-3.41.2.1.jar.
At a guess, do you have sufficient rights to write to the backupPath?
 

amorosik

Expert
Licensed User
Yes, the rights is ok (i can create new dir and new file with Widnows File Manager)
Also in the beginning of the code

B4X:
#Region  Project Attributes
    #MainFormWidth: 800
    #MainFormHeight: 600
    #AdditionalJar: jaybird-5.0.1.java11.jar
    #AdditionalJar: sqlite-jdbc-3.41.2.1.jar
#End Region

Sub Process_Globals
    Dim Sqlite_Per_Vacuum As SQL
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    '------------   Compatta il File DB  -----------------
    Dim filePath As String = "C:\PUBLIC\ERP2\SQLITE_DATA"
    Dim fileName As String = "ERP2_SQLITE.DB"
    Dim backupPath As String = "C:\PUBLIC\ERP2\SQLITE_DATA\ERP2_SQLITE_BK.DB"      'backupPath must include the filename
  
    Sqlite_Per_Vacuum.InitializeSQLite(filePath, fileName, False)
    Sqlite_Per_Vacuum.ExecNonQuery2("VACUUM INTO ?", Array(backupPath))
    Sqlite_Per_Vacuum.close
    ' ----------------------------------------------------

The error is the same

B4X:
Waiting for debugger to connect...
Program started.
Error occurred on line: 153 (Main)
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "INTO": syntax error)
    at org.sqlite.DB.newSQLException(DB.java:383)
    at org.sqlite.DB.newSQLException(DB.java:387)
    at org.sqlite.DB.throwex(DB.java:374)
    at org.sqlite.NativeDB.prepare(Native Method)
    at org.sqlite.DB.prepare(DB.java:123)
    at org.sqlite.PrepStmt.<init>(PrepStmt.java:42)
    at org.sqlite.Conn.prepareStatement(Conn.java:404)
    at org.sqlite.Conn.prepareStatement(Conn.java:399)
    at org.sqlite.Conn.prepareStatement(Conn.java:383)
    at anywheresoftware.b4j.objects.SQL.ExecNonQuery2(SQL.java:199)
    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.runVoidMethod(Shell.java:673)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:240)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    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.BA.raiseEvent2(BA.java:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:98)
    at aaa.erp2.mqtt_connector.main.start(main.java:38)
    at javafx.graphics/com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$9(LauncherImpl.java:846)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runAndWait$12(PlatformImpl.java:455)
    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)


1684921760655.png
 

Chris2

Active Member
Licensed User
This doesn't make a difference when I test it, but your database name has a lower case '.db' at the end, but your code sets the file name with capitals:
B4X:
'try changing
Dim fileName As String = "ERP2_SQLITE.DB"
'to
Dim fileName As String = "ERP2_SQLITE.db"
 
Last edited:

RickV

Member
you need the word main like below

Sqlite_Per_Vacuum.ExecNonQuery2("VACUUM main INTO ?", Array(backupPath))
 

amorosik

Expert
Licensed User
No one of the following line is correct:

B4X:
Dim filePath As String = "C:\PUBLIC\ERP2\Sqlite_Data"
Dim fileName As String = "ERP2_SQLITE.db"
Dim backupPath As String = "C:\PUBLIC\ERP2\Sqlite_Data\ERP2_SQLITE_BK.DB"      'backupPath must include the filename
    
Sqlite_Per_Vacuum.InitializeSQLite(filePath, fileName, False)
'Sqlite_Per_Vacuum.ExecNonQuery2("VACUUM      INTO ?", Array(backupPath))
'Sqlite_Per_Vacuum.ExecNonQuery2("VACUUM main INTO ?", Array(backupPath))
'Sqlite_Per_Vacuum.ExecNonQuery("VACUUM main INTO 'C:\\PUBLIC\\ERP2\\Sqlite_Data\\ERP2_SQLITE.db' ")
'Sqlite_Per_Vacuum.ExecNonQuery("VACUUM main INTO 'C:\PUBLIC\ERP2\Sqlite_Data\ERP2_SQLITE.db' ")
Sqlite_Per_Vacuum.ExecNonQuery("VACUUM      INTO 'C:\PUBLIC\ERP2\Sqlite_Data\ERP2_SQLITE.db' ")
Sqlite_Per_Vacuum.close
 

Daestrum

Well-Known Member
Licensed User
Longtime User
Maybe a long shot, have you tried using / in place of \ in the file paths.
Or escaping them \\ instead of \.
 

RickV

Member
try this also when you first initialize the db

B4X:
MySQL.ExecQuerySingleResult("PRAGMA journal_mode = wal")

so your code will look like this ?
B4X:
Dim filePath As String = "C:\PUBLIC\ERP2\Sqlite_Data"
Dim fileName As String = "ERP2_SQLITE.db"
Dim backupPath As String = "C:\PUBLIC\ERP2\Sqlite_Data\ERP2_SQLITE_BK.DB"    
   
Sqlite_Per_Vacuum.InitializeSQLite(filePath, fileName, False)
Sqlite_Per_Vacuum.ExecQuerySingleResult("PRAGMA journal_mode = wal")
Sqlite_Per_Vacuum.ExecNonQuery2("VACUUM main INTO ?", Array(backupPath))
Sqlite_Per_Vacuum.close

I have discovered in my experience so far, the vacume db must be a new or empty file, try the above first otherwise create a brand new backup file by setting the false to true at the end of your initialize line and choose a new filename for the excercise
Sqlite_Per_Vacuum.InitializeSQLite(filePath, fileName, False)
Sqlite_Per_Vacuum.InitializeSQLite(filePath, fileName, True)

I totally share your current frustration only a few days ago ☺
 

Chris2

Active Member
Licensed User
B4X:
'Sqlite_Per_Vacuum.ExecNonQuery2("VACUUM      INTO ?", Array(backupPath))
'Sqlite_Per_Vacuum.ExecNonQuery2("VACUUM main INTO ?", Array(backupPath))
'Sqlite_Per_Vacuum.ExecNonQuery("VACUUM main INTO 'C:\\PUBLIC\\ERP2\\Sqlite_Data\\ERP2_SQLITE.db' ")
'Sqlite_Per_Vacuum.ExecNonQuery("VACUUM main INTO 'C:\PUBLIC\ERP2\Sqlite_Data\ERP2_SQLITE.db' ")
Sqlite_Per_Vacuum.ExecNonQuery("VACUUM      INTO 'C:\PUBLIC\ERP2\Sqlite_Data\ERP2_SQLITE.db' ")
The last 3 listed won't work because you can't vacuum into the existing database.
Once I corrected those to be a new database name, all of the options listed work for me.

I don't think your problem is the code itself..

Are you running this on WIndows?
Also, is it a newly created database?
Check the SQLite version of the database with:
B4X:
SELECT sqlite_version();
 

amorosik

Expert
Licensed User
B4X:
'Sqlite_Per_Vacuum.ExecNonQuery2("VACUUM      INTO ?", Array(backupPath))
'Sqlite_Per_Vacuum.ExecNonQuery2("VACUUM main INTO ?", Array(backupPath))
'Sqlite_Per_Vacuum.ExecNonQuery("VACUUM main INTO 'C:\\PUBLIC\\ERP2\\Sqlite_Data\\ERP2_SQLITE.db' ")
'Sqlite_Per_Vacuum.ExecNonQuery("VACUUM main INTO 'C:\PUBLIC\ERP2\Sqlite_Data\ERP2_SQLITE.db' ")
Sqlite_Per_Vacuum.ExecNonQuery("VACUUM      INTO 'C:\PUBLIC\ERP2\Sqlite_Data\ERP2_SQLITE.db' ")
The last 3 listed won't work because you can't vacuum into the existing database.
Once I corrected those to be a new database name, all of the options listed work for me.

I don't think your problem is the code itself..

Are you running this on WIndows?
Also, is it a newly created database?
Check the SQLite version of the database with:
B4X:
SELECT sqlite_version();

SELECT sqlite_version() via SqLiteGui return 3.41.0
 

Chris2

Active Member
Licensed User
I have discovered in my experience so far, the vacume db must be a new or empty file, try the above first otherwise create a brand new backup file by setting the false to true at the end of your initialize line and choose a new filename for the excercise
Sqlite_Per_Vacuum.InitializeSQLite(filePath, fileName, False)
Sqlite_Per_Vacuum.InitializeSQLite(filePath, fileName, True)
Just to be clear, the database that you are vacuuming/backing up (i.e. "myDB.db" in the first post ) must exist.
While the database you're 'vacuuming into' must not - it will be created by the line
B4X:
sql.ExecNonQuery2("VACUUM INTO ?", Array(backupPath))

Edit: edited 2023-05-26 to make the statement more complete & clearer.
Clarification: As @RickV said in post #12, the database you are vacuuming into must be empty or not yet exist. From https://www.sqlite.org/lang_vacuum.html
The file named by the INTO clause must not previously exist, or else it must be an empty file, or the VACUUM INTO command will fail with an error.
 
Last edited:

RickV

Member
Just to be clear, the database that you are vacuuming must exist.
While the database you're 'vacuuming into' must not - it will be created by the line
B4X:
sql.ExecNonQuery2("VACUUM INTO ?", Array(backupPath))
Sqlite_Per_Vacuum.InitializeSQLite(filePath, fileName, False)
Sqlite_Per_Vacuum.InitializeSQLite(filePath, fileName, True)

these lines were to show what needed changing from false to true . I should have bolded the "true" as to avoid confusion.

My code in post #4 I think it was is working code from my current app.
delete backup if exists, vacum into new db, close and delete db thats vacumed then copy backup to original filename.

One other note is that if there are other references assigned / initialized to the db in question, there is a good chance of failure as the vacum wants exclusive access
 

Chris2

Active Member
Licensed User
My code in post #4 I think it was is working code from my current app.
delete backup if exists, vacum into new db, close and delete db thats vacumed then copy backup to original filename.
Understood 👍.

My confusion came from this bit....
Sqlite_Per_Vacuum.InitializeSQLite(filePath, fileName, False)
Sqlite_Per_Vacuum.InitializeSQLite(filePath, fileName, True)
these lines were to show what needed changing from false to true . I should have bolded the "true" as to avoid confusion.
The setting to True here creates the database if it doesn't already exist, right? Why would you want to create a backup copy of a database that doesn't exist? :)


there is a good chance of failure as the vacum wants exclusive access
Sorry to be pedantic, but this article is about 'VACUUM INTO', not 'VACUUM'.
'VACUUM INTO' does not need exclusive access to the source database.
From https://www.sqlite.org/lang_vacuum.html:
The VACUUM command with an INTO clause is an alternative to the backup API for generating backup copies of a live database.
 
Last edited:
Top