B4J Question The JDBC could not open the encrypted database

[yshzsl]

Hi, everyone.
I am using the Jdbc 3.36 driver,But it cannot open my encrypted database jtjz.db(Using sqlcipher in B4A can open this database normally).Please help me.
Code is as follows:

#AdditionalJar: sqlite-jdbc-3.36.0.2


Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("MainPage")
    xui.SetDataFolder("jtjz")
    SQLDataBasePath=xui.DefaultFolder
    SQLDateBaseName="jtjz.db"
    If File.Exists(SQLDataBasePath,SQLDateBaseName)=False Then
        File.Copy(File.DirAssets,SQLDateBaseName,SQLDataBasePath,SQLDateBaseName)
    End If
    Dim db As String=File.Combine(SQLDataBasePath,SQLDateBaseName)
    Dim db1 As String=db.Replace("\","/")
    Log(db1)
    SQL1.Initialize("org.sqlite.JDBC",$"jdbc:sqlite:file:${db1}?cipher=sqlcipher&legacy=4&kdf_iter=256000&key=5678"$)
        
End Sub

Private Sub Button1_Click
    Dim bt As String=TextField1.Text.Trim
    Dim nr As String=TextArea1.Text
    sql="select count(*) from jtcy"   
    Dim co As Int=SQL1.ExecQuerySingleResult(sql)
    Log(co)
End Sub

Dim co As Int=SQL1.ExecQuerySingleResult(sql)
Error when running to this line of code,The error message is as follows:

Error occurred on line: 46 (B4XMainPage)
org.sqlite.SQLiteException: [SQLITE_NOTADB]  File opened that is not a database file (file is not a database)
    at org.sqlite.core.DB.newSQLException(DB.java:1030)
    at org.sqlite.core.DB.newSQLException(DB.java:1042)
    at org.sqlite.core.DB.throwex(DB.java:1007)
    at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
    at org.sqlite.core.NativeDB.prepare(NativeDB.java:137)
    at org.sqlite.core.DB.prepare(DB.java:257)
    at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:45)
    at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30)
    at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:25)
    at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:35)
    at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:241)
    at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:205)
    at anywheresoftware.b4j.objects.SQL.ExecQuery2(SQL.java:365)
    at anywheresoftware.b4j.objects.SQL.ExecQuerySingleResult2(SQL.java:420)
    at anywheresoftware.b4j.objects.SQL.ExecQuerySingleResult(SQL.java:409)
    at b4j.example.b4xmainpage._button1_click(b4xmainpage.java:117)
    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:564)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:629)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
    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:564)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA$1.run(BA.java:236)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:428)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
    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:832)

 

[Erel]

This jdbc driver doesn't support encrypted databases.

 

[yshzsl]

How can I connect to an encrypted database? Does B4J only connect to a non-encrypted database?

 

[Erel]

You need to find a JDBC driver that supports it. Check with SQLCipher.

B4J works with any valid JDBC driver.

 

[mcqueccu]

I tested these drivers and they work with sqlcipher

sqlite-jdbc-3.30.0 or sqlite-jdbc-3.31.1 or sqlite-jdbc-3.35.5.1

Get it here

Releases · Willena/sqlite-jdbc-crypt

SQLite JDBC Driver with encryption support. Contribute to Willena/sqlite-jdbc-crypt development by creating an account on GitHub.

github.com

 

[yshzsl]

Hello, mcqueccu.

Following your suggestion I used sqlite-jdbc-3.35.5.1, but still the same problem occurs.

In B4J, I did not use the Sqlcipher,It should only apply to B4A, because it cannot find the b4a.SQL class and cannot run.

Sqlcipher Is there a B4J version?

 

[mcqueccu]

Open Encrypted Sqlite Databases with B4J (SQLCipher)

Download the JDBC from this GitHub, and place it in your Additional folder In your Main Module, use #AdditionalJar to add your jdbc. Add JSQL library The following JDBC has been tested and works fine. You can use any of them '#AdditionalJar: sqlite-jdbc-3.30.0 '#AdditionalJar...

www.b4x.com

 

[yshzsl]

Hello, mcqueccu.
Following your suggestion, I downloaded the sqlite-jdbc-3.46.0.0.jar and the slf4j-api-1.7.36.jar and copy them to the additional library,When running the program, the database is able to be connected, but cannot execute the sql statement,The prompt information is that The open file is not a database file.
I ran your example, it can start the window, but an error occurred when clicking the ClickMe button, the error log is as follows:

WARNING: package com.sun.javafx.embed.swing.oldimpl not in javafx.swing
Waiting for debugger to connect...
Program started.
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
DB Started
Error occurred on line: 58 (Main)
org.sqlite.SQLiteException: [SQLITE_NOTADB] File opened that is not a database file (file is not a database)
    at org.sqlite.core.DB.newSQLException(DB.java:1179)
    at org.sqlite.core.DB.newSQLException(DB.java:1190)
    at org.sqlite.core.DB.throwex(DB.java:1150)
    at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
    at org.sqlite.core.NativeDB.prepare(NativeDB.java:132)
    at org.sqlite.core.DB.prepare(DB.java:264)
    at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:46)
    at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:32)
    at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:25)
    at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:34)
    at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:225)
    at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:205)
    at anywheresoftware.b4j.objects.SQL.ExecQuery2(SQL.java:365)
    at anywheresoftware.b4j.objects.SQL.ExecQuerySingleResult2(SQL.java:420)
    at anywheresoftware.b4j.objects.SQL.ExecQuerySingleResult(SQL.java:409)
    at b4j.example.main._button1_click(main.java:182)
    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:564)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:629)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
    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:564)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA$1.run(BA.java:236)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:428)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
    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:832)

