Android Question Open sqlite databasefile fails with "SQLiteCantOpenDatabaseException" (code 14)

fredo

Well-Known Member
Licensed User
Longtime User
After a SQLite database file has been successfully downloaded, it is to be copied to another directory and opened there.

Although download and transport to the other directory are successful, a crash occurs when the SQLite object is initialized:
2018-10-26_10-20-12.jpg

B4X:
 ~i:** Activity (main) Resume **
 ~i:** Activity (main) Pause, UserClosed = false **
~i:*** Service (starter) Create ***
Using FileProvider? true
~i:** Service (starter) Start **
~i:** Activity (main) Create, isFirst = true **
~i:** Activity (main) Resume **
#-
#-
#-Sub btnDownloadDb_Click
#-Sub DownloadFile
#-  FileURL      : https://drive.google.com/open?id=1qgnalqWv-cchSMCF63R6Uo9QZgJ50p92
#-  LocalPath    : /data/user/0/b4a.example/files/shared/temp
#-  LocalFilename: myfile
~i:*** Service (httputils2service) Create ***
~i:** Service (httputils2service) Start **
#-
#-
#-Sub LogFilesInFolder, strFolder=/data/user/0/b4a.example/files/shared/temp
#-    myfile
#-
#-
#-Sub LogFilesInFolder, strFolder=/data/user/0/b4a.example/files/shared/data1
#-    datablock1.db
#-
#-
#-Sub ActivateDb, strDbFile=/data/user/0/b4a.example/files/shared/data1/datablock1.db
#-  FileExists=true
~e:main$ResumableSub_ActivateDbresume (java line: 411)
~e:android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
~e:    at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
~e:    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:210)
~e:    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:194)
~e:    at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:498)
~e:    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:205)
~e:    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:197)
~e:    at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:933)
~e:    at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:913)
~e:    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:809)
~e:    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:758)
~e:    at anywheresoftware.b4a.sql.SQL.Initialize(SQL.java:44)
~e:    at b4a.example.main$ResumableSub_ActivateDb.resume(main.java:411)
~e:    at b4a.example.main._activatedb(main.java:342)
~e:    at b4a.example.main$ResumableSub_btnDownloadDb_Click.resume(main.java:534)
~e:    at anywheresoftware.b4a.BA.checkAndRunWaitForEvent(BA.java:245)
~e:    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:185)
~e:    at anywheresoftware.b4a.BA$2.run(BA.java:365)
~e:    at android.os.Handler.handleCallback(Handler.java:790)
~e:    at android.os.Handler.dispatchMessage(Handler.java:99)
~e:    at android.os.Looper.loop(Looper.java:180)
~e:    at android.app.ActivityThread.main(ActivityThread.java:6861)
~e:    at java.lang.reflect.Method.invoke(Native Method)
~e:    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:438)
~e:    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:837)

B4X:
Sub btnDownloadDb_Click
    Log("#-")
    Log("#-")
    Log("#-Sub btnDownloadDb_Click")

    ' Cleanup the Testfolders before start
    EmptyFolder(Starter.Folder_temp)
    EmptyFolder(Starter.Folder_data1)

    Dim strUrl As String = "https://drive.google.com/open?id=1qgnalqWv-cchSMCF63R6Uo9QZgJ50p92" ' db, public link
    Dim strTempFile As String = "myfile"
    Dim strTargetFile As String = "datablock1.db"


    ' Download the file, check it and transfer to workfolder
    wait For (DownloadFile(strUrl, Starter.Folder_temp, strTempFile)) complete(strReturn As String)
    LogFilesInFolder(Starter.Folder_temp)
 
    'wait for (<some screening checks of the file are done>) complete(bolRet as Boolean)
 
    wait for (File.CopyAsync(Starter.Folder_temp, strTempFile, Starter.Folder_data1, strTargetFile)) complete(Success As Boolean)
    LogFilesInFolder(Starter.Folder_data1)
 
    wait for (ActivateDb(File.Combine(Starter.Folder_data1, strTargetFile))) complete(Success As Boolean)
 
    MsgboxAsync("Download and open result=" & Success, "Result")
 
End Sub
'
Sub DownloadFile(FileURL As String, LocalPath As String, LocalFilename As String) As ResumableSub
    Log("#-Sub DownloadFile")
    Log("#-  FileURL      : " & FileURL)
    Log("#-  LocalPath    : " & LocalPath)
    Log("#-  LocalFilename: " & LocalFilename)
 
    Dim h_dl As HttpJob
    h_dl.Initialize("",Me)
    h_dl.Download(FileURL)
   
    Wait For (h_dl) JobDone(h_dl As HttpJob)
    If h_dl.Success Then
   
        Dim inStr As InputStream
        Dim out As OutputStream

        If LocalPath= "" Then LocalPath= Starter.Provider.SharedFolder
        inStr = h_dl.GetInputStream
        out = File.OpenOutput(LocalPath, LocalFilename, False)
        wait for (File.Copy2Async(inStr, out)) complete(Success As Boolean)
        out.Close

        h_dl.Release
        Return "{OK}"
   
    Else
        h_dl.Release
        Return "{ERR}"
   
    End If
End Sub
'
Sub ActivateDb(strDbFile As String) As ResumableSub
    Log("#-")
    Log("#-")
    Log("#-Sub ActivateDb, strDbFile=" & strDbFile)
    Log("#-  FileExists=" & File.Exists("", strDbFile) )
 
    If Not(File.Exists("", strDbFile)) Then
        Return False
    End If
 
    If sqlx.IsInitialized Then sqlx.Close
 
    sqlx.Initialize(Starter.Folder_data1, strDbFile, False)
    Dim res As ResultSet = sqlx.ExecQuery("SELECT name FROM sqlite_master WHERE type='table'")
    Do While res.NextRow
        Log("#-  " & res.GetString("name"))
    Loop
    Log("#-")
    Log("#-")
    Return True
End Sub

For the directories "SharedFolder" from Erel's FileProvider class was used. Since the target file is stored without errors in the designated folder, I exclude a permission problem at the moment.

Can someone help me to get a grip on solving the sql open problem?

A small testproject is attached.
 

Attachments

  • downloadopentest03.zip
    23.6 KB · Views: 266
Last edited:

fredo

Well-Known Member
Licensed User
Longtime User
(just a thought)

Thanks, tried it - no joy.

I've uploaded a new testproject to #1.
  • Permissions integrated
  • Test File.Writestring: OK
  • Test FileCopy local db: OK
  • Test DownloadFile to local folder: OK
  • SQLx.Initialize(): fail

2018-10-26_18-07-39.jpg
The db files were tested as errorfree with the newest version of SQLite Expert Professional

As someone who has been developing databases with SQLite for the Android platform for several years without any problems, I am faced with a puzzle...

Attached are the used database files if someone is interested
 

Attachments

  • dba0_dba1.zip
    632 bytes · Views: 258
Upvote 0

mangojack

Well-Known Member
Licensed User
Longtime User
The error is in the sqlx.Initialise line.

wait for (ActivateDb(File.Combine(Starter.Folder_data1, strLocalDbFile))) complete(Success As Boolean)

B4X:
Sub ActivateDb(strDbFile As String) As ResumableSub
 
    If Not(File.Exists("", strDbFile)) Then   '@@@ OK here ...
        Return False
    End If
 
    If sqlx.IsInitialized Then sqlx.Close
 
    sqlx.Initialize(Starter.Folder_data1, strDbFile, False)  '@@@ then this ...
 
'.....................


Should be ...
B4X:
sqlx.Initialize("", strDbFile, False)
 
Upvote 0

fredo

Well-Known Member
Licensed User
Longtime User
Should be ...

Of course, Thanks.

Fixed it, retried it - no joy.

I've uploaded new testproject "03" to #1.
  • Permissions integrated
  • Test File.Writestring: OK
  • Test Database CREATE local db, SQLx.Initialize(): OK
  • Test with FileCopy local db, SQLx.Initialize(): OK
  • Test DownloadFile to local folder: OK
  • Test with DownloadFile db, SQLx.Initialize(): fail
D/SQLiteConnection(3833): DB info: open connection, path: /data/user/0/b4a.example/files/shared/data1/datablock1.db, handle: 0x71ed188300, flag: 0x2, FH handler: 0x71ed1d7ec0, count: 1

E/SQLiteLog(3833): (26) file is encrypted or is not a database
D/SQLiteDBG(3833): func: nativePrepareStatement, confirmed: 2
D/SQLiteDBG(3833): handleSQLiteError, path: /data/user/0/b4a.example/files/shared/data1/datablock1.db, handle: 0x71ed188300, errCode: 26, msg: file is encrypted or is not a database

Relevant part:
I/B4A(3833): #-Sub ActivateDb, strDbFile =/data/user/0/b4a.example/files/shared/data1/datablock1.db
I/B4A(3833): #- FileExists=true
I/B4A(3833): #- Trying: sqlx.Initialize("", "/data/user/0/b4a.example/files/shared/data1/datablock1.db", false)

D/SQLiteConnection(3833): DB info: open connection, path: /data/user/0/b4a.example/files/shared/data1/datablock1.db, handle: 0x71ed188300, flag: 0x2, FH handler: 0x71ed1d7ec0, count: 1

