Creating a table in a database automatically if the table doesn't already exist

isk2837 · Apr 10, 2012

I'm working on a game app, and am setting up a high score table as part of that. I have this line of code -

B4X:

 Sql1.Initialize(File.DirRootExternal,"Breakout.db",True)

which creates a database file if one doesn't already exist. This file contains a table called HighScores, which will hold high score data. However, at the moment, the code will only create a database called Breakout automatically - it won't automatically create and populate a table called HighScores with some default data if the table doesn't already exist. So how can I make it check for a table called HighScores, and create the table if it doesn't already exist?

NJDude · Apr 10, 2012

Somethng like this:

B4X:

Sql1.Initialize(File.DirRootExternal,"Breakout.db",True)
Sql1.ExecNonQuery("CREATE TABLE table_name(Name TEXT, Value NUMERIC)")

peacemaker · Apr 10, 2012

For example:

B4X:

Sub MaxViaSQL () As Int
Dim SQL1 As SQL, Fname As String
Fname = Rnd(1000000, 99999999) + Rnd(674, 9999)
SQL1.Initialize(File.DirInternalCache, Fname, True)
SQL1.ExecNonQuery("DROP TABLE IF EXISTS tab1")
SQL1.ExecNonQuery("CREATE TABLE tab1 (sum NUMBER, id NUMBER)")
Dim a(2) As String
Dim s As String, b, d As Int
For i = 0 To lstSort.Size - 1
   s = lstSort.Get(i)
   'Log(s)
   b = s.LastIndexOf("-")
   a(1) = s.SubString(b + 1)
   a(0) = s.SubString2(0,b)
   SQL1.ExecNonQuery2("INSERT INTO tab1 VALUES (?,?)", Array As Object (a(0), a(1)))
   If (i Mod 50) = 0 Then DoEvents
Next
Dim L As Int
Try
   L = SQL1.ExecQuerySingleResult("SELECT id FROM tab1 ORDER BY sum DESC")
Catch
   L = -1
End Try
lstSort.Clear
File.Delete(File.DirInternalCache, Fname)
Return L
End Sub

isk2837 · Apr 10, 2012

NJDude said:

Somethng like this:

B4X:

Sql1.Initialize(File.DirRootExternal,"Breakout.db",True)
Sql1.ExecNonQuery("CREATE TABLE table_name(Name TEXT, Value NUMERIC)")

Won't that cause an error message if the table already exists? I want the code to create a table, then insert some data into it, to only run if the table doesn't already exist - creating a database with the table HighScores only if both don't already exist.

peacemaker said:

For example:

B4X:

Sub MaxViaSQL () As Int
Dim SQL1 As SQL, Fname As String
Fname = Rnd(1000000, 99999999) + Rnd(674, 9999)
SQL1.Initialize(File.DirInternalCache, Fname, True)
SQL1.ExecNonQuery("DROP TABLE IF EXISTS tab1")
SQL1.ExecNonQuery("CREATE TABLE tab1 (sum NUMBER, id NUMBER)")
Dim a(2) As String
Dim s As String, b, d As Int
For i = 0 To lstSort.Size - 1
   s = lstSort.Get(i)
   'Log(s)
   b = s.LastIndexOf("-")
   a(1) = s.SubString(b + 1)
   a(0) = s.SubString2(0,b)
   SQL1.ExecNonQuery2("INSERT INTO tab1 VALUES (?,?)", Array As Object (a(0), a(1)))
   If (i Mod 50) = 0 Then DoEvents
Next
Dim L As Int
Try
   L = SQL1.ExecQuerySingleResult("SELECT id FROM tab1 ORDER BY sum DESC")
Catch
   L = -1
End Try
lstSort.Clear
File.Delete(File.DirInternalCache, Fname)
Return L
End Sub

Maybe I'm reading this wrong, but it looks like every time the program code is run it'll delete the existing table (and all the data in it), then create a new one. That's not good if I want to keep the data in the table for next time - this is for creating a high scores table, and it wouldn't be good if the high scores are effectively deleted every time the code is run.

What I want is code that will check for whether a table called HighScores exists or not, allowing me to create code that will create a table for receiving high scores in the SD card, then access that table from then on. So the table will be made the first time the player runs the game on their phone, and from then on that's it - whenever they run the game in the future the table is there and can be manipulated.

NJDude · Apr 10, 2012

You have to understand the the code posted is a "sample" so, if it gives you an idea about what to do, then you can research and add the code to check for the existence of the database, etc etc.

We are helping you by giving you clues or answering your questions, not writing a whole app for you.

isk2837 · Apr 10, 2012

NJDude said:
You have to understand the the code posted is a "sample" so, if it gives you an idea about what to do, then you can research and add the code to check for the existence of the database, etc etc.

We are helping you by giving you clues or answering your questions, not writing a whole app for you.

I'm not asking anyone to write the whole app for me, I'm just asking for help me with one small part of a much larger app. As someone who is still fairly new to some aspects of writing code in Basic4android, I appreciate any help anyone gives me with this one small part of my app. I can do most of it myself, I just need someone to tell me how to say:

B4X:

If table HighScores does not exist then
Run this Code
End if

in Basic4Android language. Or to point me to somewhere where I can learn how to say that.

NJDude · Apr 10, 2012

B4X:

If File.Exists(File.DirRootExternal, "Breakout.db") = False Then
            
   Sql1.Initialize(File.DirRootExternal,"Breakout.db",True)
   Sql1.ExecNonQuery("CREATE TABLE table_name(Name TEXT, Value NUMERIC)") 

   Else

    Sql1.Initialize(File.DirRootExternal,"Breakout.db",True)

End If

Also, for more info you can check THIS page.

isk2837 · Apr 10, 2012

NJDude said:

B4X:

If File.Exists(File.DirRootExternal, "Breakout.db") = False Then
            
   Sql1.Initialize(File.DirRootExternal,"Breakout.db",True)
   Sql1.ExecNonQuery("CREATE TABLE table_name(Name TEXT, Value NUMERIC)") 

   Else

    Sql1.Initialize(File.DirRootExternal,"Breakout.db",True)

End If

Also, for more info you can check THIS page.

Thanks for the help.

Creating a table in a database automatically if the table doesn't already exist

isk2837

Member

NJDude

Expert

peacemaker

Expert

isk2837

Member

NJDude

Expert

isk2837

Member

NJDude

Expert

isk2837

Member

Creating a table in a database automatically if the table doesn't already exist

Member

Expert

Expert

Member

Expert

Member

Expert

Member

Privacy & Transparency

Privacy & Transparency