B4J Question Error SQL.initialize - Why?

[Guenter Becker]

Hello, I have some unexpected and for me not understandable Problems with SQL. I please you experts to help me.

I have two B4J Projects (B4J 10.7)

The 1st. Project does its Job.

#Region Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 600
#End Region

    #AdditionalJar: sqlite-jdbc-3.53.2.0.jar
    #AdditionalJar: slf4j-api-1.7.36.jar
Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Private xui As XUI
    Private Button1 As B4XView
    
    Dim sql As SQL
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    MainForm.RootPane.LoadLayout("Layout1")
    MainForm.Show
    
    Sleep(0)
    
    If sql.IsInitialized = False Then
        Dim DBPath = "C:\Users\guent\appdata\roaming\techdoc\td_lms\td_lms.db"
        sql.Initialize("org.sqlite.JDBC", _
            $"jdbc:sqlite:file:${DBPath}?cipher=sqlcipher&legacy=4&key=1234&kdf_iter=256000"$)
        Log("DB Started")
    End If
End Sub

The 2nd does it not WHY?

'# open db access
    Dim DBPath = "C:\Users\guent\appdata\roaming\techdoc\td_lms\td_lms.db"
    sql.Initialize("org.sqlite.JDBC", _
        $"jdbc:sqlite:file:${DBPath}?cipher=sqlcipher&legacy=4&key=1234&kdf_iter=256000"$)
    If sql.IsInitialized = False Then
        fx.Msgbox2(Root,"No Database connection!" & CRLF & "App will be closed!", _
            "Connect Database","OK","","",fx.MSGBOX_ERROR)
    End If

ERROR:

Waiting for debugger to connect...
Program started.
Error occurred on line: 184 (B4XMainPage)
java.sql.SQLException: path to 'file:C:\Users\guent\appdata\roaming\techdoc\td_lms\td_lms.db?cipher=sqlcipher&legacy=4&key=1234&kdf_iter=256000': 'G:\B4X_Development\Framework\Project inArbeit\B4J\TD_LMS_Example\B4J\Objects\file:C:' does not exist
    at org.sqlite.Conn.open(Conn.java:103)
    at org.sqlite.Conn.<init>(Conn.java:57)
    at org.sqlite.JDBC.createConnection(JDBC.java:77)
    at org.sqlite.JDBC.connect(JDBC.java:64)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:683)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:230)
    at anywheresoftware.b4j.objects.SQL.Initialize2(SQL.java:58)
    at anywheresoftware.b4j.objects.SQL.Initialize(SQL.java:47)
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
    at java.base/java.lang.reflect.Method.invoke(Method.java:578)
    at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:676)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:240)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
    at java.base/java.lang.reflect.Method.invoke(Method.java:578)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:117)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:104)
    at anywheresoftware.b4a.shell.DebugResumableSub$RemoteResumableSub.resume(DebugResumableSub.java:21)
    at anywheresoftware.b4a.keywords.Common$2$1.run(Common.java:1114)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:457)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:399)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:456)
    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:184)
    at java.base/java.lang.Thread.run(Thread.java:1589)

Both Projects are using the same Additional JAR Files the same jSQL lib the same database file and same way to initialize sql.
I checked the folders: the database file is existing I am not able to understand where the error part "'G:\B4X_Development\Framework\Project inArbeit\B4J\TD_LMS_Example\B4J\Objects\file:C:' does not exist" comes from.
I setup a new Project configuration.
I tried around with different code possibilities but ended always in the same error as shown.

What is your opinion? What to do?

 

[Swissmade]

Maybe this can help
I use this in my applications

Public Sub InitSQLite(SQLRef As SQL, tmpDBLocation As String, DBName As String, FasterWrites As Boolean) As Boolean 'As ResumableSub
    Try
        Dim DBExists As Boolean = File.Exists(tmpDBLocation, DBName)
        If DBExists Then
            Dim fsize As Long = File.Size(tmpDBLocation, DBName)
            If fsize < 1024 Then Log("⚠️ Warning: DB file is very small, might be corrupted.")
        End If
        SQLRef.InitializeSQLite(tmpDBLocation, DBName, Not(DBExists))  'If true we have the DB so make it false no new table
    
        SQLRef.ExecNonQuery("PRAGMA journal_mode = WAL")       ' Enable Write-Ahead Logging for concurrency
        SQLRef.ExecNonQuery("PRAGMA busy_timeout = 3000")      ' Allow 3s wait if DB is locked
        If FasterWrites = True Then
            SQLRef.ExecNonQuery("PRAGMA synchronous = NORMAL")
            Log("SQLite: Using FAST write mode (synchronous=NORMAL)")
        Else
            SQLRef.ExecNonQuery("PRAGMA synchronous = FULL")
            Log("SQLite: Using SAFE write mode (synchronous=FULL)")
        End If
        Return True
    Catch
        modFunctions.ShowLog("Error: Database is corrupt", True, LastException.Message)
        Return False
    End Try
