SQLite database update

[padvou]

Dear all,
My project has an sqlite db initialized on create. If the user wishes so, he can press a button and download an updated version of the db, meaning he downloads a new db file. How is it possible to delete the old db file and have the application hook up to the new db file? I managed to close the SQL and replace the old db file with the new one. How should i proceed next?

 

[klaus]

After closing the SQL object and changing the file reinitialize the SQL object.

Best regards.

 

[padvou]

After closing the SQL object and changing the file reinitialize the SQL object.

Best regards.

You mean something like this:

SQL1.Close
File.Delete(File.DirInternal,"client.db")
File.Copy(File.DirRootExternal,"client.db",File.DirInternal,"client.db")

SQL1.Initialize(File.DirInternal, "client.db", False)

It fails to work as expected...

 

[klaus]

What doesn't work ?

 

[padvou]

What doesn't work ?

It doesn't in fact re-initialize the db, I can't figure out why..
It get initialized by itself, when the app is closed, by means of "Activity create"

 

[klaus]

I tried this code in one of my applications and it does work.

Sub btnLoad_Click
    SQL1.Close
    File.Delete(DBFileDir, DBFileName)
    File.Copy(File.DirAssets, DBFileName, DBFileDir, DBFileName)
    SQL1.Initialize(DBFileDir, DBFileName, True)
    SQLTableRead
End Sub

Do you update the display after replacing the file ?

Best regards.

 

[padvou]

I tried this code in one of my applications and it does work.

Sub btnLoad_Click
    SQL1.Close
    File.Delete(DBFileDir, DBFileName)
    File.Copy(File.DirAssets, DBFileName, DBFileDir, DBFileName)
    SQL1.Initialize(DBFileDir, DBFileName, True)
    SQLTableRead
End Sub

Do you update the display after replacing the file ?

Best regards.

Meaning?
I have two cursors that read two different tables.

 

[klaus]

Meaning?

- When I close the SQL object (the cursors are already closed)
- Load a new file
- Initialize the SQL object again
- Update the display of the data
- I get the content of the new file !

I have two cursors that read two different tables.

Do you close the cursors ?
How many files do you have ?
How many SQL objects do you have ?
Where and how do close the SQL object(s) ?
Are the two tables in the same file ?
Without knowing more details it's difficult to give you a concrete answer.

Best regards.

 

[mc73]

Klaus, this is unfortunately a problem I'm dealing with too. What I've observed is that in a part of my code, I begin a transaction, I end it properly, but somehow sqlite holds a zero-length db-journal, thus closing the sql object unfortunately doesn't help me getting the data of the new db straight away. It's mysterious, but I don't believe in mysteries so I'll get back if I find anything that may be worth sharing with the community. I mean if it is not my own mistake while coding, which makes perfect sense to me after all

 

[padvou]

The two cursors are in the same db file reading two different tables.
It is true that after a while data are refreshed correctly without me doing anything.. how should the cursors be closed?

 

[Alex2012]

I had a similar problem but solved it
Actiwity (main) there is loading databases (I just load but you can exch.)

If File.Exists(File.DirInternal,"ktm.db") = False Then
File.Copy(File.DirAssets,"kmt.db",File.DirInternal,"db1.db")
End If

If DSQL .IsInitialized = False Then
DSQL.Initialize(File.DirInternal & "/", "db1.db", False)
End If

and in the next Activity (Service) I loads and open databases ------(not main)

DSQL.Initialize (File.DirInternal & "/", "db1.db", True)

works without problems
To renew I use timer(or whatever) to initiation cursor
the result must be set to something

 

[padvou]

Please try the relevant code and report back, so we can mark this as solved:

Sub FTP_DownloadCompleted (ServerPath As String, Success As Boolean)                  
Log(ServerPath & ", Success=" & Success)
If Success = False Then
Msgbox("Error updating: " & LastException.Message,"")
Log(LastException.Message)
Else
cursor1.Close   'Add this to close the cursor first. If there are more cursors, close them all before closing the SQL object.


SQL1.Close   'Add this to close the SQL object.
File.Delete(File.DirInternal,"mydb.db")
File.Copy(File.DirRootExternal,"mydb.db",File.DirInternal,"mydb.db")

SQL1.Initialize(File.DirInternal, "mydb.db", False)
Msgbox("Update completed.","MyApp Update")
End If

End Sub

 

[klaus]

Your code looks strange to me :

File.Copy(File.DirRootExternal,"mydb.db",File.DirInternal,"mydb.db")

SQL1.Initialize(File.DirInternal, "client.db", False)

You copy to mydb.db and you initialize to client.db ???

Best regards.

 

[padvou]

Your code looks strange to me :

File.Copy(File.DirRootExternal,"mydb.db",File.DirInternal,"mydb.db")

SQL1.Initialize(File.DirInternal, "client.db", False)

You copy to mydb.db and you initialize to client.db ???

Best regards.

Sorry... wrong copy-paste.... Edited it to correct.