Android Question AGAIN Can not open SQlite Database

[Guenter Becker]

Hello again, I am back with an error I though i solved it but nope.

I am developing with the latest B4A 11.20. My SDKs are checked and OK.
The SQlite Database Version 3 is checked an can be open by a Databasemangement App.
My Problem is that I am not able to open the Sqlite Database Vers. 3. I have this Problem since updating on 11.20 not before!

1. in My Manifest I added: AddPermission(android.permission.WRITE_EXTERNAL_STORAGE)

'This code will be applied to the manifest file during compilation.
'You do not need to modify it in most cases.
'See this link for for more information: https://www.b4x.com/forum/showthread.php?p=78136
AddManifestText(
<uses-sdk android:minSdkVersion="20" android:targetSdkVersion="29"/>
<supports-screens android:largeScreens="true"
    android:normalScreens="true"
    android:smallScreens="true"
    android:anyDensity="true"/>)
SetApplicationAttribute(android:icon, "@drawable/icon")
SetApplicationAttribute(android:label, "$LABEL$")
CreateResourceFromFile(Macro, Themes.LightTheme)
'End of default text.
AddPermission(android.permission.RECORD_AUDIO)
AddPermission(android.permission.INTERNET)
AddPermission(android.permission.ACCESS_NOTIFICATION_POLICY)
AddPermission(android.permission.WRITE_EXTERNAL_STORAGE)

2. in Starter.Service_Create I added

        DBDir=DBUtils.CopyDBFromAssets("FDM.db")
        SQLite.Initialize(DBUtils.GetDBFolder,"FDM.db",False)

3. I connect my Huawei p20pro with Android 8 via B4xBridge.
4. I checked case sensivity of the database file name.
5. I run the code in Debug and in Release mode.
6. I got the error message

Error occurred on line: 42 (DBUtils)
android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (Sqlite code 14): Could not open database, (OS error - 2:No such file or directory)
    at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:225)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:209)
    at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:541)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:209)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:198)
    at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:936)
    at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:923)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:821)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:770)
    at anywheresoftware.b4a.sql.SQL.Initialize(SQL.java:44)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:777)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:354)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:193)
    at anywheresoftware.b4a.shell.DebugResumableSub$RemoteResumableSub.resume(DebugResumableSub.java:22)
    at anywheresoftware.b4a.keywords.Common$13.run(Common.java:1714)
    at android.os.Handler.handleCallback(Handler.java:809)
    at android.os.Handler.dispatchMessage(Handler.java:102)
    at android.os.Looper.loop(Looper.java:166)
    at android.app.ActivityThread.main(ActivityThread.java:7367)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:469)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:963)

7. I checked the directory on the phone and I see there is no database file copied!
8. Instead of DBUtils I tried is with file.copy but its running into the same problem.

I am at he end of my knowledge what is wrong and what is the solution. Can anyone help me please. Thank you.

 

[Guenter Becker]

Amendment:
Clean Install of B4A.
New Project and only with start_create: file copy to dir internal and sql.initialize- > same error checked: file was not copied.
Checked DIr and filenames twice.

 

[Erel]

1. This error has nothing to do with B4A version.
2.

AddPermission(android.permission.WRITE_EXTERNAL_STORAGE)

It will not do anything.

3. What is the output of this code:

 DBDir=DBUtils.CopyDBFromAssets("FDM.db")
Log(DBDir)
Log(DBUtils.GetDBFolder)
SQLite.Initialize(DBUtils.GetDBFolder,"FDM.db",False)

Best code:

Dim db As String = "FDM.db"
If File.Exists(XUI.DefaultFolder,  db) = False Then
 File.Copy(File.DirAssets, db, XUI.DefaultFolder, db)
End If
SQLite.Initialize(XUI.DefaultFolder, db, False)

 

[Guenter Becker]

Good Morning Erel,

The adding of the permission does nothing because I am writing to the internal directory. This line is just part of a my current trial and error jumping to solve the error.

Log(DBDir) and Log(DBUtils.GetDBFolder) leads to the same output: storage/emulated/0/Android/Data/TDFDM.PRO/Files.

It' s crazy sorry but I am at the end of several hours to change code and try to look into the forum and internet. and I am not able to understand what is wrong. I found that the error message is correct because in case of my different tries the database file is allways NOT copied! If I use True instead of False in the '.initialize' statement an empty database is created. I wonder because this shows me that the app has write access.

I used your code instead of mine and the situation is the same as before getting the same error and the database file is NOT copied!

 

[rboeck]

Try to name your database fdm.db; i remember a problem long time ago, but not exactly...

 

[Guenter Becker]

Thank you I did it also in lower case no change same error!

 

[TILogistic]

It may seem stupid.
Manually copy the database and see if you can access it from your application.
and thus be able to detect where the problem may be.

 

[Guenter Becker]

I tried this:

DBName = "fdm.db"
        Dim p As Phone
        Log(p.SdkVersion)
        If File.Exists(XUI.DefaultFolder,  DBName) = False Then
            File.Copy(File.DirAssets,DBName, XUI.DefaultFolder, DBName)
            Dim l As List: l.initialize
            l =File.ListFiles(XUI.DefaultFolder)
            Log(l)
        End If
        SQLite.Initialize(XUI.DefaultFolder, DBName, False)

and the Log result is:
Logger verbunden mit: HUAWEI CLT-L29
--------- beginning of crash
--------- beginning of system
--------- beginning of main
*** Service (starter) Create ***
27
(ArrayList) [virtual_assets, fdm.db]

