Is there a way for me to open a cursor for a query (in SQL select) that could potentially return a very high number of rows by fetching the result one row at a time?
When I tried to do so with “RecordSet” or “RowSet”, it looks as if all rows are populated in the set before I was able to pull the data from the set. This does not work for me as the wait is too long (maximum to be tolerated is 3 seconds) and the set may also be running out of space, even when hours are allowed to wait for results.
Has anybody tried to do this with “MBS Xojo SQL Plugin”?
Thank you very much Norman for the insights and suggestions. I thought that I was not using the “RecordSet” and “RowSet” properly, but it seems I did not, but the design was not allowing us to deal with this type of queries.
This is easy to do in Java. Any suggestions on getting this done by getting the Xojo app to call a Java program to do the fetching and to pass the returned rows by way some sort of a queue to the caller?
I am hoping that Christian Schmitz would chime in and confirm that this sort of things could be done by using “MBS Java Plugin:JavaDB”.
I also forgot to say that I created a specific class in xojo, to handle a cursor independently of the database. I don’t use the postgres mechanism but I have a class that handles it.
it works sure for sqlite, postgres and mysql because I tested it, but it must also work for other databases.
as Norman said, it uses the limit and offset of the sql language, and stores the query to manage this. but of course if you want to get all the records if refers to, the query takes time. it’s only a short way to get all the records of a big query by little chunks.
Perhaps it would help if you described the ultimate goal of using that data in chunks, instead of only asking how to chunk the SQL return data. For example, if the goal is to populate a listbox, then consider just reading the visible rows “on demand”.