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
[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.
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
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.
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
[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
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.