web: reading database in smaller quantities. Where is the end

I want to read a MySQL table and show it in a listbox after pushing a button
I have a webpage with a listbox1(3 columns) and a button1
The table has 470 records
I installed a timer because the routine never ends.
Never have seen this and because there is no error I don’t know how to react.
I tried system.debuglog
But there is no output

event button1.action tmr = new Timer tmr.Period = 1000 tmr.Mode = Timer.ModeMultiple AddHandler tmr.Action, AddressOf showRecordSetInListbox

[code]
sub showRecordSetInListbox(sender as timer)
dim fieldnames() as string = array(“field1,field2,field3”)
dim sql as String = “select “+split(fieldnames,”,”)+ " from “+ Mytable + " limit “+ str(ctr)+”,100”
rs =db.SQLSelect(sql)
ctr = ctr +100

if rs.RecordCount>0 then
for i as Integer = 0 to rs.RecordCount
dim aColumns() As string
dim field As integer
for fn as integer = 0 to UBound(aFlds)
aColumns.Append rs.Field(fieldnames(field)).StringValue
next
ListBox1.AddRow aColumns
rs.MoveNext
next
end If[/code]
The subroutine ‘showRecordSetInListbox’ passes 4 times without error
The 5th time it ends in a ‘SessionShutDownThread’ with no message
The listbox is filled with 100 records

What am i overseeing
The 5th time he has to read 70 records
How do I detect this and how do I a clean close.

I don’t use while not rs.EOF
Because every block of 100 records has apparently an EOF.

not sure about your main issue… but DON’T DO THIS

 for i as Integer = 0 to rs.RecordCount
  <other stuff>
      rs.MoveNext
    next

do it this way instead… much safer in my opinion

 while not rs.eof
  <other stuff>
      rs.MoveNext
wend

As I wrote in my message
I did this
but I looked at rs after every block of 100 records and it showed BOF=false EOF =true
By consequence this doesn’t work, because every group of 100 records shows EOF=true

It would work if you don’t use a timer
but then he ends in a black hole.

Strange thing is that this works in a Desktop version where I use while not rs.eof

Why in the web-version does he close the session?

0 - recordcount is recordcount + 1 reads ( ie/ 0 to 10 is 11 reads not 10 )

use 1 to recordcount
OR
0 to recordcount -1

I tried this
But no deal.
So I tried more debugging
He reads 3 blocks of 100 records
then he passes

aColumns.Append rs.Field(m9).StringValue (see below)

and jumps out of the routine, always the same
So, I put a try to catch an error without any result and no msgbox with an error

for i as Integer = 0 to rs.RecordCount-1 dim aColumns() As string dim field As integer for fn as integer = 0 to UBound(aFlds) field = val(aFlds(fn)) dim m9 As string = pmF1.fldName(field) try aColumns.Append rs.Field(m9).StringValue catch e as RunTimeException MsgBox e.Message end try next ListBox1.AddRow aColumns rs.MoveNext next

You’re probably trying to access a field that isn’t in the result set based on the code
Why not just use fieldcount ?
Something like

  for i as Integer = 0 to rs.RecordCount-1
      for fn as integer =  1to rs.FieldCount
          aColumns.Append rs.idxField(fn).StringValue
        catch e as RunTimeException
          MsgBox e.Message
        end try
      next
      ListBox1.AddRow aColumns
      rs.MoveNext
    next

This gives the same result. Always after 300 records (3 x 100) and always at the same record field 1
Crazy thing is that I’m doing the same in a Desktop app with the same table, filling up a listbox. And that works without a timer and
using while not rs.EOF
Drives me crazy

if rs.RecordCount>0 then for i as Integer = 0 to rs.RecordCount-1 dim aColumns() As string dim field As integer for fn as integer = 1 to rs.FieldCount try aColumns.Append rs.IdxField(fn).StringValue catch e as RunTimeException MsgBox e.Message end try next ListBox1.AddRow aColumns rs.MoveNext next end If

That seems very odd to me. I would look into why there is an EOF marker after 100 records.

I am not very familiar with mySQL, but Isn’t this statement precisely retrieving blocks of 100 records?

What happens if you dont limit the number of records in the query? something like

dim sql as String = "select "+split(fieldnames,",")+ " from "+ Mytable You would have to remove the loop wrapping the select or calling the sub, or else you will be querying the whole table severl times.

Do you still fail after 300?

I tried that first and because it didn’t work, I changed to the method with the timer.

I connected to another table and started the app with xojo2014r1 and that worked
I changed to version xojo2015r4 and the app ended in a black hole.
I changed again to xojo2014r1 and it worked again.

Could this be a bug in the mySQL plugin
Or
Could it be a speedproblem ? I mean is Xojo treating something to fast or to slow or other speedproblems

[h]Is there a way to debug this ???[/h]

Is there nobody who could test this on another installation with version 2015r4?
I could send you the table?
Or my app
Or …

[quote=241870:@roland brouwers]Is there nobody who could test this on another installation with version 2015r4?
I could send you the table?
Or my app
Or …[/quote]

I do have 2015r4 and El Capitan, if you want I can run a test for you.

I do have customers as well that are complaining that the overall all seams way slower than previous builds and that it takes longer time to load the data. So I don’t know if it’s from XOJO or the plugin but indeed, comparing to an old version it does run slower some times.

Please let me know if you need any help with testing .

When you use the LIMIT option, SELECT will return a maximum of LIMIT rows. It can return less or even 0.
Each ‘set of 100 records’ (RecordSet) will have it’s own EOF, which is not the total rows SELECT would return without the LIMIT.

Looking at your code, I am guessing that ‘ctr’ is a global variable that you set to 0 before calling that routine.

Your question: Where is the end?
When you read the last segment of rows, it will have RecordCount < 100.
Without checking for the last segment, your routine will never end until ‘ctr’ grows unreasonable.

Add a flag for the last segment and try changing your routine to a function that returns True when the last segment is processed.

ctr is a property of the webpage.
I changed the program so that for the last block, which is smaller then 100, he will do a ‘while not rs.EOF’.
But the problem stays the same.

It works with version 2014r1, but it worked also without this modification with this version.

With another table he drops into an error.

Could you or somebody have a look at that error?

This would be nice for debugging, because 2015r4 doesn’t generates an error

Some little voice in me tells me that the problem is not the MySQL plugin but the Listbox, or something in between.

I’ve got a couple of Xojo 2015 versions, as well as a VM web server for testing. I could probably take a look to see if a different environment helps. Do you have the app and tables online somewhere? You can message the details.