VirtualMode & MySQL

[engee30]

Hello

I'd like to connect my ADO Database to the Listview, but in VirtualMode. It's got more than 300 000 entries, so I wouldn't like to load all of them into the listview, but have access to the database and show only those that are set to be visible. Since I've never worked with a listview that had such a feature, I need a step-by-step tutorial.
Thanks for any help.

Regards,
Pete

[TiKu]

Well, connecting the list view directly to the database would be a bad idea because database access probably isn't as performant as required.
You should load the data from the database into an internal data structure and let the list view run against this data structure. Or at least you should implement some caching that keeps not all records in memory, but also does not access the database for each tiny bit of data.

As I neither know the structure of your database nor the details that you want to display, I cannot help you with code.

Regards
TiKu

[engee30]

Um, basically the database holds all records of the lottery game KENO, which grows by another 420 or so every day. I've got fields for DrawNumber, DrawDate and NumbersDrawn. Right now, I just load everything into the listview, but that takes ages. So the only thing I came up with was to use VirtualMode.
I could load a part of the database internally, but just have no idea how to bind the data with the listview. I've been looking online for some solutions, but so far I've been out of luck. Looks like it's not something easy to do.

[TiKu]

Can you put together a small sample that fills the list view in normal mode with some sample data? I then could modify it to use virtual mode instead.

[engee30]

All right, I'll do that. I should upload a sample project tomorrow. Oh, I was lucky this time. I've just found an example of a virtual listview. To my surprise, I was already able to bind it with some data of 1,000,000 items stored in an array

[engee30]

Okay, here's a small demo. A listview connected to a db and then loaded with some data from the db.
That's basically all I need to deal with. All other functions in the code are related to the content of the listview rather than the db.

[TiKu]

Hi,

I've extended your sample project. Don't forget to set the list view's VirtualMode property to True in the Form editor. This property cannot be set at runtime.
Have a look at the code that fills the cache (the CacheItemsHint is important for MySQL). Microsoft T-SQL does not support the SELECT ... LIMIT statement, therefore I load ALL items into the cache on application start-up. MySQL supports SELECT ... LIMIT and therefore can fill the cache dynamically. I could not test the code for MySQL though.
If scrolling becomes slow with the MySQL caching behavior, you could modify this behavior a bit. For instance you could extend the cached interval by ~20 items in each direction.

Regards
TiKu

[engee30]

Lovely jubbly. Thank you so much TiKu. Works like a charm. The time in which the data loads has now significantly improved. I haven't encoutered any lag while scrolling, so it's fine as is. However, I've got 2 more questions. Is there a nice and easy method to clear the whole cache in an instant? It takes quite some time for the application to actually end. And the other question is, how do I update the cache while the app is still running? As I said earlier, I get around 420 new entries on a daily basis. I download it all straight from the servers providing all the draws. Is it to do with just allocating new data to the cache like:

Code: Select all

cacheEntry.ID= 'new draw no. for ID'
cacheEntry.Slo = 'new date for Slo'
cacheEntry.Dra = 'new draw for Dra'

Regards,
Pete

[TiKu]

To improve performance on application shutdown, you could try to replace the class with a user-defined type (Type CacheEntry ... End Type).

To update the cache while the app is running, just create a new cache and - when it is complete - replace the old cache with the new cache. The code in the CacheItemsHint event does exactly this, so you can learn from it.

Regards
TiKu

[engee30]

Okey-doke. Job done. However, I didn't change the class to a type. I had forgotten to compile the project, and had done performance tests in the IDE instead, which produced those poor readings. The cache clears easily and smoothly.
I wouldn't be myself if I didn't ask another question This time I've got a problem with sorting the listview. Every time I've tried to sort it, I get this error:

Code: Select all

Run-time error '-2147467259 (80004005)':
Automation error

Is it something to do with storing the data in the cache, which makes the control confused?

UPDATE
I've just found a similar thread dealing with the same problem:
viewtopic.php?f=28&t=131
Still, I can't figure out where I should call the refresh method

Kind regards,
Pete

[TiKu]

Well, in VirtualMode you need to sort the data source - in your case this is the cache. The good thing is that you can let MySQL sort the data for you. After the cache has been sorted, call the list view's Refresh method.
Or did I get you wrong and your actual question is which event to use to resort the data? This would be the ColumnClick event.

Regards
TiKu

[engee30]

No, you got me right. I'll have a go with your suggestion and let you know how it's worked.

[engee30]

I'm afraid there's no way I could handle the issue. Dealing with databases, and only recently with the cache, is quite beyond me. It's not as straighforward as it is with the code I have under ColumnClick (partly provided by you):

Code: Select all

Private Sub lvwDraws_ColumnClick(ByVal column As ExLVwLibUCtl.IListViewColumn, ByVal button As Integer, ByVal shift As Integer, ByVal x As Single, ByVal y As Single, ByVal hitTestDetails As ExLVwLibUCtl.HeaderHitTestConstants)

lvwDraws.SortOrder = IIf((lvwDraws.SortOrder = soAscending), soDescending, soAscending)

Select Case column.Index
Case 0
lvwDraws.SortItems sobCustom
Case Is > 0
lvwDraws.SortItems sobText, , , , , column.Index
End Select

End Sub

Code: Select all

Private Sub lvwDraws_CompareItems(ByVal FirstItem As ExLVwLibUCtl.IListViewItem, ByVal secondItem As ExLVwLibUCtl.IListViewItem, result As ExLVwLibUCtl.CompareResultConstants)
Dim lFirstNumber As Long
Dim lSecondNumber As Long

  lFirstNumber = ExtractIntFromText(FirstItem.Text)
  lSecondNumber = ExtractIntFromText(secondItem.Text)
  
  If lFirstNumber < lSecondNumber Then
    result = crFirstSecond
  ElseIf lFirstNumber > lSecondNumber Then
    result = crSecondFirst
  Else
    result = crEqual
  End If

End Sub

Code: Select all

Private Function ExtractIntFromText(ByVal s As String) As Long
  Dim i As Long
  Dim p As Long
  Dim ret As Long

  p = -1
  For i = Len(s) To 1 Step -1
    If IsNumeric(Mid$(s, i, 1)) Then
      p = i
    Else
      Exit For
    End If
  Next i
  If p >= 0 Then
    ret = CLng(Mid$(s, p))
  Else
    ret = -1
  End If

  ExtractIntFromText = ret
End Function

I would appreciate it if you could come up with another solution. I wouldn't like to end up using the same routine as it is used at the very startup of the app, which takes time anyway with over 300 000 items, and still counting. Looks like it is the last bit I'm stuck with.

I just wish alphi (the author of the thread viewtopic.php?f=28&t=131) had provided his solution to the problem in that thread.

[TiKu]

See attached file.

Regards
TiKu

[engee30]

Thanks TiKu. I really appreciate your help. This is right what I'll stick with. I couldn't think of any better idea by myself. Yours is, roughly speaking, like the one I meant in my previous post, where I wrote:

I wouldn't like to end up using the same routine as it is used at the very startup of the app

Obviously, you made some changes in the coding.
Like I said, I'm no expert in coding and the stuff, so I think I can call my project finished now. You're a person of great knowledge of VB and patience. Many thanks for your time given up helping me out.
I can't promise I won't be asking any more questions in the future, though.

Best regards,
Pete