Reuse Recordset

I’m working on an application that queries a database, then after the recordset is populated, will need to create possibly several different files in different formats so I’m thinking I need to be able to go thru the recordset several times instead of hitting the database multiple times…Does anybody have any ideas on what I would need to do…

I forgot to add, the scripts that will be run will be created by other people so I have no idea what will be coming back (during development).

Which database? Some allow moving the record set cursor forward and backward, some only forward.

Could be any of them, SQLITE, MYSQL, Postgres, Oracle, ODBC, MSSQL and CubeSQL…It will depend on where the data is and which system is being queried

Then I’d recommend to loop over the record set once and copy the data to an array of dictionaries or DTOs (data transfer objects). Alternatively you could create a SQLite in-memory database.

I’ve not used dictionaries much, do you have an example of what you’re talking about…remember I will have no idea until runtime what that data might look like

Dictionary

None at all?
So you might get a bag of peanuts, the poems of Milton, a variety of smells, or fingerprint records?

My point is that if you don’t know whats coming you are unlikely to be able to do anything useful with the data.
You cant send an email to a peanut.

if you know that whatever arrives, there will be a NAME field, and you need to list all names in alpha order, then you have a chance.

Whatever arrives, you add the data you NEED into a dictionary in a known format, after which your processing can operate on a known ‘kind’ of data taken from the dictionary.

it’s already working the problem is if someone requests the query results in 3 different formats, such as XLS, CSV and PIPE delimited file, I have to run the query 3 times. I’m just trying to avoid hitting the database multiple times since I aleady have the data. So I’m thinking a dictionary might be the way to go…

Good.
One thing:

9 times out of 10 if someone asks for Excel, they will accept CSV.
One common ‘workaround’ was literally to save a CSV file with a .XLS extension… Excel used to open it without issue, but newer versions will sometimes mention that the contents dont match the extension, but allow you to open it anyway.

The point there is you can assemble a CSV file in memory.
If the user wants CSV, save it
If they want Excel, save it with an XLS extension
If they want Pipe delimited, use ReplaceAll to swap the commas with “|” and save it as a TXT file.

why iterate across it 3 time for 3 formats ?
would seem to me you can just write to 1, 2, 3 or N output formats as needed and use the data once

Like I said, everything is working perfectly right now, I’m just trying to cut down on the number of queries back to the database.

Norman, what I’m trying to figure out is how to write 1,2,3 at the same time while I’m traversing the recordset since I can’t go back up to the top of the recordset and process again…

If you went with something like ActiveRecord once you read the data you can pretty much do whatever the heck you want with the data. And because it’s an ‘object’ you can add all necessary helper methods into it as needed.

Bob, does activerecord work with all databases??

It works with all of the native Xojo databases. Not sure how much testing and use it’s had with ODBC, Oracle and MS SQL Server since we don’t have any projects using them. Of them, I’d say MS SQL Server is probably the most likely to fail (not because of ActiveRecord but because MS SQL Server is weak in Xojo, in general).

Instead of:

// Query database Do Until rst.EOF // Do1 rst.MoveNext() Loop // Query database Do Until rst.EOF // Do2 rst.MoveNext() Loop // Query database Do Until rst.EOF // Do3 rst.MoveNext() Loop
do:

// Query database Do Until rst.EOF // Do1 // Do2 // Do3 rst.MoveNext() Loop

I’d make an interface, and each “output format” implements that interface
Then your record set traversal can take a list (array) of output formats and your code to dump out more than one format at a time is no more than

while rs.eof <> true

      for each writer in outputformatters
              writer.writeRow(rs)
     next

    rs.movenext
wend

And adding a new output format is create a class , implement the interface and make it possible for your users to select it and add it to the list of formats to write

done

I’ve never used interfaces but it sounds interesting…I will check it out

Norman, are there any examples of implementing and creating these interfaces, I’m not finding them

http://developer.xojo.com/userguide/interfaces