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

mr.gedo

Member
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

B4X:
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


B4X:
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

B4X founder
Staff member
Licensed User
Longtime User
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.
 
Upvote 0

mr.gedo

Member
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

B4X:
    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?
 
Upvote 0

mangojack

Well-Known Member
Licensed User
Longtime User
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 .

 
Upvote 0

udg

Expert
Licensed User
Longtime User
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.
 
Upvote 0

mr.gedo

Member
thank you all

The problem has been resolved

there is my code now

B4X:
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
 
Upvote 0

Guenter Becker

Active Member
Licensed User
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

B4X:
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


B4X:
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.

Example:
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
 
Upvote 0

mangojack

Well-Known Member
Licensed User
Longtime User
@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)
B4X:
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 ..
B4X:
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 ...
B4X:
SQLight.Initialize(File.DirInternal,"testdb.db",True)

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

might be wrong ...
 
Last edited:
Upvote 0

Guenter Becker

Active Member
Licensed User
Good man, there are really some errors:

Now corrected thank you....

B4X:
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
 
Upvote 0

mangojack

Well-Known Member
Licensed User
Longtime User
ehh, I think that should be ....
B4X:
' 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 ...
B4X:
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 ..
B4X:
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 :)
 
Last edited:
Upvote 0

Guenter Becker

Active Member
Licensed User
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!
 
Upvote 0
Top