Android Question How to pass variables to a sql database

cammel8 · Dec 27, 2016

OK so I am trying to set up a database and this is what i have so far

B4X:

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Dim sql1 As SQL

End Sub
Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    'Activity.LoadLayout("Layout1")
    If FirstTime Then
        sql1.Initialize(File.DirDefaultExternal, DateTime.GetMonth(DateTime.Now) &"."& DateTime.GetDayOfMonth(DateTime.Now) &"."& DateTime.GetYear(DateTime.Now) & " " & DateTime.TIME(DateTime.Now) & ".db", True)

    End If
createtables

End Sub
Sub populatedatabased
    sql1.BeginTransaction
    Try
        sql1.ExecNonQuery("INSERT INTO table1 VALUES ('"+playeronename+"', "+EBmakep1+", "+EBMissp1+", "+EBdefp1+", "+EBLotp1+", "+EBMobP1+", "+EBDeadp1+", "+EBe8P1+", "+EB8obp1+", "+EBbnrp1+", '"+winnerwinner+"', "+gamechosen+")")
        sql1.ExecNonQuery("INSERT INTO table1 VALUES ("+playertwoname+", "+EBmakep2+", "+EBMissp2+", "+EBdefp2+", "+EBLotp2+", "+EBMobP2+", "+EBDeadp2+", "+EBe8P2+", "+EB8obp2+", "+EBbnrp2+", "+winnerwinner+", "+gamechosen+")")
        sql1.ExecNonQuery("INSERT INTO table1 VALUES ('george', "+EBmakep1+", 4, 2, 0, 1, 6, 0, 0, 1, 'george', 8)")
        sql1.ExecNonQuery("INSERT INTO table1 VALUES ('TED', 22, 41, 21, 01, 11, 61, 01, 01, 11, 'george', 8)")
         sql1.TransactionSuccessful

    Catch
        Log(LastException.Message)
    End Try

    sql1.EndTransaction
    End Sub
Sub createtables
    sql1.ExecNonQuery("DROP TABLE IF EXISTS table1")
    sql1.ExecNonQuery("CREATE TABLE table1 (Col1 TEXT, col2 INTEGER, col3 INTEGER, col4 INTEGER, col5 INTEGER, col6 INTEGER, col7 INTEGER, col8 INTEGER, col9 INTEGER, col10 INTEGER, col11 TEXT, col12 INTEGER)")


End Sub

**********************
The four lines are all uncommented but the first two ae my first try and second two are my second try and i commented out the non working ones when trying other things they are just here to show how i was doing it. Also all of the variables are already in globals and working fine. I just didn't see the need to copy that code.
**********************
But when I try to call populatedatabase it skips over everything.
this line works:
sql1.ExecNonQuery("INSERT INTO table1 VALUES ('TED', 22, 41, 21, 01, 11, 61, 01, 01, 11, 'george', 8)")

but this doesnt:
sql1.ExecNonQuery("INSERT INTO table1 VALUES ('george', "+EBmakep1+", 4, 2, 0, 1, 6, 0, 0, 1, 'george', 8)")

So obviously it has something to do with the "+variable+" format

I have tried to find another way to pass it but i havent found a way.

In the tutorial it says use execnonquery2 to put ? in place of values but doesn't have anything on how to do it to write known values

All I'm trying to do is write the values i have stored in the variables to a database. Any help would be appreciated. And thank you in advance.

sorex · Dec 27, 2016

there are tools to create your sqlite database design in advance and test your queries.

it will also show the errors when you copy the query from the log to the query editor and run it.

Mahares · Dec 27, 2016

Here is a complete project that has all the code you need that is very close to what you are trying to do. Create a new project and paste all this code to it and make sure to check the SQL library. It is tested and it works:

B4X:

Sub Process_Globals
    Dim SQL1 As SQL
End Sub

Sub Globals
End Sub

Sub Activity_Create(FirstTime As Boolean)
  
    If FirstTime Then
        SQL1.Initialize(File.DirDefaultExternal, DateTime.GetMonth(DateTime.Now) &"."& DateTime.GetDayOfMonth(DateTime.Now) &"."& DateTime.GetYear(DateTime.Now) & " " & DateTime.TIME(DateTime.Now) & ".db", True)
    End If
  
    createtables

    populatedatabased
  
    displayRecords