It should be noted that:
I am able to open your database file test.db with DB_Broswer.
The system I use is windows11 and the jdk version is 14.
I don't know where the problem is,Please help me to analyze it.
Thank you.

 

[teddybear]

The jdbc works, how did you create jtjz.db? I guess it was created with non-encryped mode. comment the 3 lines, and delete the old db in SQLDataBasePath.

    'If File.Exists(SQLDataBasePath,SQLDateBaseName)=False Then
       ' File.Copy(File.DirAssets,SQLDateBaseName,SQLDataBasePath,SQLDateBaseName)
   ' End If

 

[mcqueccu]

Maybe the Database is not properly encrypted. Some software's do not do it well. I will advice you test it with the DB Brower and do new encryption. Try these.

Are you able to open your database with the DB Browser?
If yes, Is it Password Protected?
Does it use Version 3 Cipher or Version 4 Cipher?

You can do new encryption using the DB Browser. Go to Tools->Set Encryption.

 

[yshzsl]

I created the database jtjz.db with DB Browser for SQLCipher and set the password,The Version 4 Cipher was used.
The program runs well with the SQLCipher under B4A.
I did not change the test.db in the example of mcqueccu ,The same error occurred when I run the example.

 

[mcqueccu]

Upload a small project with your database (that is if your database does not contain any private data).

Alternatively, since my test.db is working for you, Open it with DB Browser, and import your tables into it and use.

NOTE: I have had similar problems before with other versions of DB Browsers. Make sure you are using DB Browser version 3.12.2

 

[yshzsl]

Hello, Mcqueccu.Thank you for your attention and support.

My DB Browser for SQLCiphe version is 3.12.2,I can open your database test.db and my database jtjz.db Using this browser.Your example works properly on your computer and not properly on mine,I think this is not about the database problem.

When running your example on my computer,It is able to connect to the database but unable to execute the SQL statement,I think there should be a problem with the system configuration or the B4J component configuration,But I don't know what the problem is.

 

[teddybear]

You'd better upload a small project

 

[yshzsl]

Here is the small project,Please help me to find what the problem is

 

[mcqueccu]

I run your project without changing ANYTHING and everything works.
Even if you dont Replace the slashes ( \ or / ) using db or db1 variables both work at my end.
I opened your Database with DB Browser too I see everything.

I think you should post the new error messages you are getting. Also answer this questions

1. Which version of B4J Are you using? Are you using the lastest Jsql library
2. Which operating System? Is it running in Virtual Machine?

 

[teddybear]

Here is the small project,Please help me to find what the problem is

As mcqueccu said, your project also works at my end. you can join the wechat group for help.

 

[yshzsl]

Thank you mcqueccu,teddybear. Thanks for trying to find the problem.

My operating environment is as follows:

Operating system: Windows 11 Professional Edition.
B4J：9.80.
JSQL：1.61.
Additional library：#AdditionalJar: slf4j-api-1.7.36
#AdditionalJar: sqlite-jdbc-3.46.0.0
remarks:The operating system operates on a desktop computer,The B4J was run directly at the Windows 11,No virtual machine is being used.

When running the project on my computer, the error message reads:

WARNING: package com.sun.javafx.embed.swing.oldimpl not in javafx.swing
Waiting for debugger to connect...
Program started.
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
Error occurred on line: 92 (B4XMainPage)
java.lang.reflect.InvocationTargetException
    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:564)
    at anywheresoftware.b4a.keywords.Common.CallSubDebug2(Common.java:486)
    at b4j.example.b4xpagesmanager._createpageifneeded(b4xpagesmanager.java:872)
    at b4j.example.b4xpagesmanager._showpage(b4xpagesmanager.java:351)
    at b4j.example.b4xpagesmanager._addpage(b4xpagesmanager.java:172)
    at b4j.example.b4xpagesmanager._addpageandcreate(b4xpagesmanager.java:186)
    at b4j.example.b4xpagesmanager._initialize(b4xpagesmanager.java:122)
    at b4j.example.main._appstart(main.java:80)
    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:564)
    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:564)
    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 b4j.example.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(AccessController.java:391)
    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:832)
Caused by: java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
    at anywheresoftware.b4a.debug.Debug.CallSub4(Debug.java:134)
    at anywheresoftware.b4a.debug.Debug.CallSubNew2(Debug.java:81)
    ... 35 more
Caused by: java.lang.reflect.InvocationTargetException
    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:564)
    at anywheresoftware.b4a.debug.Debug.CallSub4(Debug.java:115)
    ... 36 more
Caused by: org.sqlite.SQLiteException: [SQLITE_NOTADB] File opened that is not a database file (file is not a database)
    at org.sqlite.core.DB.newSQLException(DB.java:1179)
    at org.sqlite.core.DB.newSQLException(DB.java:1190)
    at org.sqlite.core.DB.throwex(DB.java:1150)
    at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
    at org.sqlite.core.NativeDB.prepare(NativeDB.java:132)
    at org.sqlite.core.DB.prepare(DB.java:264)
    at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:46)
    at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:32)
    at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:25)
    at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:34)
    at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:225)
    at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:205)
    at anywheresoftware.b4j.objects.SQL.ExecQuery2(SQL.java:365)
    at anywheresoftware.b4j.objects.SQL.ExecQuerySingleResult2(SQL.java:420)
    at anywheresoftware.b4j.objects.SQL.ExecQuerySingleResult(SQL.java:409)
    at b4j.example.b4xmainpage._combobox_init(b4xmainpage.java:124)
    at b4j.example.b4xmainpage._b4xpage_created(b4xmainpage.java:92)
    ... 41 more

An error occurred while executing a SQL statement.
Lline 92: Dim co As Int=SQL1.ExecQuerySingleResult(sql)