MySQL move back in recordset

Databases
1

I know that MySQL only supports MoveNext in a recordset and not MovePrevious. So I’m wondering what the best way would be to build that feature?

In my app I start with a table of records to browse in a detail window. I am thinking that I could make an array of the primary key column of the table and use that or maybe save the record set someway and pass that to the record browsing window? Hoping someone could share some experience and suggestions. Thanks.

Duane Mitchell

2

Hi Duane - a couple of important question that needs to be answer before figuring out the best way to set something like this up:

Are there child records? (i.e invoices and invoice_line_items)
How are you going to handle updating data? By field? By Record?
How are you going to handle data that’s been updated on the server? A timer that occasionally checks the locally stored record data for changes?

When I’ve done things like this with MySQL, I’ve often represented the data using classes. So if I have a invoices and line items, I’d create an invoices class and a line_items class. They’d each have properties to represent the field data for the record (e.g. record_id as integer, invoice_number as integer, description as string) and also some properties to represent state data (e.g. modified as boolean, modified_at as date, etc.)

Invoices would also have a property that’s an array or line_items objects. So invoices.line_items() would have x number of line_item_objects in it.

This makes it easy to represent relational data locally. And it tend to be reusable because you’re simply adding a property invoices() as invoice to your window, class, module where you’re actually working with the record set data.

So if I had a window and a listbox to display invoice data. I might add a window level property invoices() as invoice and then load it with data from a returned recordset. I’d then load the listbox with data from the array of invoice objects, possibly using the invoices(i).record_id property as the rowtag.

Then if a user wants to edit that invoice, I can open up a window to display data from the invoice object (stored recordset data) OR I could load a fresh copy of just that record’s data using the invoice(i).record_id value.

This is a very high-level take, but hopefully it’s helpful and makes it clear why the questions I started with are very important.

Anthony

3

Yes, there are child records. Two listboxes of related records. Right now the updates are being made by record, if I understand this correctly. A user may change one of the 85 fields and all fields are gathered up and updated to the server. There’s generally only 2 or 3 people using this system. There is no locally stored data, it’s all off the server.

I know exactly what you are describing thanks to Paul Lefebvre’s excellent webinar yesterday on OOP but it’s not something I’ve done…yet. Maybe this is it. Thank you for the detailed description.

4

If you will be doing a lot of reading with this offline recordset then consider writing it to a temporary local SQLite database (which is very fast and supports all methods including movePrevious).

5

Good suggestion. I’ve considered adding such a database for other reasons as well.

6

Duane, regardless of the database, you shouldn’t keep a RecordSet around longer than to immediately do something with the result set.

7

I guess, in part, that’s what I’m learning to do.