B4J Tutorial Backup SQLite DB with VACUUM INTO

Chris2 · Jan 7, 2023

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.

RickV · May 20, 2023

Chris2 said:
The bit that stood out to me was;
...
2. VACUUM can not be executed from within a transaction.

Thank you so much for sharing, like you said, there is so much that one cannot find ☺

amorosik · May 24, 2023

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 · May 24, 2023

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 · May 24, 2023

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 · May 24, 2023

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)

Chris2 · May 24, 2023

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"

RickV · May 24, 2023

you need the word main like below

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

Chris2 · May 24, 2023

As per @RickV code, you could try using:

B4X:

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

I have seen it mentioned that the schema name should be included, but I've never needed it - https://stackoverflow.com/questions/68534418/how-to-vacuum-into-in-sqlite

amorosik · May 24, 2023

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 · May 24, 2023

Maybe a long shot, have you tried using / in place of \ in the file paths.
Or escaping them \\ instead of \.

RickV · May 24, 2023

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 · May 24, 2023

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 · May 24, 2023

Chris2 said:
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 · May 25, 2023

amorosik said:
SELECT sqlite_version() via SqLiteGui return 3.41.0

Hmm. If you've tried everything suggested above I have no further suggestions I'm afraid.

You could try the code here instead - https://www.b4x.com/android/forum/t...uum-into-to-make-a-copy-of-an-open-db.145678/
Although it's basically the same with the addition of checks.

Chris2 · May 25, 2023

RickV said:
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.

RickV · May 25, 2023

Chris2 said:
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 · May 26, 2023

RickV said:
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?

RickV said:
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.

B4J Tutorial Backup SQLite DB with VACUUM INTO

RickV

Member

amorosik

Expert

RickV

Member

Chris2

Active Member

amorosik

Expert

Chris2

Active Member

RickV

Member

Chris2

Active Member

amorosik

Expert

Daestrum

Expert

RickV

Member

Chris2

Active Member

amorosik

Expert

Chris2

Active Member

Chris2

Active Member

RickV

Member

Chris2

Active Member

Similar Threads

B4J Tutorial Backup SQLite DB with VACUUM INTO

Member

Expert

Member

Active Member

Expert

Active Member

Member

Active Member

Expert

Expert

Member

Active Member

Expert

Active Member

Active Member

Member

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency