B4J Question Save/Load Listview items in SQLite

[ThRuST]

This concerns a B4J listview with added items Test1 and Test2. I am using SQLite with UPDATE to store and retrieve listview items which can be single-clicked in the listview. If I save them with Array As Object as listview.items it looks like this in the database: [Test1, Test2] and when I put them in a List and save them as Array As Strings it looks like this: (ArrayList) [Test1, Test2]

The SQLite table name is User and the cell is Categories.

When I load it back from SQLite it is treated as one string so when added to the listview it's not possible to select each of them only the whole string as [Test1, Test2].

How is it possible to Save (by using UPDATE) and Load Listview items back to work properly in the Listview?
I've got a clue to turn it into a CSV or use B4XSerializor but dunno how to make it work.

UPDATE:
I added a little example that you can make it work and add to your post. When you post your update make sure to add your comment at the history log in the source and save it with a new version number, starting from v0.1. The one who make it work first will win a free virtual toaster and respect from this community

v0.1 Basic template by ThRuST (use this if you want to add your solution).
v0.2 Added. Solution by Cableguy.

 

[klaus]

This concerns a B4J listview with added items Test1 and Test2.

How do you fill the ListView?

 

[ThRuST]

I do it like this, I check if the item allready exists if not I add it

' ########## Search Categories #############
Sub SearchCategories

    If TextboxCategories.Text <> "" Then
       
    Dim i As Int = ListviewCategories.Items.IndexOf(TextboxCategories.Text)
    If i = -1 Then
        'Log("Not found")
   
        FXnotify.ShowNotification4("Mindcraft", "Category " & TextboxCategories.Text & " was added.", NotifyIcon, "CENTER", 3000)
   
        ' Add new item to Categories listview
        ListviewCategories.Items.Add(TextboxCategories.Text)
        ListviewCategories.ScrollTo(ListviewCategories.Items.size - 1)
        ListviewCategories.SelectedIndex = ListviewCategories.Items.size - 1

        ListviewCategories.Items.Sort(True)
       
    Else
        'Log("Found!")

        FXnotify.ShowNotification4("Mindcraft", "Category " & TextboxCategories.Text & " was located.", NotifyIcon, "CENTER", 3000)
        ListviewCategories.SelectedIndex = i
    End If
   
    Else
        FXnotify.ShowNotification4("Mindcraft", "Please enter a category name.", NotifyIcon, "CENTER", 3000)

End If
           
End Sub

Then I save and load it like this

Sub SQLite_SaveCategories
   
    'Try
        Dim cList As List
        cList.Initialize
'       
'        ' Save Categories to database
'        'SQLiteLocal.ExecNonQuery("DELETE FROM User WHERE Categories = " & Main.ID_SQL)
'        'Dim MyArray() As String
'       
        For Each test As String In Main.ListviewCategories.Items
              cList.Add(test)
        Next
       
        SQLiteLocal.BeginTransaction
        SQLiteLocal.ExecNonQuery2("UPDATE user SET Categories=? WHERE Id=?",Array As String(cList, 1))
        SQLiteLocal.TransactionSuccessful
   
       
    'Catch
        'Log(LastException)
    'End Try
   
End Sub

Sub SQLite_LoadCategories
   
    Dim cArray() As String
    Dim Cursor As ResultSet
    Cursor = SQLiteLocal.ExecQuery("SELECT Categories FROM user")
    Do While Cursor.NextRow
    cArray(Cursor.GetString("Categories"))
   
'        'Log(Cursor.GetString("col1"))
'        'Log(Cursor.GetInt("col2"))
    Loop
'   
    For Each item As String In cArray
        Main.ListviewCategories.Items.AddAll(Array As String(item))
    Next
   
End Sub

In SQLite table user cell category now holds : (ArrayList) [Test1, Test2]
I have an idea to split the string by using indexof(",") but I'd like to know what you think about that.

 

[klaus]

I think that the follwing:
Here you can heve several items.

For Each test As String In Main.ListviewCategories.Items
    cList.Add(test)
Next

And then you update the database with the List object as a string.
This gives you the result you get.
What exactly do you want to have in the cell when you update?

 

[ThRuST]

I want the added items in the listview, in this case two items Test1 and Test2 to be saved (updated) in the database and then then loaded back into the listview when I restart the program so the user can click on each line to select in this case a category that was added to the listview. My problem is that it loads it back as one long string. There must be a commonly known way how to work with listview items inside the database.

 

[stevel05]

Roger, As I explained to you, the database does not recognise sub values. There is no commonly known way to do this. If you want to access the individual items, you need to change the design of the database. If you just want to save and retrieve a list of data, you need to build the string before you save it, and then parse it when you read it.

Using csv is a simple as it's going to get. (Assuming there are no commas in the data)

Dim Str As String
    For Each S As String In List1
     Str = Str & S & ","
    Next
    Str = Str.Substring2(0,Str.Length-1)

Then to parse it:

Dim List2 As List = Regex.Split(",",Str)

 

[ThRuST]

Thanks Steve, I just wanted to pass this question to the forum so it can help others. It makes sense to split the string so that should not be too much of a problem. At first I was thinking it was an easy way to just load it back into an array directly into the listview but I was wrong as usual I should make this smiley my avatar image
Anyway thanks again and I will play around with string splitting to gain some experience in this field, but why Regex Steve!!! ouch that's complicated stuff.

 

[ThRuST]

btw how would you deal with the [ and ] chars? if str.Contains("[" or "]") then return, or something?

 

[stevel05]

You only get the '[]' characters because you are saving a raw list. If you create the csv string and save that you will only get the string back.

Regex, because that is the way to split a delimited string into an array(or List in this case)

 

[stevel05]

If you don't want to use CSV remember you can use the B4xSerilizator

 

[ThRuST]

Ok thanks I will check this up. B4xSerializator is probably a code module since I did not see it in the libraries manager.

 

[stevel05]

It part of the jRandomAccessFile Library

 

[ThRuST]

I'm sorry Steve, but I was lazy so I wrote only a template. Hoping that you or any of the other coding gurus would make it work hehe
Hopefully you will fall into my trap, you'll find my template v0.1 in Post #1. Good luck!!!

 

[ThRuST]

Steve and Klaus this is a challenge for you to prove who can answer first and solve it and be hero of the day

 

[ThRuST]

And Cableguy is a force to be counted with, so who will be the Jedi programmer of this Friday? Time will tell

 

[stevel05]

Show us what you've tried and we'll help you get it working.

 

[Cableguy]

Here you GO

(Actually my solution is a lot similar to Steve's, only that I didn't use regex to split it...)

 

[ThRuST]

Steve, you will find my source code template in post #1, please contribute by posting your own solution and others will do the same. We will learn multiple ways

 

[ThRuST]

Cableguy, your solution is most impressive.

*** Cableguy is the coding hero of the day ***

 

[ThRuST]

Cableguy, your solution (v0.2) was added to post #1.