best way to open sqlite database

[sigster]

Hi

can you tell me that is the best way to open the database Please


I use this code to open my database
but when I upgrade the app and new database then I am still on the old database and if I change field I get error so I need to uninstall the app and install

that is the best way to open the database

   If File.Exists(File.DirDefaultExternal, "database.db") = False Then
        File.Copy(File.DirAssets, "database.db", File.DirDefaultExternal,"database.db")                     
      End If
      If FirstTime Then
           SQL.Initialize(File.DirDefaultExternal, "database.db", True)                                                                 
End If   
 End If

Regards
Sigster

 

[margret]

Your code appears to be wrong. You are using SQL.Initialize. SQL is reserved as a type. See the comments in the code below. Try the code listed below, it should work for you. You should also dim your object in Process_Globals:

Dim db As SQL

If File.Exists(File.DirDefaultExternal, "database.db") = False Then
   File.Copy(File.DirAssets, "database.db", File.DirDefaultExternal,"database.db")
End If
If FirstTime Then
   '***SQL.Initialize(File.DirDefaultExternal, "database.db", True)***
   'The above line is wrong.  SQL is reserved. You should use something like db in place of SQL
   'It should also be declared in Process Globals as below:
   'Dim db As SQL.  The new line should be:
   db.Initialize(File.DirDefaultExternal, "database.db", True)                                                                  
End If

 

[klaus]

You could change the database name when you need to change it and manage the copying of the new database in the code.
- check if file does exist, new file doesn't exist
- delete current database from the device
- copy new database with the standard name.

Best regards.

 

[timo]

You should allways have in your DB a table named 'DBversion' with a version value in order to let the update decide (by code) if to change the db (or, for example, update some tables) or not. People will not be very happy if they loose data by updating your app.

 

[Andras]

You should allways have in your DB a table named 'DBversion' with a version value in order to let the update decide (by code) if to change the db (or, for example, update some tables) or not. People will not be very happy if they loose data by updating your app.

I thought that when the app was updated the existing data was preserved? Certainly that's what my device does when updating an app. But I'm very willing to learn that I've got it wrong somewhere...!

John

 

[timo]

It depends on your code. If you write, 'If it doesn'exist, then copy from Assets' it means that if the DB exists, it will not be replaced. You can force it to be replaced, if you want. But remember to adapt the code to the new structure of the DB, if it has changed, and that by acting this way all existing data will be lost.

 

[Andras]

Yes - when the 'new' App is run for the first time I can see this - I was thinking of the process of loading it onto the device, which leaves the existing data untouched, unless the App is actually removed first before being replaced.

John

 

[sigster]

Thanks all for replay

will change how I open the database and check the DBversion of the database
and copy the database if is new DBversion

Regards
Sigster

 

[Penko]

As Timo stated, you should have logic about updating the database. E.g You may add a field which hasn't existed in the last version. You application would crash if the field is not in the database. The user will loose data if you simply copy the new database over the old one.

You have to be careful.

 

[sigster]

Hi

little experiment
I am testing this to copy the database when I upgrade the App
the database in only info the user don't write to the database

it will be nice to have this in libraries
but that is something I don't know how to make

'Activity module
Sub Process_Globals
   'These global variables will be declared once when the application starts.
   'These variables can be accessed from all modules.

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.
    Dim DirName As String        : DirName = File.DirRootExternal & "/databaseinfo"
    Dim FileNameislapp As String    : FileNameislapp = "databaseinfo.txt"   
End Sub

Sub Activity_Create(FirstTime As Boolean)

'make folder and Text file
If File.Exists(File.DirRootExternal,"/databaseinfo/databaseinfo.txt")=False Then
   File.MakeDir(File.DirRootExternal,"databaseinfo")
   Dim Writer As TextWriter
   Writer.Initialize(File.OpenOutput(File.DirRootExternal,"/databaseinfo/databaseinfo.txt",True))   
   SaveINI_ISLAPPL 'Safe new app version to Textfile
Else
   LoadINI_ISLAPP 'check the App and database Version and copy the database if I upgrade the app and the database
End If 

End Sub


Sub LoadINI_ISLAPP

      Dim pm As PackageManager 
        Dim version As Int
        Dim version_from_ini As Int      
       Dim rw As TextReader

    rw.Initialize(File.OpenInput(DirName, FileNameislapp))
    version_from_ini = rw.ReadLine
    rw.Close
   If version_from_ini < pm.GetVersionCode("sigster.upgrade_sample") Then
      File.Copy(File.DirAssets, "database.db", File.DirDefaultExternal, "database.db")
      SaveINI_ISLAPPL 'Safe app version to Textfile
   Else

   End If
      
End Sub

Sub SaveINI_ISLAPPL
      'Safe app version to Textfile
       Dim version As Int
        Dim pm As PackageManager
        version = pm.GetVersionCode("sigster.upgrade_sample")
      
       Dim tw As TextWriter    
       tw.Initialize(File.OpenOutput(DirName, FileNameislapp, False))
       tw.WriteLine(version)        
       tw.Close   

End Sub
Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Regards
Sigster

 

[Erel]

Please use File - Export as zip to zip your project. Otherwise you are uploading many unnecessary files.

it will be nice to have this in libraries

I don't see any advantage for adding such code to the SQL library. It will only make it less flexible.

Adding it to DBUtils code module is better.

 

[sigster]

Export as zip Done

 

[sigster]

DBUtils need to check it out one day


I change DirRootExternal to DirDefaultExternal
it remove the text file when I Uninstall the App

Regards
Sigster

 

[miguelconde]

Hello Guy's. What is the best practice to open a database?. Currently I declare the object database in each local sub where i need it, i open it, use it and finally close. My application has several activitys. Do you recommend declaring the object database on each global process of each activity?. Thank you.

 

[Erel]

This is an old thread. Please start a new one instead.