Re: Loading Variable Array From SQL Caveat

Hi Sam,

Am 15.02.2024 um 22:13 schrieb Sam_L <lennon_s_j@xxxxxxxxxxx>:

What am I doing wrong in the code below? Each subsequent fetch from the cursor seems to load into element 1 of the array.

(There are 42,654 rows to be loaded. Granted, this is an unusually large number to load, but I'm accessing it by index to build random test data and loading rows one at a time works great, so I'm not stuck. But I am curious about this and why I can't get this to work.)

On OS version 7.4 and below, the FETCH can only load 32737 rows at a time. But with each FETCH, the target array is filled from index 1.

That's not a fault - it's a feature - and (AFAIK) it was always like that. The multi row FETCH was always used as: read a batch of rows, process them, read the next batch of rows.

From version 7.5 up you can load more rows at once. I think there still is a maximum value, but I didn't checked it. It might be the full INTEGER range now - but not necessarily.

But whenever I see multi-row fetches I always ask myself "Why?". Of course one can do things like that, but most of the time that's some kind of premature optimization.

If I need randomly sorted rows for testing - why not sort them in SQL with "ORDER BY RANDOM()"? If I need those randomized rows in a work file, why no "INSERT/SELECT" (mass INSERT)? If I need additional information, just JOIN it.

The database is in 99% of all cases faster than any own algorithm - so I always think "How can I make the database do the work for me?" and start looking for the easiest way.

To fully utilize SQL and the database one has to start thinking in SQL and sets, instead of procedures and rows.

If you write something about the "Why" - we can look for a better, easier and probably faster way to solve your problem.

HTH
Daniel