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:

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]

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]

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?

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]

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

    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]

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]

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

#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

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]

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:

'try changing
Dim fileName As String = "ERP2_SQLITE.DB"
'to
Dim fileName As String = "ERP2_SQLITE.db"

 

[RickV]

you need the word main like below

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

 

[Chris2]

As per @RickV code, you could try using:

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]

No one of the following line is correct:

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]

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

 

[RickV]

try this also when you first initialize the db

MySQL.ExecQuerySingleResult("PRAGMA journal_mode = wal")

so your code will look like this ?

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]

'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:

SELECT sqlite_version();

 

[amorosik]

'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:

SELECT sqlite_version();

SELECT sqlite_version() via SqLiteGui return 3.41.0

 

[Chris2]

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]

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

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]

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

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]

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.