End Sub

Sub populatedatabased
    Dim playertwoname As String="Ted"
    Dim  EBmakep2 As Int= 22
    Dim EBMissp2 As Int=41
    Dim EBdefp2 As Int = 21
    Dim EBLotp2 As Int =1
    Dim EBMobP2 As Int = 11
    Dim EBDeadp2 As Int =61
    Dim  EBe8P2 As Int =1
    Dim EB8obp2 As Int =1
    Dim EBbnrp2 As Int= 11  
    Dim winnerwinner As String ="george"
    Dim gamechosen    As Int =8
  
    SQL1.BeginTransaction
    Try
        SQL1.ExecNonQuery2("INSERT INTO table1  VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", _
        Array As Object( playertwoname, EBmakep2, EBMissp2, EBdefp2, EBLotp2, EBMobP2, EBDeadp2, EBe8P2, _
        EB8obp2, EBbnrp2, winnerwinner, gamechosen))
         SQL1.TransactionSuccessful

    Catch
        Log(LastException.Message)
    End Try

    SQL1.EndTransaction
    End Sub
  
Sub createtables
    SQL1.ExecNonQuery("DROP TABLE IF EXISTS table1")
    SQL1.ExecNonQuery("CREATE TABLE table1 (Col1 TEXT, col2 INTEGER, col3 INTEGER, col4 INTEGER, col5 INTEGER, col6 INTEGER, col7 INTEGER, col8 INTEGER, col9 INTEGER, col10 INTEGER, col11 TEXT, col12 INTEGER)")
End Sub

Sub displayRecords
    Dim cursor1 As Cursor
    cursor1=SQL1.ExecQuery("SELECT * FROM table1")
    For i=0 To cursor1.RowCount-1
        cursor1.Position=i
        Log(cursor1.GetString("col11"))
    Next
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)
    If UserClosed Then
        SQL1.Close      
    End If
End Sub

cammel8 · Dec 28, 2016

Mahares said:

Here is a complete project that has all the code you need that is very close to what you are trying to do. Create a new project and paste all this code to it and make sure to check the SQL library. It is tested and it works:

B4X:

Sub Process_Globals
    Dim SQL1 As SQL
End Sub

Sub Globals
End Sub

Sub Activity_Create(FirstTime As Boolean)

    If FirstTime Then
        SQL1.Initialize(File.DirDefaultExternal, DateTime.GetMonth(DateTime.Now) &"."& DateTime.GetDayOfMonth(DateTime.Now) &"."& DateTime.GetYear(DateTime.Now) & " " & DateTime.TIME(DateTime.Now) & ".db", True)
    End If

    createtables

    populatedatabased

    displayRecords
End Sub

Sub populatedatabased
    Dim playertwoname As String="Ted"
    Dim  EBmakep2 As Int= 22
    Dim EBMissp2 As Int=41
    Dim EBdefp2 As Int = 21
    Dim EBLotp2 As Int =1
    Dim EBMobP2 As Int = 11
    Dim EBDeadp2 As Int =61
    Dim  EBe8P2 As Int =1
    Dim EB8obp2 As Int =1
    Dim EBbnrp2 As Int= 11
    Dim winnerwinner As String ="george"
    Dim gamechosen    As Int =8

    SQL1.BeginTransaction
    Try
        SQL1.ExecNonQuery2("INSERT INTO table1  VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", _
        Array As Object( playertwoname, EBmakep2, EBMissp2, EBdefp2, EBLotp2, EBMobP2, EBDeadp2, EBe8P2, _
        EB8obp2, EBbnrp2, winnerwinner, gamechosen))
         SQL1.TransactionSuccessful

    Catch
        Log(LastException.Message)
    End Try

    SQL1.EndTransaction
    End Sub

