Android Question Sql can't find table

[Bryan]

I know this has been mentioned so many times before me and I've looked at so many posts already about it. I tried many different options to make this work and regardless no success. I think it is the many different variations of code that makes it so confusing.

What I have done is created a database with SQLite Expert program in windows called "time.db" I created 2 tables in this db. One called "main" the other called "android_metadata". I read that a table called android_metadata needs to be made or android OS will not be able to use this database. So, I created it and added a text field called "locale" with a default value of "en_US". I also read that I needed to create an Index in my "main" table as "_id" as an integer and primary key so I did that too.

I copied my time.db to my file assets directory and added it with the file manger in B4A.

Now for my code:

This is from an activity module. It is not the complete code but it contains the code that fails.

Sub Process_Globals
Dim SQL1 As SQL   
End Sub

Sub Globals
    Dim su As StringUtils
End Sub

Sub Activity_Create(FirstTime As Boolean)
        DBUtils.CopyDBFromAssets("time.db")
        SQL1.Initialize(File.DirInternal,"time.db",True)
       
        Try
        SQL1.BeginTransaction
        Dim lst1 As List
        lst1 = su.LoadCSV(File.DirRootExternal & "/TcLogger","1.csv",",")
        For i = 0 To lst1.Size - 1
        Dim sColumn() As String
        sColumn = lst1.Get(i)
        SQL1.ExecNonQuery2("INSERT INTO main (a,b,c,d,e) VALUES (?, ?, ?, ?, ?)", sColumn)
       
        Next
        SQL1.TransactionSuccessful
        Catch
        Log(LastException.Message)
        End Try
        SQL1.EndTransaction

The line SQL1.ExecNonQuery2("INSERT INTO main (a,b,c,d,e) VALUES (?, ?, ?, ?, ?)", sColumn) is what fails with the table "main" cannot be found.

I looked on my S5 I'm using to test this with and the "time.db" file is located in a folder Device storage>Android>data>com.linear.time>files. This is where DBUtils.CopyDBFromAssets put it.

SQL1.Initialize(File.DirInternal,"time.db",True) does not fail as far as I can see.

So I'm stuck here and at a loss. There is my db file if someone wants to look at it.

Appreciate any help, thanks

 

[DonManfred]

DBUtils.CopyDBFromAssets("time.db")

give back the dir where the file is stored in.

So i guess it should be

dim pth as String = DBUtils.CopyDBFromAssets("time.db")
        SQL1.Initialize(path,"time.db",True)

 

[DonManfred]

or

        SQL1.Initialize(DBUtils.CopyDBFromAssets("time.db"),"time.db",True)

 

[DonManfred]

SQL1.Initialize(File.DirInternal,"time.db",True)

Maybe you are trying to open the db from the wrong location...

dbutils.bas

'Copies a database file that was added in the Files tab. The database must be copied to a writable location.
'This method copies the database to the storage card. If the storage card is not available the file is copied to the internal folder.
'The target folder is returned.
'If the database file already exists then no copying is done.
Sub CopyDBFromAssets (FileName As String) As String
    Dim TargetDir As String
    If File.ExternalWritable Then TargetDir = File.DirDefaultExternal Else TargetDir = File.DirInternal
    If File.Exists(TargetDir, FileName) = False Then
        File.Copy(File.DirAssets, FileName, TargetDir, FileName)
    End If
    Return TargetDir
End Sub

so the target path will be File.DirDefaultExternal or File.DirInternal

 

[klaus]

It should be:

If FirstTime Then   
    Dim pth as String = DBUtils.CopyDBFromAssets("time.db")
    SQL1.Initialize(path,"time.db",True)
End If

or

If FirstTime Then   
    SQL1.Initialize(DBUtils.CopyDBFromAssets("time.db"),"time.db",True)
End If

You should initialize SQL1 only once.

 

[Erel]

Move SQL1 to the Starter service and initialize it in Service_Create.

https://www.b4x.com/android/forum/threads/57599/#content

 

[Bryan]

Thanks much this worked perfectly.

I'll have to investigate moving SQL1 to a Starter service. I never really understood the purpose of using the Starter service. Seems my app has been working
fine without it so far. But I will read up on it again and try to get an understanding of why it is needed. Not for a topic here I guess.

Thanks again for the help

It should be:

If FirstTime Then  
    Dim pth as String = DBUtils.CopyDBFromAssets("time.db")
    SQL1.Initialize(path,"time.db",True)
End If

or

If FirstTime Then  
    SQL1.Initialize(DBUtils.CopyDBFromAssets("time.db"),"time.db",True)
End If

You should initialize SQL1 only once.