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:
B4X:
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:
B4X:
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.
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.
B4X:
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