I have been building a Sqllite3 DB app. Every time I have a method or an action that has to access the DB I have to define a RecordSet (Dim abc as RecordSet). As I am learning XOJO I realized I can define a property with a container of type RecordSet and access that from all my methods and actions. Is there a Drawback to doing this. When should I avoid this or not?
all of my recordsets are a local property of a window, or a method for importing datas.
I don’t have a recordset that is global to the application.
the database is a property of the application (or of a document)
but not a recordset.
Recordset’s should be local properties only and NOT global to a window or application. You’re only opening yourself up for a world of hurt. I say this from fixing a bunch of apps that have recordset properties on windows and globally.
Get the data in the recordset. Keep track of the record ID (in local property), display the data you need.
When you want to save the data there are two steps:
- Is this new data? If so, use the DatabaseRecord class (or plain SQLExecute query to insert) to insert the data
- Is this existing data? if so, create second local RS, edit the recordset, load any updated values into it and do an Update (or do an SQLExecute query to update).
Item 2 is kind of important because if the data has changed between the time you originally opened it and when you saved it you have the opportunity to realize it at this point.
There are various ways of doing this but I’d recommend NOT having recordset as window properties. Obviously Jean-Yves does with no problems but I’d be wary of recommending that to a newbie.
FWIW, we have a number of videos available to subscribers at http://xojo.bkeeney.com/XojoTraining/ that show how to use a database. This is for beginner level and we have some advanced ones as well. We have over 200 videos (about 65 hours worth).
My gut told me not to do this hence the question. I did test it and it works but for this application the RecordSet is a throw away. Get the data I need display it and close the RecordSet. My other thinking is, in single user mode is it more efficient to leverage a global property. All works great but Ill stick to local for now. Learning to leverage other features
Thanks for feedback.
[quote=364577:@Bob Keeney]There are various ways of doing this but I’d recommend NOT having recordset as window properties. Obviously Jean-Yves does with no problems but I’d be wary of recommending that to a newbie.
[/quote]
I have (some) recordsets in a window for one main reason : speed. when you deal with remote databases, it’s important to make as little requests as possible to the server. this is for linked tables. ( Bob you do so for reports no ?). you build one big recordset with all the linked datas, and store it in the window (or method) so that the dependent tables get the same datas without the need to ask more to the server, and thus gain speed in remote db apps.
I always define a recordset at the start of a method and use it one or more times within that method. I only create two RecordSets when I need both RecordSets at once (rarely).
I never create global recordsets. You can always pass a recordset as a parameter to a method or return it from a method.
I use recordsets as a property of a class instance, so each instance has its own private recordset. That way, it’s fine to have the recordset live longer, and you avoid the pitfalls of a global recordset. Note that I don’t use the recordset for updates. It’s only as an iterator (and normally not long-lived).