Android Question SQL Databasing

A

Andrew Montgomery-Fox

Member
Licensed User
Longtime User
Hi Guys,

I have a couple of problems I need help with, The first is the FirstTime run, I setup an if loop in the Activity_Create on the Main Activity to setup the databases used for scoring and such like, and it has an IF loop to detect if the files exist, but it doesn't work, I also tried the "If FirstTime Then" but that doesn't work either, it runs everytime the app is launched, code attached below :

Code: Copy 
Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    Activity.LoadLayout("game_menu")
     
    If File.Exists(File.DirDefaultExternal, "score.db") = False Then
        SQL1.Initialize(File.DirDefaultExternal, "scores.db", True)


        SQL1.ExecNonQuery("DROP TABLE IF EXISTS highscore")
        SQL1.ExecNonQuery("DROP TABLE IF EXISTS gamesplayed")
        SQL1.ExecNonQuery("DROP TABLE IF EXISTS playername")
        SQL1.ExecNonQuery("CREATE TABLE highscore (name TEXT, score INTEGER)")
        SQL1.ExecNonQuery("CREATE TABLE gamesplayed (number INTEGER)")
        SQL1.ExecNonQuery("CREATE TABLE playername (name TEXT)")

 
        SQL1.ExecNonQuery2("INSERT INTO highscore VALUES(?, ?)", Array As Object ("Default", 16))
        SQL1.ExecNonQuery2("INSERT INTO highscore VALUES(?, ?)", Array As Object ("Andrew", 3))
        SQL1.ExecNonQuery2("INSERT INTO highscore VALUES(?, ?)", Array As Object ("Default", 0))
        SQL1.ExecNonQuery2("INSERT INTO highscore VALUES(?, ?)", Array As Object ("Default", 2))
        SQL1.ExecNonQuery2("INSERT INTO highscore VALUES(?, ?)", Array As Object ("Andrew", 10))

     
        Log("Number of rows = " & SQL1.ExecQuerySingleResult("SELECT count(*) FROM highscore"))
        Log("Number of rows = " & SQL1.ExecQuerySingleResult("SELECT count(*) FROM gamesplayed"))     

        InputName.Input = ""
        InputName.Hint = "Enter Your Name"
        InputName.HintColor = Colors.Cyan
        playerName = DialogResponse.CANCEL
        playerName = InputName.Show("Enter Your Name", "Enter Your Name", "Okay", "Cancel", "Mehbe", Null)
        ToastMessageShow("Name Set! Click on your name to change it!", False)
        SQL1.ExecNonQuery2("INSERT INTO playername VALUES(?)", Array As Object(playerName))
        ToastMessageShow("Creating files on first run", True)
     
    Else If  File.Exists(File.DirDefaultExternal, "score.db") = True Then
        ToastMessageShow("Welcome back!", False)
    End If
End Sub

Also I how can I set a maximum row count on the SQL database, and have any new high scores replace the lower scores? I only need 10 rows to keep the top 10 high scores


Thanks,
Andrew Montgomery-Fox

Edit: ZIP of all the code: http://amfox.eu:8080/GameSourceCode.zip got an error saying it was too big for the server so had to upload it to my server
 
Last edited:
Sort by date Sort by votes
Andrew

In the false part, I can't see where you copy the DB file from DirAssets to DirExternal... So it will never exist where you're checking.

I think that's the issue.
 
Upvote 0
eps said:
Andrew

In the false part, I can't see where you copy the DB file from DirAssets to DirExternal... So it will never exist where you're checking.

I think that's the issue.
Click to expand...

Doesn't
Code: Copy 
SQL1.Initialize(File.DirDefaultExternal, "scores.db", True)

Create scores.db on the external storage? I had a look and it is in the Android/Data/com.appname folder on my SD Card
 
Upvote 0
You won't find the file you are looking for because it doesn't exist !

You look in If File.Exists(File.DirDefaultExternal, "score.db") = False Then if the database "score.db" does exist.
If not, you initialize a database with a different name "scores.db" in SQL1.Initialize(File.DirDefaultExternal, "scores.db", True)

You should replace this
Code: Copy 
Else If File.Exists(File.DirDefaultExternal, "score.db") = True Then
    ToastMessageShow("Welcome back!", False)

