Android Question SQL Databasing

Andrew Montgomery-Fox · Apr 9, 2014

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 :

B4X:

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

eps · Apr 9, 2014

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.

Andrew Montgomery-Fox · Apr 9, 2014

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.

Doesn't

B4X:

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

klaus · Apr 9, 2014

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

B4X:

Else If File.Exists(File.DirDefaultExternal, "score.db") = True Then
    ToastMessageShow("Welcome back!", False)

by this

B4X:

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

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

LucaMs · Apr 9, 2014

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:

B4X:

    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:

B4X:

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:

B4X:

    Cursor1 = SQL1.ExecQuery("SELECT name FROM playername")

Cursor1.Position = 0

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

name is TEXT, not INTEGER, then GetString

Andrew Montgomery-Fox · Apr 9, 2014

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:

B4X:

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:

B4X:

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:

B4X:

Cursor1 = SQL1.ExecQuery("SELECT name FROM playername") Cursor1.Position = 0 lblPlayerName.Text = (Cursor1.GetInt("name"))

name is TEXT, not INTEGER, then GetString

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!

Andrew Montgomery-Fox · Apr 9, 2014

Also I'll go through some of the other code snippets and see if one works, Thanks to all of you for your help!

klaus · Apr 9, 2014

Did you look at my answer in post #4 ?

Andrew Montgomery-Fox · Apr 9, 2014

klaus said:
Did you look at my answer in post #4 ?

Yes I did, that's the first thing I'm going to try, Didn't realise that I have to init the database even if its already been created!

And your explanation of using global variables was helpful, I need to change that too

LucaMs · Apr 9, 2014

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

B4X:

Else If File.Exists(File.DirDefaultExternal, "score.db") = True Then ToastMessageShow("Welcome back!", False)

by this

B4X:

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

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.

klaus · Apr 9, 2014

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.

LucaMs · Apr 10, 2014

Damn, I'll have to go to a good ophthalmologist

Andrew Montgomery-Fox · Apr 10, 2014

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.

Oh my gosh

I feel so silly now!

eps · Apr 10, 2014

Classic, at least it should be sorted out now

Andrew Montgomery-Fox · Apr 10, 2014

eps said:
Classic, at least it should be sorted out now

Indeed, Thanks for all your help guys

eps · Apr 10, 2014

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.

mangojack · Apr 10, 2014

the old ' . ' instead of ' , ' trick

Andrew Montgomery-Fox · Apr 10, 2014

mangojack said:
the old ' . ' instead of ' , ' trick

Thats one I know too well now! Your profile photo pretty much sums it up as well!

Android Question SQL Databasing

Andrew Montgomery-Fox

Member

eps

Expert

Andrew Montgomery-Fox

Member

klaus

Expert

LucaMs

Expert

Andrew Montgomery-Fox

Member

Andrew Montgomery-Fox

Member

klaus

Expert

Andrew Montgomery-Fox

Member

LucaMs

Expert

klaus

Expert

LucaMs

Expert

Andrew Montgomery-Fox

Member

eps

Expert

Andrew Montgomery-Fox

Member

eps

Expert

mangojack

Expert

Andrew Montgomery-Fox

Member