B4J Question SQLite code issue (using DBUtils)

[Steve Piehl]

It has been a long time since I wrote SQL statements. I am trying to get a query to execute and return a list with 3 fields in the list. I have tried a lot of variations on the query and the list only returns 1 value. All of the field names are correct and each of them will return a query when I am requesting them individually...but I can't get the list to return more than one value for some reason.

Code that works:

    Dim ResultList As List
    ResultList.Initialize
    DBUtils.ExecuteList(SQL1,"SELECT EventDescription FROM LookupCIDEvents WHERE CIDEventCode= ?", Array As String(EventCode),0,ResultList)
Log ("size= " & ResultList.Size)

Above returns a size of 1

Code that doesn't work:

    Dim ResultList As List
    ResultList.Initialize
    DBUtils.ExecuteList(SQL1,"SELECT EventDescription || ' ' || OpenEventDesc || ' ' || RestoreEventDesc FROM LookupCIDEvents WHERE CIDEventCode= ?", Array As String(EventCode),0,ResultList)
Log ("size= " & ResultList.Size)

Above returns a size of 1 when I would expect it to return 3...because the data exists.

Any advice is appreciated.

 

[Erel]

1. Don't use ExecuteList if you want to return more than a single column. Use ExecuteMemoryTable.
2. A size of one means that a single record or row matched the query.

Dim res As List = DBUtils.ExecuteMemoryTable(SQL1,"SELECT EventDescription, OpenEventDesc, RestoreEventDesc FROM LookupCIDEvents WHERE CIDEventCode= ?", Array As String(EventCode),0)
For Each Row() As String in Res
 Log("EventDescription = " & Row(0))
 Log("OpenEventDesc= " & Row(1))
Next

 

[Steve Piehl]

Erel -- thank you for your help! Learning B4X is made simple with your guidance and assistance.