Display query result

[Gekornro]

Hi,

I'm still very new to Basic4Android and now I'm playing around with dSQL Databases

I've manged to create a very simple database with two tables. PlayerNames and and Games. I've succesfully created the tables and some queries to enter data.

What I would like now is to somehow execute a query against the database and display the result in a table.

From reading the tutorials I think I need to use a Cursor object? But I can't quite get my head around Cursor and how I can use this to display my query result in a list/table on screen.

Is this a simple code or is it more complicated that I think?

The query I want to run is simply SELECT * FROM PlayerNames

Sub Activity_Create(FirstTime As Boolean)

If FirstTime Then 
    SQLDb.Initialize(File.DirDefaultExternal, "vbstats.db", True)
    End If
    CreateTables
    Insertdata
   
   Result=SQLDb.ExecQuerySingleResult("SELECT count()* FROM PlayerNames")
   Msgbox("Number of players: ", Result)

End Sub

Sub CreateTables

SQLDb.ExecNonQuery("CREATE TABLE IF NOT EXISTS PlayerNames (id INTEGER PRIMARY KEY , First TEXT, Last TEXT, Serves INTEGER, Game INTEGER)")
SQLDb.ExecNonQuery("CREATE TABLE IF NOT EXISTS Games (id INTEGER PRIMARY KEY,  Hometeam TEXT, Awayteam TEXT)") 

End Sub

Sub Insertdata
    SQLDb.ExecNonQuery("INSERT INTO PlayerNames VALUES(NULL, 'Mario', 'Bros', 1, 1)")
   SQLDb.ExecNonQuery("INSERT INTO PlayerNames VALUES(NULL, 'Donkey', 'Kong', 1, 1)")
   SQLDb.ExecNonQuery("INSERT INTO Games VALUES(NULL, 'Team A', 'Team B')")
End Sub

 

[colboy]

So assuming you had a table called "Fred" with a field "Field1" and you wanted to iterate through the result set, you would do something like the following.

Dim i as int
dim lField1 as string
Dim Cursor1 As Cursor
Cursor1=SQL1.execquery("Select * from Fred")
If Cursor1.RowCount=0 Then   
   ' No Matches Found
Else If 
   lblRowCount.text=Cursor1.Rowcount 'My row count label
   For i=0 To Cursor1.RowCount-1
   Cursor1.Position=i
   lField1= Cursor1.GetString("Field1")
   Next
end if
Cursor1.close

This is off the top of my head, but should work.

Colin

 

[Erel]

I recommend you to try DBUtils: Basic4android Search: DBUtils

It will make such tasks much simpler.

 

[klaus]

You don't do anything with the variable lField1 the For/Next loop.
Heve you checked the content of lField1 in the Log Tab ?
In your code, after the For/Next loop, lField1 has the value of the last row.

Best regards.

 

[Gekornro]

I tried looking at the Dbutils tutorial and also examined the code within the module but it's too advanced for me. I couldn't make any sense of it.

I'm still a beginner in terms of basic4android so perhaps I'll stick to something else rather than SQL for the time being.

I have a fair understanding SQL but struggle to make sense of how it connects to b4a. There are many tutorials available on the forum but some aren't very well commented. And I am someone who needs things broken down into very detailed explanations.

I'll get there one day

 

[klaus]

Did you have a look at the Beginner's Guide chapter 13.2 ?

Best regards.

 

[Gekornro]

Did you have a look at the Beginner's Guide chapter 13.2 ?
Best regards.

Aaahh! I hadn't seen that tutorial before. I had a quick glance through the SQL example and it's simple and well documented! It's the kind of simplicity I can understand with code snippets I can use in my own apps!

I'll sit down and spend some time on this later.

Thank you so much.