Therefore the file should be in the folder!? But moving ahead same error:
android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (Sqlite code 14): Could not open database, (OS error - 2:No such file or directory)
raises.

I can not deal with this.

 

[Guenter Becker]

Hell oparra thank you for comming in.
In my trial and error I also copied the database file by filemanger manually into the directory. But there is no change same error!.

 

[klaus]

You should post a small project showing the problem, so we could test it and see what happens.

 

[TILogistic]

One more question, have you tried on another phone?

 

[Guenter Becker]

May be we are comming closer?
I tried this:

        ' get folders files don't trust file.exists
        Dim l As List: l.initialize
        l =File.ListFiles(XUI.DefaultFolder)
        Log(XUI.DefaultFolder)
        Log(l)
        Log(l.IndexOf(DBName))
        ' if the file exitst open database
        If l.IndexOf(DBName) > -1 Then
            SQLite.Initialize(XUI.DefaultFolder, DBName, False) 
        Else
            XUI.Msgbox2Async("Datenbank wurde nicht kopiert!", _
            "Fehler: Start create","OK","","",Main.imgError)
        End If

and got this logs:
/data/user/0/TDFDM.PRO/files
(ArrayList) [virtual_assets, fdm.db]
1

If I'm correct this shows that the file ist copied and is in the directory but if going to open (initialize) the database I got the same error.
I feel that the error is internally in the SQLite.Initialize(--) statement.

Checked the database with sqliteexpert: no problems - reports sqlite3.dll 3.37.0

 

[Erel]

1. Works fine here.
2. File.Exists works properly.

Tested with this code:

Sub Service_Create
    dbname = "fdm.db"
    If File.Exists(XUI.DefaultFolder,  dbname) = False Then
        File.Copy(File.DirAssets,dbname, XUI.DefaultFolder, dbname)
    End If
    SQLite.Initialize(XUI.DefaultFolder, dbname, False)
    Dim rs As ResultSet = SQLite.ExecQuery("SELECT name FROM sqlite_master WHERE type='table'")
    Do While rs.NextRow
        Log(rs.GetString("name"))
    Loop
    rs.Close
End Sub

Add File.Delete(XUI.DefaultFolder, dbname). Maybe there is an old corrupted copy left in the internal folder.

 

[klaus]

Works fine here too !

 

[TILogistic]

Huawei Y9

It tells me this error.

And I've tried other databases with no problems.

*** Service (starter) Create ***
starter_service_create (java line: 165)
android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (Sqlite code 14 SQLITE_CANTOPEN): Could not open database, (OS error - 2:No such file or directory)
    at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:226)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:210)
    at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:552)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:213)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:202)
    at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:958)
    at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:945)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:843)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:791)
    at anywheresoftware.b4a.sql.SQL.Initialize(SQL.java:44)
    at TDDBTest.PRO.starter._service_create(starter.java:165)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:213)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:193)
    at TDDBTest.PRO.starter.onCreate(starter.java:56)
    at android.app.ActivityThread.handleCreateService(ActivityThread.java:4150)
    at android.app.ActivityThread.access$2400(ActivityThread.java:273)
    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2055)
    at android.os.Handler.dispatchMessage(Handler.java:112)
    at android.os.Looper.loop(Looper.java:216)
    at android.app.ActivityThread.main(ActivityThread.java:7625)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:524)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:987)
java.lang.RuntimeException: Unable to create service TDDBTest.PRO.starter: java.lang.RuntimeException: android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (Sqlite code 14 SQLITE_CANTOPEN): Could not open database, (OS error - 2:No such file or directory)
    at android.app.ActivityThread.handleCreateService(ActivityThread.java:4169)
    at android.app.ActivityThread.access$2400(ActivityThread.java:273)
    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2055)
    at android.os.Handler.dispatchMessage(Handler.java:112)
    at android.os.Looper.loop(Looper.java:216)
    at android.app.ActivityThread.main(ActivityThread.java:7625)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:524)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:987)
Caused by: java.lang.RuntimeException: android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (Sqlite code 14 SQLITE_CANTOPEN): Could not open database, (OS error - 2:No such file or directory)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:250)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:193)
    at TDDBTest.PRO.starter.onCreate(starter.java:56)
    at android.app.ActivityThread.handleCreateService(ActivityThread.java:4150)
    ... 8 more

Other data base chinook;

 

[TILogistic]

code:

Sub Service_Create
    dbname = "fdm.db"
    If File.Exists(XUI.DefaultFolder,  dbname) Then
        File.Delete(XUI.DefaultFolder, dbname)
    End If
    File.Copy(File.DirAssets, dbname, XUI.DefaultFolder, dbname)
    SQLite.Initialize(XUI.DefaultFolder, dbname, False)
    Dim rs As ResultSet = SQLite.ExecQuery("SELECT name FROM sqlite_master WHERE type='table'")
    Do While rs.NextRow
        Log(rs.GetString("name"))
    Loop
    rs.Close
End Sub

 

[klaus]

I tested it with my Samsung Galaxy S10.

I tried to open the database with an external application, SQLite2009Pro, on the PC and I get a message database encrypted.
Do not know if this could be the problem.

 

[Guenter Becker]

Ok, from another projekt I have an older version of the database.

I deleted the problem version and copied the older one to be used -> it works?
I examined the difference between the databases with sqlitexpert: both databases structures are reported ok.

I'm not able to understand whats going wrong but I will look forward. I hope it will work in future as well. Let's see.
Sometimes ways may be magic/mysterious.

If I have problems again I will be back. Hope not.

Thank you very much to all of your assisstance.