RecordSet vs DatabaseRecord

Databases
1

Is there a way to store each “record” from a RECORDSET into an array of “databaserecords” (or something?)

I need to thread the results of a large recordset, and was hoping to do something like this

THREAD

while not rs.eof
    .... add current rs record to an array
rs.movenext
wend

A TIMER

if thread.isRunning=false then me.mode=timer.modeoff
if array.ubound>=0 then 
    for i=0 to array.ubound
      ..... analyze "databaserecord" and add to listbox
    next i
    redim array(-1)
end if

The issue is the field information (name, type, count etc) is dynamic… so array cannot be a fixed string construct.
I need to be able to access… IDXFIELD level information

I could create a custom class for the array… but that adds the overhead of disecting the recordset to construct the class

I need
FieldName
FieldType
FieldValue (including IsNull)

Too bad [DatabaseRecord] is not a viable object type for this endeavor, as you cannot extract it from a RecordSet…
So at this point it looks like my “custom” class will be an class object with an array of DatabaseFIELDS instead.

unless someone has a better idea???

2

I normally just use a dictionary for this as it has more universal methods like a way to query list of keys.

3

I require the same functionality as a RECORDSET including order of return.
I would use the recordset directly, but it is inside the thread, and the listbox in in the GUI
so I have to put records in a bucket so the timer can update the listbox…

A dictionary is no help… as I need ALL the properties of a [DATABASEFIELD] in the exact order they were extracted
I guess I could use a sequential number as the “KEY” and the DatabaseField as the “value”
but I don’t see benefit in this situation over an “array”

4

a recordset is NOT made up of a bunch of database records (but given the docs I can see how this mistaken impression is arrived at)

its rows & columns you can iterate over using the recordset methods

so you’d need to pull the data out into “something else” (dictionary or some custom class you craft that preserves the column name, column type, value, etc)

5

While I’m realtively new to Xojo and I may be misunderstanding the question, but wouldn’t the combination of dictionary and JSONItem classes work?

6

No… for a number of reasons…

a) a dictionary is not guaranteed to return the results in any specific order
b) JSON would require overhead to create the JSON FROM the recordset, then Read it again later.

The requirement was to take a subset of the Recordset returned from an SQL query and not have to transform it multiple times … since the goal was speed.

The problem was solved using timers, threads, two custom classes and a lot of imagination… resulting in a percieved increase in speed of over 8x

7

Fun fact: the original SQL definition calls columns attributes and they do not have a guaranteed order.