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

[MicroDrie]

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.

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]

Try this:

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

 

[MicroDrie]

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]

Try:

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

 

[MicroDrie]

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
   

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

       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:
   
   

       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