Android Question How to update sqlite database file without getting error ?

[mr.gedo]

Hello all,

i have problem after update my databse and my app
i had (Table1) and work fine, i added another table (Table2) and update my app but when i try to view Table2 data giv me error (No such table)
i must unistall the app first and then reinstall it to works without problems

this code before add Table2

Sub Process_Globals

    Dim SQL As SQL

    Dim CUR As Cursor

    Dim DBFileDir As String : DBFileDir = File.DirRootExternal ': DBFileDir = File.DirDefaultExternal

    Dim DBFileName As String : DBFileName = "data.db"

End Sub


Sub Activity_Create(FirstTime As Boolean)

    Activity.LoadLayout("Main")

    If FirstTime = True Then

        DBFileDir = DBUtils.CopyDBFromAssets("data.db")
        SQL.Initialize(DBFileDir, DBFileName, True)

    End If



End Sub

and after update my databse and try to view data i get the error

Sub Button2_Click

    CUR = SQL.ExecQuery("SELECT * FROM Table2")

        For i = 0 To CUR.RowCount - 1

        CUR.Position = i

        CLV.Add(CreateListItem(CUR.GetString("Name"),CUR.GetString("Age"), CLV.AsView.Width, 260dip), $"Item #${i}"$)

    Next

End Sub

 

[Erel]

1. Use ResultSet instead of Cursor.
2. Make it a local variable.
3. Close it when done.
4. Use B4XPages.
5. Initialize SQL with False for the last parameter. You don't want to create a new database.
5. DBUtils.CopyDBFromAssets will not copy the file if it already exists. I guess that at some point you have created an empty database and the full database is not copied.

 

[mr.gedo]

1. Use ResultSet instead of Cursor.
2. Make it a local variable.
3. Close it when done.
4. Use B4XPages.
5. Initialize SQL with False for the last parameter. You don't want to create a new database.
5. DBUtils.CopyDBFromAssets will not copy the file if it already exists. I guess that at some point you have created an empty database and the full database is not copied.

thank you for your replay but i think you understod me wrong

when i update my app from old version to new, the old database not updated, so my app can't see the new changes or new tables inside database
becuse i have already added features in my app and modified the old database by adding a new table
If I (update) the old version to the new, I can call the old tables only and work fine like old version, but I can't call the new tables except in the case of unistall the old version and installing the new version

anyway i changed Cursor to ResultSet as you advice

    Dim Rs As ResultSet = SQL.ExecQuery("SELECT * FROM Table1")
    
    Do While Rs.NextRow
        CLV.Add(CreateListItem(Rs.GetString2(1),Rs.GetString2(3), CLV.AsView.Width, 260dip),Null)

    Loop
    Rs.Close

As for B4XPages i will try to move my project ASAP

But at the moment, is there any solution to replace the old database with a new one if i update the version?

 

[Erel]

Another point: don't use File.DirRootExternal.

Change the database file name and the previous one will be ignored.

 

[mangojack]

Another option that might suit you is to Delete the old DB prior to Checking for File Existence.

We were discussing this the other day in the Snippets Forum .

Tip: SQLite Column Does not exist even if you know it exists!!!

Ola When you run your app and developing it during debug and release mode. You will find out that you encounter this error. NB: If during development you make changes to your schema on your files inside "Files" tab. Now if your code has this kinda of statement. 'copy the default DB to...

www.b4x.com

 

[udg]

Hi @mr.gedo ,
did you have a look at this tutorial? That's the way I update DB structure from one version to another of an app.
As you will read, customer could even jump from version1 to version3 (or higher) while preserving full functionality.

 

[mr.gedo]

thank you all

The problem has been resolved

there is my code now

Sub Process_Globals
    Dim SQL As SQL
    Dim DBFileDir As String : DBFileDir = File.DirInternal ': DBFileDir = File.DirDefaultExternal
    Dim DBFileName As String : DBFileName = "data.db"
End Sub


Sub Activity_Create(FirstTime As Boolean)

    Activity.LoadLayout("Main")
    
    If File.Exists(File.DirInternal, "data.db") = False Then
        'copy the default DB
        File.Copy(File.DirAssets, "data.db", File.DirInternal, "data.db")
    End If
    
    SQL.Initialize(DBFileDir, DBFileName, False)
    
    End Sub

 

[Guenter Becker]

Hello all,

i have problem after update my databse and my app
i had (Table1) and work fine, i added another table (Table2) and update my app but when i try to view Table2 data giv me error (No such table)
i must unistall the app first and then reinstall it to works without problems

this code before add Table2

