Xojo2024R1: WebListBox with DataSource - RowCount method

Targets Web
1

In my TableDataSource class (WebDataSource interface) I have RowCount and RowData methods.
The query is passed to TableDataSource.SetQuery method. The query is returning large number of rows from Postgres database, say over 50K.
My question is: should the query string passed to TableDataSource.SetQuery method include limit clause (ex: “LIMIT 100”) or not?

Note: the example shows that RowData is appending “LIMIT” and “OFFSET” ex:

sql = sql + " LIMIT " + Str(RowCount) + " OFFSET " + Str(RowOffset)
2

Datasource is designed to request the information needed (as needed) to the database. It depends on the rows shown on screen.

In other words you don’t need to provide the limit.

3

OK, but the problem is that in case of the large result set in my case the WebListBox does not show results (I gave up waiting after a minute or so), if I have the limit of 2000 in the query passed then I can see the data showing. There are still some issues when scrolling through the list, at some point the code breaks.

4

I don’t have such a large database to test, I’m not sure how long it will take. It could need a DataSource configuration adjustment or is the time it takes for the keys to be sorted/cached for your db.

If you can create a sample project that reproduce your problems, I’m sure someone will review it and give hints.

Looking at the Issues it seems that some work has been done to DataSource for next Xojo release. I read that 2024r2 is around the corner.

5

Well, I have run another test, it eventually showed data in the list. When scrolling through the list at some point the code breaks, exactly in the RowCount. It doesn’t break right away but after few clicks.

6

Another question on this topic. Assuming I have 159,100 rows in the result set. How many times is the RowCount executed if I scroll through the list? The problem I see is that this code:


sql = "SELECT COUNT(*) AS c FROM" + " " + vTableName + " " + vLeftJoin + " " + vWhereClause

If Param1 = "" Then
  rs = Session.db.SelectSQL(sql)
Else
  rs = Session.db.SelectSQL(sql,Param1)
End If

Var c As Integer 
c = rs.Column("c").IntegerValue

breaks, the rs will not show the count but the values of the row. It seems that the initial call to this method works fine but next time when I scroll past the first batch it breaks.

7

The LIMIT and OFFSET should go in the RowData method, using the parameters you will be receiving (rowCount and rowOffset).

That method you wrote, “SetQuery”, it shouldn’t be doing any query to the database, it should just prepare the query for when RowData method is called.

8

Right, that is what I did, ex:
TableDataSource.SetQuery


mQuery = pQuery
Param1 = pParam1
If pParam1.Length > 0 Then 
  mQuery = mQuery.Replace("?","$1")
End If

Return
9

Further info, the query I have takes roughly 22 seconds to show the data on the ResultsList (I do it on my machine connecting to the remote Postgres database server, so I can accept this for now), there are over 159,000 rows to select (9 columns per row).

The TableDataSource.RowCount is trying to execute “SELECT COUNT(*) …” but it is failing, when I scroll past the first batch in the ResultsList.

Btw, I have no idea what the “batch size” is, it looks like around 200 rows in my case but I suspect this depends on the size of the list box control?