Android Question SQL.Initialize creating new file when file already exists?

[skaliwag]

This code is used to restore a database that the user has copied from their PC, having previously saved it from the program.
FavSQL and DeckSQL are declared in Process_Globals in the Main activity.
This code is in another activity.

The code works fine on almost all devices, with Size1, Size2 and Size3 reporting the same correct value.
However, on a Samsung Galaxy S2 running Android version 2.3.5, this code does not behave as expected.
Size1 reports the expected, correct value.
Size2 and Size3 both report a value of zero.
SQL.Initialize seems to be creating a new file rather than using the existing one.

Main.FavSQL.Close
Main.DeckSQL.Close
                 
File.Copy(File.DirDefaultExternal & "/backups",sFile& ".vgbk",File.DirInternal,"vguser.db")
Log("Size1="&File.Size(File.DirInternal,"vguser.db"))   
               
Main.FavSQL.Initialize(File.DirInternal, "vguser.db", True)
Log("Size2="&File.Size(File.DirInternal,"vguser.db"))
Main.DeckSQL.Initialize(File.DirInternal, "vguser.db", True)
Log("Size3="&File.Size(File.DirInternal,"vguser.db"))

 

[Erel]

I don't see how it can happen. However try to change the last parameter to false to prevent it from creating a new database.

 

[skaliwag]

No. Erel. Neither can I.
But now it gets even stranger.
Setting the last parameter to false as suggested gave an exception.

So now I tried backing up the original database file and catching the exception when attempting to open the new database file.
If the exception fires I re-open the original database file and inform the user that the restore failed.
Surprisingly, this does work.

This would suggest that there is a problem with permissions on the new database file.
Is there a way of checking these?

Main.FavSQL.Close
Main.DeckSQL.Close
         
 'Backup the original in case of failure
File.Copy(File.DirInternal,"vguser.db",File.DirInternal,"vguser.dbk")
File.Copy(File.DirDefaultExternal & "/backups",sFile& ".vgbk",File.DirInternal,"vguser.db")
         
Dim Success As Boolean
Success=True

Try
  Main.FavSQL.Initialize(File.DirInternal, "vguser.db", False)
Catch
  Success=False
End Try

If (Success) Then
  Main.DeckSQL.Initialize(File.DirInternal, "vguser.db", False)
Else
  'Restore from the backup
  File.Copy(File.DirInternal,"vguser.dbk",File.DirInternal,"vguser.db"
  Main.FavSQL.Initialize(File.DirInternal, "vguser.db", False)
  Main.DeckSQL.Initialize(File.DirInternal, "vguser.db", False)
End If
        
'Delete the backup
File.Delete(File.DirInternal,"vguser.dbk")

If (Success) Then
  Msgbox("Backup File "&sFile&" Restored","Success")
Else
  Msgbox("Not supported on this version of Android","Failure")
End If

 

[sorex]

you connected 3 times to the same SQLite file, I guess it puts a lock on it to prevent data corruption due to multiple connections to the same file like most database do.

why don't you work with just 1 connector?

 

[skaliwag]

Thanks, sorex.
I tried that, but it didn't solve the problem.
I use multiple connections for clarity. As far as I'm aware this should not be a problem unless using different connections from different threads.
It would also not explain why it works fine on most devices.

I am now pretty confident that the problem is with the backup file itself.
It does not seem to be compatible with all versions which causes SQL.Initialize to fail.
This file will successfully restore to Android 2.3.3 and 4.0.3 but fails on 2.3.4 and 2.3.5.
However a backup written out on 2.3.5 will successfully restore on 2.3.5.

Erel, can you confirm that SQL.Initialize with CreateIfNecessary set to true will create a new file if the Initialize fails, regardless of whether the file exists?

 

[Erel]

That is correct. If the file is not considered a valid database file then it will be deleted and a new file will be created.

I think that you shouldn't use multiple SQL objects. Use one and make it a public variable.

 

[sorex]

It will create a 0 bytes file if the file doesn't exist, not sure how it behaves when it can't access the database due to a possible lock tho.

 

[keirS]

Thanks, sorex.
I tried that, but it didn't solve the problem.
I use multiple connections for clarity. As far as I'm aware this should not be a problem unless using different connections from different threads.
It would also not explain why it works fine on most devices.

I am now pretty confident that the problem is with the backup file itself.
It does not seem to be compatible with all versions which causes SQL.Initialize to fail.
This file will successfully restore to Android 2.3.3 and 4.0.3 but fails on 2.3.4 and 2.3.5.
However a backup written out on 2.3.5 will successfully restore on 2.3.5.

Erel, can you confirm that SQL.Initialize with CreateIfNecessary set to true will create a new file if the Initialize fails, regardless of whether the file exists?

Doesn't matter what the Android version is. It matters what version of SQLite you are using. A Samsung S2 is a custom Android install and uses SQLite version 3.7.5 A standard Honeycomb and ICS install uses 3.7.4. AFAIK you cannot open a 3.7.5 DB with SQLite 3.7.4