Photographers who make extensive use of Photoshop have a saying: "Just because you can, doesnt mean you should. " I think the same concept often applies to programming.
Im still trying to expand/improve my use of Objects in Xojo. Heres a scenario I would appreciate knowledgable comments recommendations about.
Assume for simplicity I have a SQL db with a table for employees. I have a window that provides basic add/update/delete functionality for the Employee table.
How I HAVE done it in the past:
I do a select and retrieve all the records from the table and populate a Listbox with identifying info and put the primary key integer field in the rowtag. Then on a double-click, I get the rowtag and select the single record using the pkRecID, then populate all the controls on the update window from the recordset with rsEmployee.Field(FirstName).stringvalue type statements. (theres also an ADD and DELETE button on the form to but the process works the same). Then when user makes changes and clicks the save button I re-select the appropriate record and update it with the controls values to the recordset fields. This seems simple and straight forward to me.
But would using objects here make as much or more sense?
In my Global module, create a Class for Employee and a method (not part of the class) that would take a recordset as a parameter, instantiate an Employee Object and put all the stuff from the recordset into its properties. Then in the local update module, instead of putting the control values directly to the recordset fields and doing an update, instantiate another Employee object and populate it with the control values and then pass it to an update method of the employee class.
This seems way more complex to me and I just cant see the advantages of it. I would love to hear your thoughts.
I would stay with Method #1
a) you know where and when the data is coming in… and out
b) you limit the in-memory footprint and overhead to only what the listbox “needs” to inform the user
The only thing I would do (and you may be doing so already)… is to use SQL to update the table(s), and not the RS.EDIT/UPDATE functions… And perhaps NOT reselect the record, unless the value you display in the listbox changed, and you can’t have updated the listbox directly from the same data you updated the table with… Why make an additional round-trip to the database, when you might have done something like this (just an example)
newname="Dave"
SQL="Update mytable set Emp_Name='"+newname+"' where ID=42" // really should be a Prepared Statement
Listbox1.cell(0,14)=newName
Dave, I have heard before to avoid using the rs.edit/rs.update but I do use them because it seems easier to read the code. I have never been able to really understand the disadvantage to using them.
As to why I reselect the record, somewhere in all this I have read the a recordset wasn’t meant to retain data but just to load it and populate controls. I don’t understand this fully either but it at least is easy to implement. I didn’t reselect in the past, in fact I kept the RS open and used button controls to page through the records, rs.movenext and such. But like I said, I still trying to get a firm handle on new technologies. Delphi and Object-Pascal where the last time I did any serious programming until last year when I decided to catch back up.
if this is intended to be a multi user db where there could be several people making changes reselecting and verifying that the record has not changed since you edited it is prudent
some db’s have a mechanism where they can notify you of changes that occur so you can do this sort of thing more proactively
One big advantage of using an object is it allows you to more easily do “intelligent” updates - only write the values that actually changed. That greatly reduces the number of collisions. And for running balances, update the delta change, eg.,
update theTable set theValue = theValue + CurrentValue - OriginalValue
If 2 people update the value at the same time, the result is still correct.