I have a custom canvas control one of the properties is recordset which is assigned by calling SetRecordset(rs as recordset). My control can then display the recordset and performs edits by calling edit setting field value and then calling update. All fine the recordset updates I know because I have the dB open in another database editor. After I call update I then call refresh on my control to repaint the canvas which re displays all the records but the old field values are displayed. What is happening here?
record sets are snapshots and as such when you edit & commit updates you have to rebuild the recordset to refresh it with new values
Pain. So my concept of the user calling SetRecordset(rs as recordset) is flawed as they would need to recall the method after my control edits any values within the recordset to see the control updated?
If you use a RecordSet for more than tightly looping through results, you are asking for trouble. Copy the results to an array of Dictionaries. Keep that around.
What about writing back to the database?
[quote=89825:@Mike Charlesworth]What about writing back to the database?
[/quote]
If you’re saving a RecordSet so you can write back later, you’re very likely using database transactions to lock records in user time, which is beyond horrible. It’s especially horrible that the Database API suggests this might be a good thing to do.
Stick a primary key value in that Dictionary when you copy the RecordSet. Craft your own update statement later when user action triggers the update.
I write back when my textfield loses focus, check the text in the textfield has changed since it’s value was set and if it has:
mRS.Edit
mRS.IdxField(SelectedCell.column + 1).Value = txtEditString.Text
mrs.Update
My control only has access to the recordset which was passed to it, not the database.
[quote=89829:@Mike Charlesworth]My control only has access to the recordset which was passed to it, not the database.
[/quote]
OK. Well, we’ve had this discussion here many times before. That RecordSet has internal database info in it, a result set cursor at the very least. The Database API used by developers to connect databases doesn’t require that the plugin make what you’re doing safe. Sorry, that’s just how it is. Use RecordSets locally, copy to array/Dictionary if you need to use it longer.
Brad can you elaborate? What are the risks?
RecordSet is an opaque structure. You have no idea what it wraps underneath or what the conventions for use of those things are. Typical C APIs for database client libraries have some kind of a “cursor” structure or object, used to cycle through a result set. As you cycle through, the results may or may not be on the client side. So getting the next result might be very fast because it’s already made it to the client side, or it may be slow as the client has to get another page of results from the server. Having that cursor in existence may impose a read lock on the server. It may impose an implicit transaction that needs to be rolled back. You, as a RecordSet user in Xojo, have no idea. Those details aren’t documented for any of the supported databases.
The Xojo documentation also doesn’t tell you the safe thing to do, i.e. keep use of RecordSets local and in tight loops. The API itself is 15+ years old, and still reflects what Geoff described to me 13 years ago as “it should work like 4D”. The bigger problem is that multi-user databases don’t work like 4D. Transactions and locking are things you really need to think about, or if not, keep yourself from tripping on.
But I’ve expounded on this a number of times, and it usually falls on deaf ears of those who are having problems going down bad paths. I see it as my punishment for eating undercooked meat.
I agree with Brad. In my experience using the RecordSet as anything more than a temporary holding area for SQLSelect’s is dangerous.
So, if I copy the rs into an array of dictionaries and store the pk within the array and then release the rs. Then edit my values in the array and for any row I change in my array do a bulk transaction at some point when the user requests to plonk them back in the db?
[quote=89863:@Mike Charlesworth]So, if I copy the rs into an array of dictionaries and store the pk within the array and then release the rs.
[/quote]
Yes.
There are many approaches to this. Yours is one.
I’d suggest decoupling your thinking from trying to emulate what you’d do if RecordSet worked the way you expect. You’ll find better, more natural approaches. For example, I like to have a subclass of the Database class and define high lever operations on it, available through computed properties and methods. No SQL or SQL related structures or abstractions exit that class.
Ok thanks brad. I’ll have a rethink.