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

MicroDrie

Well-Known Member
Licensed User
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?
 

MicroDrie

Well-Known Member
Licensed User
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
 
Upvote 0

MicroDrie

Well-Known Member
Licensed User
It works now withn the following updates:
  1. 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
  2. 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
  3. Add in your B4XPage or module or class the default file name and storage location:

  4. 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
  5. 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
  6. 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
 
Upvote 0
Top