Sub Process_Globals

    Dim SQL As SQL

    Dim CUR As Cursor

    Dim DBFileDir As String : DBFileDir = File.DirRootExternal ': DBFileDir = File.DirDefaultExternal

    Dim DBFileName As String : DBFileName = "data.db"

End Sub


Sub Activity_Create(FirstTime As Boolean)

    Activity.LoadLayout("Main")

    If FirstTime = True Then

        DBFileDir = DBUtils.CopyDBFromAssets("data.db")
        SQL.Initialize(DBFileDir, DBFileName, True)

    End If



End Sub

and after update my databse and try to view data i get the error

Sub Button2_Click

    CUR = SQL.ExecQuery("SELECT * FROM Table2")

        For i = 0 To CUR.RowCount - 1

        CUR.Position = i

        CLV.Add(CreateListItem(CUR.GetString("Name"),CUR.GetString("Age"), CLV.AsView.Width, 260dip), $"Item #${i}"$)

    Next

End Sub

High,
I had this problem too. The reason is that the copied database is residing in the choosen directory and the "master databasae" is in the DirAsset (File)s Directory. If you are changing elements of the Master Database this does have no effect on the copied Database!.
In developping phase and testing sometimes database elements have to be changed. To take this changes also to the copied Database you have first to delete it (File.dellete....) and than to copy it again. By the way it does not matter wether you use sql or dbutils its a basic issue.

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Public SQLight As SQL
End Sub

Sub Service_Create
    If File.Exists(File.dirinternal,"testdb.db") Then
        ' delete or comment it out if not used'
        File.Delete("/storage/emulated/0/Android/data/b4a.example/files","fa.db")
    End If
    ' copy db to accessible folder
    If File.Exists(File.DirAssets,"testdb.db")= False Then
        File.Copy(File.DirAssets,"testdb.db",File.DirInternal,"testdb.db")
    End If
    ' open database
    SQLight.Initialize(File.DirInternal,"testdb.db",True)
End Sub

 

[mangojack]

@Guenter Becker ... If you wanted to delete the working DB and allow the updated 'Master database' to be copied again from your assets folder....
Should not the above code be .... (* I understand this is just some example code)

If File.Exists(File.dirinternal,"testdb.db") Then   '@ unneccessary ?  no Error will occur if you attempt to delete an non-existent file.
  ' delete or comment it out if not used'
  File.Delete(File.dirinternal,"testdb.db")
End If

this line adds a bit of confusion to your example ..

File.Delete("/storage/emulated/0/Android/data/b4a.example/files","fa.db")

Also ... after checking for file (db) existence , and copying db from assets (if it does not exist) ...

I think technically this line ...

SQLight.Initialize(File.DirInternal,"testdb.db",True)

should be

SQLight.Initialize(File.DirInternal,"testdb.db",False)   '@@@ False = Do not create a new empty DataBase.

might be wrong ...

 

[Guenter Becker]

Good man, there are really some errors:

Now corrected thank you....

Sub Service_Create
    'This is the program entry point.
    'This is a good place to load resources that are not specific to a single activity.
    File.Delete("/storage/emulated/0/Android/data/b4a.example/files","fa.db")

    ' copy db to accessible folder
    If File.Exists(File.DirAssets,"testdb.db")= True Then
        File.Copy(File.DirAssets,"testdb.db",File.DirInternal,"testdb.db")
    End If
    ' open database
    SQLight.Initialize(File.DirInternal,"testdb.db",False)
End Sub

 

[mangojack]

ehh, I think that should be ....

' copy db to accessible folder
If File.Exists(File.DirInternal,"testdb.db") = False Then
    File.Copy(File.DirAssets,"testdb.db",File.DirInternal,"testdb.db")
End If
' open database
SQLight.Initialize(File.DirInternal,"testdb.db",False)

'Your line above ...

If File.Exists(File.DirAssets,"testdb.db")= True Then

Why would you need to test for this ... you should know if the file is there or not ... you would have added it there (or not)
Plus you would be copying over your working DB over and over again ... wondering where is all the recent data !

ps: personally I like ..

If Not(File.Exists(File.DirInternal,"testdb.db"))Then
    File.Copy(File.DirAssets ...........)

pps: .. I have just re read you first post and now more confused ... so going to stop before this tread becomes a confusing mess.

Cheers

 

[Guenter Becker]

Okay,
The reason for testing is that I had the situation that the database was corrupt on the device, whiy don' no? You are correct normally it's not neccessary.
Deleting is not neccessary at release I use it only for work in process if the database structure/content is changed. And you are correct in this case earlier data input is lost.
I used it because I found that it is not possible to override an existing database on the device. Therefore I delete it.

Hope I am becomming clearer don't like to confuse you!