SELECT query may return 0 to an infinite number of rows

Hello All,

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!
-Simon

I’m not aware of any of the Xojo supplied plugins working in the manner you describe where they only retrieve the data from the server in small batches

You could manually do this using various sql selects with LIMIT and OFFSET
But that will rerun your query

MBS may have other mechanisms to permit you to run the query once and then use a DB API to fetch rows as you describe

postgres has a cursor integrated mechanism.
https://www.postgresql.org/docs/9.2/plpgsql-cursors.html
but you have to deal with them using sql yourself. xojo has no build-in methods.

and here a small tutorial : https://www.postgresqltutorial.com/plpgsql-cursor/

Someone suggested, off list, that maybe you could run the query and put the results in a temporary table them use offset and limit on that temp table to avoid rerunning any complex query

It’s worth looking into as well

Thank you for your information, Jean-Yves. I forgot to mention that the database I used was Oracle (12.x).

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

no problem.
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”.

For example, see this thread for some suggestions.

Thank you for the input and the suggestion, Douglas.

As far as my “goal” is concerned, I have several of them. I am hoping that a Xojo solution is there without having to fall back on an alternative solution (such as by using Java and/or other tools).