by this
Code: Copy 
Else
    SQL1.Initialize(File.DirDefaultExternal, "scores.db", True)
    ThenToastMessageShow("Welcome back!", False
You must initialize the database also when the file exists.

In every Activity you set in Process_Globals Dim SQL1 As SQL
You should declare it only once and the access it with module name in all the other modules.
If you declare Dim SQL1 As SQL in the Create module you can access the database with Create.SQL1 from any other module.

Also I how can I set a maximum row count on the SQL database, and have any new high scores replace the lower scores? I only need 10 rows to keep the top 10 high scores
Click to expand...
When you want to add a new scroe you should
- check the row count
- if it's less then 10 insert the new score
- if it's equal to 10
- check if the new score is bigger than the min score
- if yes remove the min score and insert the new one
- if not do nothing
 
Last edited:
Upvote 0
I check for my db in a module, and i can't try that project because of my version of B4A, but i don't understand why this code should not work; better, why it don't works:
Code: Copy 
    If Not (File.Exists(File.DirDefaultExternal, "score.db")) Then
        SQL1.Initialize(File.DirDefaultExternal, "scores.db", True)


        SQL1.ExecNonQuery("DROP TABLE IF EXISTS highscore")
'...

I can see the file (score.db) but File.Exists(...) returns False.


To get first 3 rows, you should use something like:
Code: Copy 
SELECT * FROM highscore WHERE name = 'Andrew' ORDER BY score DESC LIMIT 2
' or
SELECT * FROM highscore ORDER BY score DESC LIMIT 2

Also, this is wrong:
Code: Copy 
    Cursor1 = SQL1.ExecQuery("SELECT name FROM playername")

Cursor1.Position = 0

lblPlayerName.Text = (Cursor1.GetInt("name"))

name is TEXT, not INTEGER, then GetString
 
Last edited:
Upvote 0
LucaMs said:
I check for my db in a module, and i can't try that project because of my version of B4A, but i don't understand why this code should not work; better, why it don't works:
Code: Copy 
    If Not (File.Exists(File.DirDefaultExternal, "score.db")) Then
        SQL1.Initialize(File.DirDefaultExternal, "scores.db", True)


        SQL1.ExecNonQuery("DROP TABLE IF EXISTS highscore")
'...

I can see the file (score.db) but File.Exists(...) returns False.


To get first 3 rows, you should use something like:
Code: Copy 
SELECT * FROM highscore WHERE name = 'Andrew' ORDER BY score DESC LIMIT 2
' or
SELECT * FROM highscore ORDER BY score DESC LIMIT 2

Also, this is wrong:
Code: Copy 
    Cursor1 = SQL1.ExecQuery("SELECT name FROM playername")

Cursor1.Position = 0

lblPlayerName.Text = (Cursor1.GetInt("name"))

name is TEXT, not INTEGER, then GetString
Click to expand...

With that last bit being a GetString you have just solved one of those problems I just couldn't understand! Thank you so very much!
 
Upvote 0
klaus said:
You won't find the file you are looking for because it doesn't exist !

You look in If File.Exists(File.DirDefaultExternal, "score.db") = False Then if the database "score.db" does exist.
If not, you initialize a database with a different name "scores.db" in SQL1.Initialize(File.DirDefaultExternal, "scores.db", True)

You should replace this
Code: Copy 
Else If File.Exists(File.DirDefaultExternal, "score.db") = True Then
    ToastMessageShow("Welcome back!", False)

by this
Code: Copy 
Else
    SQL1.Initialize(File.DirDefaultExternal, "scores.db", True)
    ThenToastMessageShow("Welcome back!", False
You must initialize the database also when the file exists.

In every Activity you set in Process_Globals Dim SQL1 As SQL
You should declare it only once and the access it with module name in all the other modules.
If you declare Dim SQL1 As SQL in the Create module you can access the database with Create.SQL1 from any other module.

When you want to add a new scroe you should
- check the row count
- if it's less then 10 insert the new score
- if it's equal to 10
- check if the new score is bigger than the min score
- if yes remove the min score and insert the new one
- if not do nothing
Click to expand...


Also I've read your post, Klaus, but unfortunately I did not understand it.

Your first line:
"You will not find the file you are looking for Because it does not exist!"
It exist! I can see it in the right folder! Despite this, running step by step the code I have written in #5 (or adding a log for FileExists), the file seems to not exist.

Ok, I'll try to read your post better.
 
Upvote 0
Andrew was using two different file names !!!
score.db here: If File.Exists(File.DirDefaultExternal, "score.db") = False Then
and scores.db here: SQL1.Initialize(File.DirDefaultExternal, "scores.db", True)
If you generate a file with the name "scores.db" and then you look for a file with the name "score.db" you won't find it.
 
Upvote 0
klaus said:
Andrew was using two different file names !!!
score.db here: If File.Exists(File.DirDefaultExternal, "score.db") = False Then
and scores.db here: SQL1.Initialize(File.DirDefaultExternal, "scores.db", True)
If you generate a file with the name "scores.db" and then you look for a file with the name "score.db" you won't find it.
Click to expand...

Oh my gosh :( I feel so silly now!
 
Upvote 0
We've all done it! It's _the_ most frustrating thing ever in programming, but does happen - especially when developing on your own, i.e. no code review.
 
Upvote 0
the old ' . ' instead of ' , ' trick o_O
 
Upvote 0
You must log in or register to reply here.

Similar Threads

Erel
  • Article Article
B4A Library [B4X][LOA] DBQuery - SQL made easy
Replies
0
Views
2K
Erel
Erel
Erel
  • Article Article
Android Code Snippet [B4X] SQL - setting the journal mode
Replies
0
Views
4K
Erel
Erel
Erel
  • Article Article
Android Tutorial [B4X] B4XTable - Load data from SQL database
Replies
4
Views
28K
Erel
Erel
Erel
  • Locked
  • Article Article
Android Tutorial [B4X] SQL with Wait For
Replies
16
Views
43K
Erel
Erel
Erel
  • Locked
  • Article Article
Android Tutorial Android database encryption with SQLCipher library
7 8 9
Replies
163
Views
189K
Erel
Erel
Back
Top