B4J Question Include directory in SQLite DB Browser (for encrypted files)

MicroDrie · Oct 16, 2021

The Share My Creation SQLite DB Browser (for encrypted files) from ilan give use the possibility to create and open an encrypted SQLite database. However, the database file is created in debug mode in the objects directory.

Initialize the encrypted SQLite database:

Sub initializeSQL
    dataDb.Initialize("org.sqlite.JDBC",$"jdbc:sqlite:file:${getDb}?cipher=sqlcipher&legacy=4&kdf_iter=256000&key=${getKey}"$)
End Sub

Sub getKey As String
    Return "xxxxxx" '<--- enter here your key (the same key is the password for the sqlite chiper app)
End Sub

Sub getDb as String
    xui.SetDataFolder("Databases")
    Dim DirString As String =  $"${xui.DefaultFolder}\\${getFileName}"
'   Change all single "\" to "\\"   
    Dim wNewWord As String = DirString.replace("\", "\\")
    Log(wNewWord)
    Return wNewWord
End Sub

Public Sub getFileName() As String
    Return "data.db"
End Sub

Calling getFileName creates the encrypted database in the objects directory, but calling getDb returns an java.lang.reflect.InvocationTargetException error code.

What is the correct way to open the encrypted database in the XUI.DefaultFolder?

Erel · Oct 17, 2021

Try this:

B4X:

 dataDb.Initialize("org.sqlite.JDBC",$"jdbc:sqlite:${File.Combine(XUI.DefaultFolder, "data.db").Replace("\", "/")}?cipher=sqlcipher&legacy=4&kdf_iter=256000&key=${getKey}"$)

MicroDrie · Oct 17, 2021

Unfortunately Erel's suggestion doesn't work either. The problem is that the separation of the different parameters is the colon ":". So as soon as a drive letter with a colon is passed in the given path command, the reading of the path parameter is aborted. We will have to do it with a filename without a path and via File.DirApp

Erel · Oct 18, 2021

Try:

B4X:

 dataDb.Initialize("org.sqlite.JDBC",$"jdbc:sqlite:/${File.Combine(XUI.DefaultFolder, "data.db").Replace("\", "/")}?cipher=sqlcipher&legacy=4&kdf_iter=256000&key=${getKey}"$)

MicroDrie · Oct 25, 2021

It works now withn the following updates:

Download the latest version of the xerial/sqlite-jdbc jar (at the moment of writing sqlite-jdbc-3.36.0.3.jar) from https://github.com/xerial/sqlite-jdbc/releases to the external lib directory

Add in the main page the

Latest sqlite-jdbc version in main page:

' Download from https://github.com/xerial/sqlite-jdbc/releases
    #AdditionalJar: sqlite-jdbc-3.36.0.3

Add in your B4XPage or module or class the default file name and storage location:

Default filename and location of SQLite encrypted database in the B4XPage module:

Public Sub getDbName As String
    Return "data.db"
End Sub

Public Sub getDbFileName As String
    xui.SetDataFolder("SQLtest")
    Dim Result As String = $"${xui.DefaultFolder}\${getDbName}"$
'    Dim Result As String = $"${getDbName}"$
    Log(Result)
    Return Result
End Sub

And use the connection string

Encrypted SQLite database connection string:

    dataDb.Initialize("org.sqlite.JDBC",$"jdbc:sqlite:file:${getDbFileName}?cipher=sqlcipher&legacy=4&kdf_iter=256000&key=${getKey}"$)
   
    If dataDb.ExecQuerySingleResult("SELECT count(name) FROM sqlite_master WHERE type='table' AND name ='table1'") = 0 Then
'        don't exist
        Log($"Table don't exist"$)
       
        QueryStr = _
$"CREATE TABLE IF NOT EXISTS table1 (
    "number"    INTEGER
);
"$
        dataDb.ExecNonQuery(QueryStr)
        Log("Table created")
        For i = 1 To 1000
            dataDb.AddNonQueryToBatch("INSERT INTO table1 VALUES (?)", Array(Rnd(0, 100000)))
        Next
        Dim SenderFilter As Object = dataDb.ExecNonQueryBatch("SQL")
        Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
        Log("NonQuery: " & Success)
        Log("Database file records exists")
    Else
        'exists
        Log("Database file exists")
        If dataDb.ExecQuerySingleResult("SELECT count(number) FROM table1") = 0 Then
'            No records
            Log("No records found!")
            For i = 1 To 1000
                dataDb.AddNonQueryToBatch("INSERT INTO table1 VALUES (?)", Array(Rnd(0, 100000)))
            Next
            Dim SenderFilter As Object = dataDb.ExecNonQueryBatch("SQL")
            Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
            Log("NonQuery: " & Success)
        Else
            'records found
            Log("Database file records exists")
        End If
    End If
   

    QueryStr = $"dataDb.ExecQuerySingleResult("SELECT count(number) FROM table1") "$
    Log(dataDb.ExecQuerySingleResult("SELECT count(number) FROM table1"))
   
    dataDb.Close

you can read the JDBC driver information with the following code:

Read SQLite JDBC driver information:

    Dim NativeMe As JavaObject    NativeMe = Me
    Log(NativeMe.RunMethod("Jdbc",Null))
   

#IF JAVA
//package net.codejava.jdbc;
 
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.List;
 
/**
 * This program demonstrates making JDBC connection to a SQLite database.
 * @author www.codejava.net
 *
 */
    public void Jdbc() {
        try {
            Class.forName("org.sqlite.JDBC");
            String dbURL = "jdbc:sqlite:product.db";
            Connection conn = DriverManager.getConnection(dbURL);
            if (conn != null) {
                System.out.println("Connected to the database");
                DatabaseMetaData dm = (DatabaseMetaData) conn.getMetaData();
                System.out.println("Driver name: " + dm.getDriverName());
                System.out.println("Driver version: " + dm.getDriverVersion());
                System.out.println("Product name: " + dm.getDatabaseProductName());
                System.out.println("Product version: " + dm.getDatabaseProductVersion());
                conn.close();
            }
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
#End If

B4J Question Include directory in SQLite DB Browser (for encrypted files)

MicroDrie

Well-Known Member

Erel

B4X founder

MicroDrie

Well-Known Member

Erel

B4X founder

MicroDrie

Well-Known Member

Similar Threads

B4J Question Include directory in SQLite DB Browser (for encrypted files)

MicroDrie

Well-Known Member

Erel

B4X founder

MicroDrie

Well-Known Member

Erel

B4X founder

MicroDrie

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency