Android Question [SOLVED] Cannot open SQLite database

[RKM904]

I have a simple app which includes a small SQLite database. (experimenting with including a db with the app)

I included the db in the files folder and according to information from various forum posts it should get installed in the Files.DirAssets folder when deployed to the phone
When I attempt to initialize the database the app fails with a SQL Error "android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 1294 SQLITE_CANTOPEN_ENOENT[1294]): Could not open database" The app fails on line 45 of the included code

Researching the error (in B4X forums and google) it seems the most likely explanation for the error is that the database cannot be found (i.e. does not exist in the folder)

As part of error investigation I subsequently included code in the app which logs the file names that are present in the Files.DirAssets prior to the error occurring and the database file appears in the log - (see attached log). I also opened the file with SQLlite Browser from the files folder and it opens with no errors and the data is present.

I have been struggling with this error for the better part of yesterday and today - so much so that I'm concerned I'm missing something obvious. Therefore any help from someone taking a fresh look at the code and the logs would be much appreciated.

Thanks
RKM

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Dim SQL1 As SQL
    Dim RowNumber As Int



End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.

    Private btnShowFiles As Button
    Private SpinnerCourses As Spinner
    Private SpinnerMap As Map
    Private CourseID As Int
End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    Activity.LoadLayout("Main")
   
   
    Dim filelist As List    
    filelist.Initialize
    filelist = File.ListFiles(File.DirAssets)   '<----------  get a list of all included files
    For i = 0 To filelist.Size-1
        Log(filelist.Get(i)) ' <------------------------------log filenames                
    Next
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub


Sub btnShowFiles_Click
   
    SQL1.Initialize(File.DirAssets, "escscore.db", True)  '<------- initialize the database and  
    Dim Row As Int
    Dim Cursor1 As Cursor
    SpinnerMap.Initialize
    SpinnerCourses.Clear
   
    Cursor1 = SQL1.ExecQuery("SELECT ID,CourseName FROM Courses") '<--- get name and id from db table
    If Cursor1.RowCount > 0 Then
        RowNumber=Cursor1.RowCount
        For Row = 0 To RowNumber - 1
            Cursor1.Position=Row
            SpinnerCourses.Add(Cursor1.GetString("CourseName"))
            SpinnerMap.Put(Cursor1.GetString("CourseName"),Cursor1.GetInt("ID"))
        Next
    End If
    Cursor1.Close
End Sub


Sub SpinnerCourses_ItemClick (Position As Int, Value As Object)
    CourseID=SpinnerMap.Values
    ToastMessageShow(CourseID,True)
End Sub

 

[mangojack]

You cannot work with the assets db file .... you must copy it to another directory first.

It is better to test for db existence / copy and initialize it in the Starter Service ...

Starter Service - Consistent & Single Entry Point

One of the challenges that developers of any non-small Android app need to deal with, is the multiple possible entry points. During development in almost all cases the application will start from the Main activity. Many programs start with code similar to: Sub Activity_Create (FirstTime As...

www.b4x.com

for example .. (Starter Service module)

Sub Process_Globals
    Public SQL1 As SQL
End Sub

Sub Service_Create

    'check if db exists .. if not copy default db from assets ...
    If Not(File.Exists(File.DirInternal,"escscore.db"))  Then
        File.Copy(File.DirAssets,"escscore.db", File.DirInternal, "escscore.db")
    End If

    'initialize the database ..do NOT create new file as we have already tested for existance ..
    SQL1.Initialize (File.DirInternal,"escscore.db",False)
End Sub

then anywhere in other App modules you can use the SQL object by calling Starter.SQL1 ie:

Cursor1 = Starter.SQL1.ExecQuery("SELECT ID,CourseName FROM Courses") '<--- get name and id from db table

 

[RKM904]

MJ
Thanks very much. Works perfectly. I really appreciate your rapid response and your great advice. I'll mark the question SOLVED

Regards,
RKM