E/SQLiteLog(3833): (26) file is encrypted or is not a database
D/SQLiteDBG(3833): func: nativePrepareStatement, confirmed: 2
D/SQLiteDBG(3833): handleSQLiteError, path: /data/user/0/b4a.example/files/shared/data1/datablock1.db, handle: 0x71ed188300, errCode: 26, msg: file is encrypted or is not a database


D/SQLiteDBG(3833): Dump FDs:
D/SQLiteConnection(3833): DB info: close connection, path: /data/user/0/b4a.example/files/shared/data1/datablock1.db, handle: 0x71ed188300, FH handler: 0x71ed1d7ec0, count: 0
E/DefaultDatabaseErrorHandler(3833): Corruption reported by sqlite on database: /data/user/0/b4a.example/files/shared/data1/datablock1.db
E/DefaultDatabaseErrorHandler(3833): deleting the database file: /data/user/0/b4a.example/files/shared/data1/datablock1.db
D/SQLiteDBG(3833): DB info: /data/user/0/b4a.example/files/shared/data1/datablock1.db doesn't exist when open
E/SQLiteLog(3833): (14) cannot open file at line 35807 of [605907e73a]
E/SQLiteLog(3833): (14) os_unix.c:35807: (2) open(/data/user/0/b4a.example/files/shared/data1/datablock1.db) -
E/SQLiteConnection(3833): DB info: sqlite3_open_v2, path: /data/user/0/b4a.example/files/shared/data1/datablock1.db, flag: 2, ret: 14
E/SQLiteConnection(3833): DB info: errno = 2, errno message = No such file or directory
E/SQLiteDatabase(3833): Failed to open database '/data/user/0/b4a.example/files/shared/data1/datablock1.db'.
E/SQLiteDatabase(3833): android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
E/SQLiteDatabase(3833): at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
E/SQLiteDatabase(3833): at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:210)
E/SQLiteDatabase(3833): at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:194)
E/SQLiteDatabase(3833): at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:498)
E/SQLiteDatabase(3833): at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:205)
E/SQLiteDatabase(3833): at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:197)
E/SQLiteDatabase(3833): at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:933)
E/SQLiteDatabase(3833): at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:916)
E/SQLiteDatabase(3833): at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:809)
E/SQLiteDatabase(3833): at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:758)
E/SQLiteDatabase(3833): at anywheresoftware.b4a.sql.SQL.Initialize(SQL.java:44)
E/SQLiteDatabase(3833): at b4a.example.main$ResumableSub_ActivateDb.resume(main.java:415)
E/SQLiteDatabase(3833): at b4a.example.main._activatedb(main.java:342)
E/SQLiteDatabase(3833): at b4a.example.main$ResumableSub_btnDownloadDb_Click.resume(main.java:734)
E/SQLiteDatabase(3833): at anywheresoftware.b4a.BA.checkAndRunWaitForEvent(BA.java:245)
E/SQLiteDatabase(3833): at anywheresoftware.b4a.BA.raiseEvent2(BA.java:185)
E/SQLiteDatabase(3833): at anywheresoftware.b4a.BA$2.run(BA.java:365)
E/SQLiteDatabase(3833): at android.os.Handler.handleCallback(Handler.java:790)
E/SQLiteDatabase(3833): at android.os.Handler.dispatchMessage(Handler.java:99)
E/SQLiteDatabase(3833): at android.os.Looper.loop(Looper.java:180)
E/SQLiteDatabase(3833): at android.app.ActivityThread.main(ActivityThread.java:6861)
E/SQLiteDatabase(3833): at java.lang.reflect.Method.invoke(Native Method)
E/SQLiteDatabase(3833): at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:438)
E/SQLiteDatabase(3833): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:837)
I/B4A(3833): ~e:main$ResumableSub_ActivateDbresume (java line: 415)
I/B4A(3833): ~e:android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database

So, only the downloaded dba1.db raises "msg: file is encrypted or is not a database" although the structure is almost the same:

2018-10-27_14-00-40.jpg
I hope it's a mistake due to a lack of knowledge. Because the function "Download and open database" is an essential part of the current project.

 
Upvote 0

mangojack

Well-Known Member
Licensed User
Longtime User
All I can offer is the db file is being downloaded and copied to the working folder OK.

The call ... sqlx.Initialise results in the file being renamed from datablock1.db TO datablock1.db.back ??

Hopefully someone with more SQL neurons can shed some light.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
I modified your second project (the one where you have versionCode 2) before the 3rd one you posted. I was able to open and view data from the local database using the local database button, but when downloading the file datablock1.db, the download works, but the database datablock1.db does not open when I copied it to a PC and tried to open it using SQLite Expert Professional. It said: 'File is encrypted or not a database' There is something wrong with the file, that is why it would not initialize. Please see attached project: I also included a copy of the database downloaded that will not open. I changed its extension from db to txt so I can upload it to the forum
 

