Record not saved into database when exiting app

[tamadon]

I am integrating a local highscore board into my simple game. The code is based on this solution

But I am using DBUtils ExecuteHTML to display the high score records. The records are saved and read properly while the app is running. But once I exit the app and restart it, the previous records were no longer in the database.

Some of my code:

Sub Process_Globals
   Dim sqlScore As SQL
   Dim DB As String       : DB = "hscore.jpg"
End Sub

Sub Globals
   Dim btnSave As Button
   Dim edtName As EditText
   Dim varName As String
   Dim varScore As Int
   Dim varMode As String
   Dim webScore As WebView
   Dim lblScore As Label
   Dim lblDifficulty As Label
   Dim pnlScore As Panel
End Sub

Sub Activity_Create(FirstTime As Boolean)
   
   Main.PhoneOrientation.SetScreenOrientation(1)
   If FirstTime Then
      DBUtils.CopyDBFromAssets(DB)
      sqlScore.Initialize(File.DirDefaultExternal, DB, True)
   End If
   
   Activity.LoadLayout("highscoretable")
   
   If finalScore.enterScore = True Then
      pnlScore.Visible = True
      pnlScore.Width = 100%x
      pnlScore.Top = 0
      Select gameLevel.Mode
         Case "1"
            varMode = "Easy"
         Case "2"
            varMode = "Medium"
         Case "3"
            varMode = "Hard"
      End Select
      lblDifficulty.Top = 0
      lblDifficulty.Width = 100%x
      lblDifficulty.Text = "Difficulty: " & varMode
      lblScore.Top = lblDifficulty.Top + lblDifficulty.Height + 2dip
      lblScore.Width = 100%x
      lblScore.Text = "Your score: " & game.totalPercent & "%"
      edtName.Top = lblScore.Top + lblScore.Height + 2dip
      edtName.Width = 100%x - 4dip - btnSave.Width
      btnSave.Top = edtName.Top
      btnSave.Left = edtName.Width + 2dip
      
   End If
   
   resizeWebView
   
End Sub

Sub resizeWebView
   If finalScore.enterScore = False Then
      webScore.Top = 0
      webScore.Left = 0
      webScore.Width = 100%x
      webScore.Height = 100%y
   Else
      webScore.Top = pnlScore.Height + 2dip
      webScore.Left = 0
      webScore.Width = 100%x
      webScore.Height = 100%y - webScore.Top
   End If
   
   loadScore
   
End Sub

Sub inputsControl() As Int
   If edtName.Text.Length = 0 Then
      edtName.Text = "---"
   End If
   
   finalScore.enterScore = False
   pnlScore.Visible = False
   resizeWebView
   Return 0

End Sub

Sub btnSave_Click
   Dim ctrl As Int

   ctrl = inputsControl
   
   If ctrl = 0 Then
      varName = edtName.Text
      varScore = game.totalPercent
      saveScore
   Else
      Return
   End If
      
End Sub

Sub saveScore
   sqlScore.ExecNonQuery("INSERT INTO scores (Name, Score, Difficulty) VALUES("& "'"&varName&"'" &","&varScore&", "& "'"&varMode&"'" &")")   
   
   loadScore
   
End Sub

Sub loadScore

   Dim scoreDat As String
   
   scoreDat = DBUtils.ExecuteHtml(sqlScore, "SELECT * FROM scores ORDER BY Score DESC", Null, 10, False)
   webScore.LoadHtml(scoreDat)
   
End Sub

What am I doing wrong? Thanks in advance for the help.

 

[klaus]

You should modify your code like this:

If FirstTime Then
  If File.Exists(File.DirDefaultExternal, DB) = False Then 
    DBUtils.CopyDBFromAssets(DB)
  End If
  sqlScore.Initialize(File.DirDefaultExternal, DB, True)
End If

In your code you copy the DB in DirAssets every time when starting the program.
In the code above the program checks if the DB exist in DirDefaultExternal and copies the DB only if it doesn't exist.

Best regards.

 

[tamadon]

Thanks for the reply Klaus.

For some reason it still doesn't work, same problem as before. I wonder if it has anything to do with the

DBUtils.CopyDBFromAssets(DB)

I've also tried changing the following to False but it still produce the same result.

  sqlScore.Initialize(File.DirDefaultExternal, DB, True)

 

[tamadon]

I noticed another thing, this problem only occurs when I run it on the emulator but the records are saved and loaded properly when run on a real device.

 

[klaus]

Did you set SD card support when you defined the Emulator ?
Page 87 Beginner's Guide.

From the code in DBUtils

'Copies a database file that was added in the Files tab. The database must be copied to a writable location.
'This method copies the database to the storage card. If the storage card is not available the file is copied to the internal folder.
'The target folder is returned.
'If the database file already exists then no copying is done.

Best regards.

 

[Erel]

There is a bug on some Android 2.2 versions where the DirDefaultExternal gets deleted when you reinstall the app. You can use a different folder.

The correct code is:

If FirstTime Then
     Dim folder As String
      folder = DBUtils.CopyDBFromAssets(DB) 'This method already checks for existence
     sqlScore.Initialize(folder, DB, True)
End If

 

[tamadon]

Did you set SD card support when you defined the Emulator ?
Page 87 Beginner's Guide.

From the code in DBUtils

'Copies a database file that was added in the Files tab. The database must be copied to a writable location.
'This method copies the database to the storage card. If the storage card is not available the file is copied to the internal folder.
'The target folder is returned.
'If the database file already exists then no copying is done.

Best regards.

Yes I have setup a 16MB SD card

 

[tamadon]

There is a bug on some Android 2.2 versions where the DirDefaultExternal gets deleted when you reinstall the app. You can use a different folder.

The correct code is:

If FirstTime Then
     Dim folder As String
      folder = DBUtils.CopyDBFromAssets(DB) 'This method already checks for existence
     sqlScore.Initialize(folder, DB, True)
End If

I see. Yes I was using Android 2.2 for the emulator. My device has Android 2.3.

Are there any advantages of using File.DirInternal over File.DirDefaultExternal or vise versa?

 

[tamadon]

Does that mean we cannot use this code when running on Android 2.2?

folder = DBUtils.CopyDBFromAssets(DB)
sqlScore.Initialize(folder, DB, True)

Since it will always write to the SD card (if writable) which will get deleted when you reinstall the app.

So how can I copy the db from asset to internal folder using DBUtils?

 

[Erel]

Note that it only happens on a specific minor version of Android 2.2.

If you want to workaround this issue then you should modify DBUtils.CopyDBFromAssets to always copy to a different folder.