In the docs, I see this example for RecordSet.Edit:
rs.Edit
If Not db.Error Then
rs.Field("ProductName").StringValue = "Generic Widgets"
rs.Update
If db.Error Then
MsgBox("DB Error: " + db.ErrorMessage)
End If
Else
MsgBox("DB Error: " + db.ErrorMessage)
End If
Assuming I have already tested rs for any database error, is it necessary to test rs.Edit for a database error like you see above? Can placing the RecordSet into Edit mode sometimes cause an error that is not found by just testing the RecordSet itself for errors?
If I place a RecordSet in Edit mode, assign values to various fields in that RecordSet, and then issue RecordSet.Update, can I assume this is all done behind the scenes as a Transaction?
If Not db.Error Then
rs.Edit
rs.Field("myDBField1").Value = MyTextField1.Text
rs.Field("myDBField2").Value = MyTextField2.Text
rs.Update
Else
MsgBox db.ErrorMessage
Return
End If
Note that I am using RecordSet.Value in the above example instead of RecordSet.StringValue. I’m doing this to serve as TextValue (which as yet doesn’t exist) where StringValue is traditionally used, to get me in the practice of using Text instead of String when convenient, for the future when String disappears in Xojo. Is this okay? Or should I stick with using RecordSet.StringValue for now?
Yes. The database could have gone off-line since you acquired the recordset, for example.
No. Some databases, such as sqlite, may or may not default as using a transaction. Most, you have to be explicit.
My instinct is that RecordSet isn’t ready for the new framework, so you’re just causing it to do more work. I personally would wait until recordset has a true TextValue method before I gave any thought to making my code new-framework-ready. Note: I haven’t done much with the new framework, so I could be totally off base.
You should check for errors after every database operation. rs.Edit communicates with the database and could encounter an error. Someone else could have the record locked, network could be experiencing heavy traffic, sun spots…
I actually didn’t think rs.Edit communicated with anything other than the RecordSet being held in memory by Xojo. I thought only rs.Update would be communicating with the database during this sort of operation.
SQlite is an edge case. It’s the same as doing file operations. You only expect an error in some catastrophic situation where there’s not much you can do, but that is exactly when you need to catch the error and stop trying, because continuing to try to access the filesystem might just make matters worse.
it’s been easier for me to use the UPDATE query of sql then the recordset.edit method.
then if you need a transaction you can do it just before, and send one sql command of all your updates to the database.
also this will work with different databases, and can be very fast even if this is a remote database
[quote=366961:@Jean-Yves Pochez]it’s been easier for me to use the UPDATE query of sql then the recordset.edit method.
then if you need a transaction you can do it just before, and send one sql command of all your updates to the database.
also this will work with different databases, and can be very fast even if this is a remote database[/quote]
i use UPDATE sometimes if there isn’t too much fields to update.
What do you do if you have a window with 20 fields and you need to update those 20 fields using UPDATE query without doing the assigning 20 times???
Ok. My general format now is the following. I place the BEGIN TRANSACTION line before rs.Edit, though I imagine it could go immediately after it.
db.SQLExecute("BEGIN TRANSACTION")
rs.Edit
If db.Error Then
MsgBox db.ErrorMessage
Return
End If
rs.Field("MyDBField1").StringValue = MyField1.Text
rs.Fiield("MyDBField2").StringValue = MyField2.Text
rs.Update
If db.Error Then
MsgBox db.ErrorMessage
db.Rollback
Return
Else
db.Commit
End If
However, if rs.Update produces an error, wouldn’t there be no updating of the database anyway, so doing all this as a Transaction would be unnecessary, as there would be nothing to Rollback? Or is there a possibility that rs.Update could alter a database when producing an error and leave the database in that altered state, thus necessitating doing this as a Transaction? I certainly know I need to use a Transaction if there’s a series of rs.Edit/rs.Update blocks to be wrapped as a single Transaction. I was just wondering about the case where there’s only a single such block.
[quote=366989:@Richard Duke]i use UPDATE sometimes if there isn’t too much fields to update.
What do you do if you have a window with 20 fields and you need to update those 20 fields using UPDATE query without doing the assigning 20 times???[/quote]
that’s what the class interface is made for …
you make a class interface, assign this interface to all your concerned fields, then you make a loop for all the items in the class interface, and the update can be done for any number of items in the window.
you can also subclass your own window, windowthathasfieldstostore, and this window has it’s method to store your classinterface assigned fields.
[quote=366998:@Jean-Yves Pochez]that’s what the class interface is made for …
you make a class interface, assign this interface to all your concerned fields, then you make a loop for all the items in the class interface, and the update can be done for any number of items in the window.
you can also subclass your own window, windowthathasfieldstostore, and this window has it’s method to store your classinterface assigned fields.[/quote]
is there a sample some where that i can look and analyse??
sorry, did not see any in the example folder.
I followed long ago the developper guide for class interfaces.
I made a class interface with two methods: populatefromrecordset and savetodatabase
then I subclassed all window items I needed : checkbox, textedit, … and assigned the class interface to the subclasses
try it on an empty project with one or two different items, it’s quite easy to make it work.
A transaction is only necessary if you’re updating more than one record at a time and you want them all to succeed or fail together. In your example you can do away with the transaction.
And just to be pedantic, you should check for an error after Begin Transaction and before rs.Edit, in addition to the error checking you already have.
[quote=367019:@Jean-Yves Pochez]sorry, did not see any in the example folder.
I followed long ago the developper guide for class interfaces.
I made a class interface with two methods: populatefromrecordset and savetodatabase
then I subclassed all window items I needed : checkbox, textedit, … and assigned the class interface to the subclasses
try it on an empty project with one or two different items, it’s quite easy to make it work.[/quote]
I found that xojo book with the class interface chapter. it is the User Guide Fundamental
i got a sample project with those information you mention.
Instead of doing all that error coding I would subclass the SQliteDB class (or whichever DB Class you use) and override the methods after which you want to check for errors… Have the subclassed methods check for an error and if one is detected then throw an exception., Teh in you DB code use try… catch blocks.
That could make the error handling easier and help keep the code more readable.