I’ve got an application that’s working pretty good and I want to see if I can’t screw it up. I want to learn about how the dictionary may help in managing recordsets and displaying them in listboxes.
The application is a very simple contacts database for a high school class. The number of records is about 600. It will always be about 600. The way it works now is the app launches, goes to the remote MySQL server, gets just the basic display data for each contact, loops through the data and puts the data into a listbox, and closes the recordset . The fields displayed are: primary key, first name, last name, email, and phone. If a user wants to see some more data or edit the data they double-click a row. This action queries the database for the individual contact data based on the primary key and puts that data into a window and closes the db connecction. If a user changes any fields they click an update button. I’m not concerned about concurrency at this point. Rarely will there be 2 people working on this.
I’m wondering what the benefit of putting the data into a dictionary might be? If not for a small set of data but for larger sets. The docs aren’t too clear to me. At least for this potential use.
If you mean the initial 4 or 5 fields that live in the listbox, then I’d advise there is no massive benefit for 600 records.
If you intend to read the whole ‘behind the scenes’ data into memory (you can have an in memory database, btw) then in theory you get access to the data faster than a later read-from-disc-on-demand
But you then run a small risk because any changes made to the in-memory database aren’t ‘real’ until you save them in a lump at the end. And a power cut means its all gone.
For larger initial sets than 600 , you can populate a dictionary or array and then fill the listbox on demand (just add n rows upfront , and only change the text of a row when the row is shown for the first time, say in the celltextpaint event)
This reduces the time-to-display the listbox window.
By the same token, you could create an in-memory db and populate that with a SELECT * from…
Then use it the same way as the dictionary.
To get a value from a dictionary, Xojo uses a hash algorithm to find the record you want.
Your key might be the primary key, and the data would be the value.
Finding a record from the primary key then becomes as simple as
thedata = myDictionary(the key)
You can think of this as equivalent to
//connect to database
select * from some table where primarykey = the key
//close connection
But without the connection times and the disc reads.
You’re right, I’m just putting the initial 4 or 5 fields in the listbox. I want to learn some skills in handling larger data sets and more users. Techniques or best practices involving concurrency, editing records, and data sets. I’m starting on something smaller could be helpful.
Couldn’t I load a record into a window and then allow the user to update that one record? Of course now there would be a difference in the 2 record sets unless as part of that I went and got the data again as a sort of refresh. This would perhaps reduce the issues surrounding a power outage as any changes would have been committed.
I suppose I could use a SQLite db but I know how to do that and this seems more fun.
Do I populate the dictionary the same way I do a listbox? Get the data and then loop through?
Why would you want to put the data into a Dictionary? Makes little sense to me. I don’t understand why you are going to the database 2 times either. The second time to get the data for the update page.
Make a Class and add all the properties you need for your fields to the class, and store the data in it. Then set the rowtags = the class.
No more extra trips to the database, updating can be done either immediately or when the user has finished with all the records.
When displaying a record for editing I would always pull from the database as the user would then have the actual data that they are updating. If you cache all the data in a dictionary (or row tag or in-memory database doesn’t matter). Then the record that the user edits may not be the record they think they are editing since someone else may have changed it. You would need to check for record freshness before saving to let the user know that someone else has already changed that record.
Don’t ever do this. Locking records is old-school and likely to get you into trouble when the user begins an edit and goes to lunch. Instead, only update the data that actually changes and update accumulated fields with the delta difference of the change.
From the Xojo database webinar on 7/1 I have the following note: do not use recordsets. Create a dictionary. Save just the primary key.
There was some discussion around this and I filed it away in my mind for future development and that’s what I’m trying to do now. I don’t recall all the details and should probably go watch that part again to refresh my memory.
As it is now I’m not keeping any records open through a db connection. I go get the records, put them in the listbox, and close the connection. If a user double-click a record they will get the latest and they won’t be locking it for edit. If they make a change they click the update button and it’s just an update. Yes, the problem is if at the same time someone else is changing the same record.
Only update the parts that changed. For static information, the last change wins. For calculated values, update the difference and it will always be in sync.
Duane, say you have only 3 fields (so it’s not complicated) Primary_Key, First_Name, Last_Name (only PK and First_name show in the listbox so it’s not ocmplicated)
Add a class to your project, call it myClass and add the 3 properties to it. Primary_Key As Integer, First_name As String, Last_name As String.
Now when you are adding the data to your Listbox you’d code
While rs.EOF = False
Dim myc As new myClass
myc.Priamary_Key = rs.Field(1).IntegerValue
myc.First_name = rs.Field(2).GetString
myc.Last_name = rs.Field(3).GetString
Listbox1.AddRow (myc.Primary_Key, myc.First_name)
Listbox1.RowTag(Listbox1.LastIndex) = myc
rs.MoveNext
Wend
now if a user wants to see ‘more’ info you code
Dim myc as myClass = listbox1.RowTag(listbox1.ListIndex)
and all your information for the student is myc and you don’t need to poll the database a second time.
[quote=127026:@Duane Mitchell]From the Xojo database webinar on 7/1 I have the following note: do not use recordsets. Create a dictionary. Save just the primary key.
As it is now I’m not keeping any records open through a db connection. I go get the records, put them in the listbox, and close the connection. If a user double-click a record they will get the latest and they won’t be locking it for edit. If they make a change they click the update button and it’s just an update.[/quote]
ActiveRecord does this (without the listbox part). Plus AR gives you some compiler warnings, auto complete, and some before/after events to play with. We also check to make sure that db fields have a matching counterpart in the data classes when doing the initial setup. Very handy on large projects.
AR doesn’t re-save a record when data hasn’t changed either so you don’t need to have code for IsDirty (handy for not updating LastModified dates when nothing has changed). And behind the scenes it is using Prepared Statements for Inserts, Updates, and Deletes (though you can still muck it up yourself if you’re not careful).
In cases where this is important (not all projects thinks so), we add some code to the ActiveRecord classes that checks the LastModified date of the record in question. If it’s newer we flag the user to ask them what they want to do. You should really do this check not only before Edit but before Save as well.
Record locking, as others have testified above, it a pain. I think it’s just easier to check last modified date. That has it’s own particular joys but the more complicated your record locking scheme is the more likely it will bite you. Keep it simple.
Pessimistic usually gets you in trouble - since you clock it on first access & hold it til things are saved
That can lead to the infamous “hey they went to lunch & left that record locked” which can have all kinds of implications (some db’s used to NOT commit anything until that lock was released which meant you could pile up a LOT of uncommitted data doing things this way)
Optimistic you DONT lock the record until you’re actually going to update it BUT you have to check & see if what you updated is still whats in the DB as if it isn’t someone else updated it between the time you grabbed the data & when you want to save it.
@Bob Keeney Thanks for bring up AR again. It was discussed in the seminar and I do have a note about it with a link. I like to know or at least understand how things are done “old school”. I’ll move up to AR quickly.
@Norman Palardy That got discussed in the webinar but it’s nice to hear it again with a little bit of a different description.