End Sub

 

[LucaMs]

#AdditionalJar: sqlite-jdbc-3.53.2.0.jar #AdditionalJar: slf4j-api-1.7.36.jar

Are those two lines the same in both projects?

 

[Daestrum]

Did you put the additional jar lines in B4XMainPage instead of Main?

 

[Guenter Becker]

Hello,
I tried it at the header of the B4X MainPage but got the error "Additional JAR not supported"
so put it back to the main

 

[Guenter Becker]

Are those two lines the same in both projects?

Exactly the same and pointing to the same JAR in the extended lib folder.

 

[Guenter Becker]

Maybe this can help
I use this in my applications

Public Sub InitSQLite(SQLRef As SQL, tmpDBLocation As String, DBName As String, FasterWrites As Boolean) As Boolean 'As ResumableSub
    Try
        Dim DBExists As Boolean = File.Exists(tmpDBLocation, DBName)
        If DBExists Then
            Dim fsize As Long = File.Size(tmpDBLocation, DBName)
            If fsize < 1024 Then Log("⚠️ Warning: DB file is very small, might be corrupted.")
        End If
        SQLRef.InitializeSQLite(tmpDBLocation, DBName, Not(DBExists))  'If true we have the DB so make it false no new table
   
        SQLRef.ExecNonQuery("PRAGMA journal_mode = WAL")       ' Enable Write-Ahead Logging for concurrency
        SQLRef.ExecNonQuery("PRAGMA busy_timeout = 3000")      ' Allow 3s wait if DB is locked
        If FasterWrites = True Then
            SQLRef.ExecNonQuery("PRAGMA synchronous = NORMAL")
            Log("SQLite: Using FAST write mode (synchronous=NORMAL)")
        Else
            SQLRef.ExecNonQuery("PRAGMA synchronous = FULL")
            Log("SQLite: Using SAFE write mode (synchronous=FULL)")
        End If
        Return True
    Catch
        modFunctions.ShowLog("Error: Database is corrupt", True, LastException.Message)
        Return False
    End Try
End Sub

Good Idea but will not work! Because I am using a SQLCipher Database and therefor need to use a provider string in the sql.initialize.

 

[stevel05]

You are not logged into different accounts? Getting desperate

 

[Guenter Becker]

You are not logged into different accounts? Getting desperate

I'm sorry, but maybe I'm a bit stupid right now but I don't understand your answer. What do you mean with different accounts?
I'm using the same B4J the same database the same libs/jar and the same ini-code in both projects. Tried also in a clean project with P4XPages and without. Will not work. Using the Project of shown 1st code it is working. Far away to understand why one works and one not.

 

[stevel05]

Just clutching as straws, but if you logged onto the computer with different accounts, the user directory will be different.

If you are working in one session, then that's not the case.

 

[Guenter Becker]

Just clutching as straws, but if you logged onto the computer with different accounts, the user directory will be different.

If you are working in one session, then that's not the case.

I am working with one account and checked the dehault folder/DataFolder.
In the meantime I upgraded to
#AdditionalJar: sqlite-jdbc-3.54.1.0.jar
#AdditionalJar: slf4j-api-2.0.20.jar
#AdditionalJar: slf4j-nop-2.0.20.jar

same error.

 

[stevel05]

Are you running one in release mode and the new one in debug mode? or is it the same in Release mode?

 

[Guenter Becker]

Trial and Error again:

sql.Initialize("org.sqlite.JDBC", $"jdbc:sqlite:file:TD_LMS.db?cipher=sqlcipher&legacy=4&key=1234&kdf_iter=25600"$) WORKING!
- DBFile in Project Folder \Objects

Dim DBPath As String = "C:\Users\guent\appdata\roaming\techdoc\td_lms\td_lms.db"
sql.Initialize("org.sqlite.JDBC",$"jdbc:sqlite:file:${DBPath}?cipher=sqlcipher&legacy=4&key=1234&kdf_iter=25600"$) NOT WORKING same error
- Path checked: existing, DB-File existing

-Release/Debug no difference