Sub createtables
    SQL1.ExecNonQuery("DROP TABLE IF EXISTS table1")
    SQL1.ExecNonQuery("CREATE TABLE table1 (Col1 TEXT, col2 INTEGER, col3 INTEGER, col4 INTEGER, col5 INTEGER, col6 INTEGER, col7 INTEGER, col8 INTEGER, col9 INTEGER, col10 INTEGER, col11 TEXT, col12 INTEGER)")
End Sub

Sub displayRecords
    Dim cursor1 As Cursor
    cursor1=SQL1.ExecQuery("SELECT * FROM table1")
    For i=0 To cursor1.RowCount-1
        cursor1.Position=i
        Log(cursor1.GetString("col11"))
    Next
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)
    If UserClosed Then
        SQL1.Close  
    End If
End Sub

So after seeing this i went back to look at everything i had. Somehow in my testing the line from activity create, Createtables, Got deleted. So at some point during the time I have been testing various things people told me to, it hasn't been creating the tables. I put it back in and these two lines both worked to populate the database.

B4X:

            sql1.ExecNonQuery2("INSERT INTO table1  VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object( playertwoname, EBmakep2, EBMissp2, EBdefp2, EBLotp2, EBMobP2, EBDeadp2, EBe8P2, EB8obp2, EBbnrp2, winnerwinner, gamechosen))
sql1.ExecNonQuery("INSERT INTO table1 VALUES ('"&playeronename&"', "&EBmakep1&", "&EBMissp1&", "&EBdefp1&", "&EBLotp1&", "&EBMobP1&", "&EBDeadp1&", "&EBe8P1&", "&EB8obp1&", "&EBbnrp1&", '"&winnerwinner&"', "&gamechosen&")")

But at least now i know what was wrong.

So now My follow up question is which is a cleaner way to do it since both work or is it irrelevant? I just don't want the app crashing down the road because of the code i used being the wrong way.

cammel8 · Dec 28, 2016

By the way, thank you to everyone that helped me. I really appreciate it.

Erel · Dec 28, 2016

cammel8 said:
So now My follow up question is which is a cleaner way to do it since both work or is it irrelevant?

You should always prefer the parameterized query (ExecNonQuery2). It is simpler to write, doesn't require escaping the values and is not prone to SQL injections.

cammel8 · Dec 28, 2016

Erel said:
You should always prefer the parameterized query (ExecNonQuery2). It is simpler to write, doesn't require escaping the values and is not prone to SQL injections.

Thank you i will keep that in mind for next time.

sorex · Dec 28, 2016

It's a matter of taste.

I prefer the old style method as it is easier to track errors in your queries.

on the other hand...

first check/consider if sql is really needed in your project. in a lot of cases you're better of with just writing/reading files.

sorex · Dec 28, 2016

cammel8 said:
Somehow in my testing the line from activity create, Createtables, Got deleted.

that's why I asked "are you sure about your table layout & field names?" as the error mentioned that table1 didn't exist

Erel · Dec 28, 2016

sorex said:
in a lot of cases you're better of with just writing/reading files.

I don't agree with this statement. There are no disadvantages for using a "regular file" over SQLite database. It doesn't mean that you always need to use a database (which is also a file).

sorex · Dec 28, 2016

you don't agree but you explain that you agree with the statement?

my point is, why use an sql database to just store a username, score and game type.

you can just write a 1 line csv string or 3 lines text to a file instead (or a map) which is faster, requires less memory and doesn't need extra libs.

once you start using joins, math, grouping or multisorting etc sqlite becomes a real benefit ofcourse.

Erel · Dec 28, 2016

There are many cases where there could be many good options. I don't agree with this statement:
"in a lot of cases you're better of with just writing/reading files."

There is nothing better with using a text file over a SQLite database.

Android Question How to pass variables to a sql database

cammel8

Member

sorex

Expert

Mahares

Expert

cammel8

Member

cammel8

Member

Erel

B4X founder

cammel8

Member

sorex

Expert

sorex

Expert

Erel

B4X founder

sorex

Expert

Erel

B4X founder

Similar Threads

Android Question How to pass variables to a sql database

Member

Expert

Expert

Member

Member

B4X founder

Member

Expert

Expert

B4X founder

Expert

B4X founder

Similar Threads

Privacy & Transparency

Privacy & Transparency