Android Question need a suggestion in dbutils approach

[marcick]

Hi all,
It's the first time I look to dbutils and I'm trying to use it for storing incoming SMS.
The records have 4 fields, Id, Sender, Date and Message.
No problems til now.

Now I want to display all messages in a scrollview, grouped by chat (sender).
Which is the more easy and efficient way to do it, supposing the db can grow ang grow and contain hundreds, thousands of messages ?

I think I have to query the database and obtain a list of "senders". Is it easy or do I have to scan all the records ?
Then, for each sender, I should query the database and obtain a list of messages, then I should sort them by date, then I can display them.
Maybe it would be better not to use a single database, but one for each sender ?

I just ask you experts if the approach is correct.
Any hints will be appreciated, thanks
Marco

 

[Erel]

You should definitely use a single database.

You can retrieve all messages and sort the query based on the senders column. This way you will get a list that is organized based on the sender.

 

[Reviewnow]

I would use the customlistview to display your data it is much more flexable

example of displaying your query in a customlistview

dim msgquery as string
msgquery = "Select * from mytable Where sender = '" & SenderFilter & "' and Date = '" & dateFilter & "' order by Date desc"

Dim Table As List
 
Table = dbutils.ExecuteMemoryTable(SQL, msgquery, NULL, 0)
 
    Dim Cols() As String
    Dim id As Int
    Dim Sender As String
    Dim smsDate As String
    Dim Message As String
    For i = 0 To Table.Size - 1

        Cols = Table.Get(i)

        id = Cols(0)
        Sender = Cols(1)
        smsDate = Cols(2)
        Message = Cols(3)
        'add your record to the custom list view
        'this is just
          clv1.Add(CreateListItem(ID & ":" & smsDate & ":" & Sender & ":" & Message , clv1.AsView.Width, 100dip), 100dip, "Index # " & i & " Message #" & id)

      Next

 

[marcick]

Thanks Erel and Reviewnow for help.
Marco

 

[marcick]

I'm trying to use this code to sort the table according to the field of column 1 that contains "sender", but the sintax is not correct.
Help ..

Dim msgquery As String
    msgquery = "Select * from Sms"
    Dim Table As List
    Table = DBUtils.ExecuteMemoryTable(SQL, msgquery, Null, 0)
    Table.SortType(1, True)

 

[klaus]

You should do the sorting in the SQLite query.
Example in Reviewnows' post #3.

 

[barx]

first off I would show a list of senders. To get these use something like

SELECT DISTINCT senderColumn FROM msgTable

This will pull a list of the different values for sender (i.e no duplicate values)

Then when a sender is picked, get the msgs from that sender to show in your scrollview.

"SELECT * FROM msgTable WHERE senderColumn='" & SelectedSender & "'"

Iterate through this list adding the records to the scrollview/listview as required

 

[marcick]

Yeah ...
Where I'm wrong is pretending to use dbUtils without a base knowledge of SQL ...
But yes, with your help I'm getting now the desired result.
Great people around here, thanks
Marco

 

[RandomCoder]

I found the W3Schools website a great resource whilst learning the syntax and functions of SQL...
http://www.w3schools.com/sql/sql_syntax.asp

Its amazing just how fast SQL queries are executed, it can quite literally return thousands of results matching the required criteria and sorted in the order you require in a fraction of a second.

Regards,
RandomCoder.

 

[marcick]

I found the W3Schools website a great resource whilst learning the syntax and functions of SQL...
http://www.w3schools.com/sql/sql_syntax.asp

Got that, well explained, thanks
Marco