Attachments

  • FredoDownload102718.zip
    23 KB · Views: 236
  • datablock1.txt
    131.4 KB · Views: 230
Last edited:
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
Change the URL used to

https://drive.google.com/uc?export=download&id=1qgnalqWv-cchSMCF63R6Uo9QZgJ50p92

PD: The Example app now reports SUCCESS

Logger verbunden mit: 988ad036525346515630
--------- beginning of main
--------- beginning of system
*** Service (starter) Create ***
p.SdkVersion=26
Using FileProvider? true
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
sending message to waiting queue (activity_permissionresult)
running waiting messages (1)
** Activity (main) Resume **
>
>
#-Sub btnDownloadDb_Click
#-Sub DownloadFile
#- FileURL : https://drive.google.com/uc?export=download&id=1qgnalqWv-cchSMCF63R6Uo9QZgJ50p92
#- LocalPath : /data/user/0/b4a.example/files/shared/temp
#- LocalFilename: myfile
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
>
>
#-Sub LogFilesInFolder, strFolder=/data/user/0/b4a.example/files/shared/temp
#- >> btnDownloadDb_Click 1
#- >> myfile
>
>
#-Sub LogFilesInFolder, strFolder=/data/user/0/b4a.example/files/shared/data1
#- >> btnDownloadDb_Click 2
#- >> datablock1.db
>
>
#-Sub ActivateDb, strDbFile =/data/user/0/b4a.example/files/shared/data1/datablock1.db
#- FileExists: true
#- Trying: sqlx.Initialize("", "/data/user/0/b4a.example/files/shared/data1/datablock1.db", false)
#- Table found: a1
#- Result = OK
>
>
** Activity (main) Pause, UserClosed = false **
** Activity (main) Resume **

Check this: https://www.labnol.org/internet/direct-links-for-google-drive/28356/
 

Attachments

  • examplenewurl.zip
    23.6 KB · Views: 243
Last edited:
Upvote 0

Mahares

Expert
Licensed User
Longtime User
Change the URL used to
Now that you fixed the URL, I added a little bit of code to the Sub ActivateDb as shown below to your code and now I can display data from the downloaded database:

B4X:
Sub ActivateDb(strDbFile As String, bolCreateIfNecessary As Boolean) As ResumableSub
    Log(">")
    Log(">")
    Log("#-Sub ActivateDb, strDbFile =" & strDbFile)
    Log("#-  FileExists: " & File.Exists("", strDbFile) )
    '
    If Not(File.Exists("", strDbFile)) And Not(bolCreateIfNecessary) Then
        Return False
    End If
    '
    If sqlx.IsInitialized Then sqlx.Close
    '
    Log($"#-  Trying:  sqlx.Initialize("", "${strDbFile}", ${bolCreateIfNecessary})"$)
    sqlx.Initialize("", strDbFile, bolCreateIfNecessary) ' <-- here ~~~ ~~~ ~~~ ~~~ ~~~ ~~~ ~~~ ~~~ ~~~ ~~~ ~~~ ~~~
 
    Dim res As ResultSet = sqlx.ExecQuery("SELECT name, sql FROM sqlite_master WHERE type='table'")
    Do While res.NextRow
'        Log("#-    " & res.GetString("name"))
        LogColor(res.GetString("name") & CRLF & res.GetString("sql"), Colors.Blue )
    Loop
    res.Close 
    Log("#-  Result = OK")
    Log(">")
    Log(">")
 
    Dim rs As ResultSet  
    Dim rs As ResultSet = sqlx.ExecQuery("SELECT id1, aname FROM a1")
    Do While rs.NextRow
        LogColor($"${rs.Getstring("id1")}       ${rs.Getstring("aname")}"$, Colors.Magenta)
    Loop
    rs.Close
 
    Return True
End Sub
Here is some of the data from the 2 records in table a1 (columns: id1 and aname):
9825d53cc4af6ea87406fb74895ee60b eProject
9239bf165778bd6927adc3f4fe69dc03 eStorage
 
Upvote 0

fredo

Well-Known Member
Licensed User
Longtime User
Change the URL

Thanks so much for the support, guys.

I can confirm the successful download with the adapted link.

However.
When using the same link "https://drive.google.com/uc?export=download&id=1qgnalqWv-cchSMCF63R6Uo9QZgJ50p92" in the main project, an HTML document is loaded instead of the expected file.​

2018-10-28_09-36-32.jpg

2018-10-28_08-57-04.jpg

The only difference between the test project and the main project is that in the main project the user is logged in using OAuth2 with the scope "https://www.googleapis.com/auth/drive".​

Will investigate further...
 
Upvote 0
Top