Over the past few days I have tried creating and editing new records with the TSQLQuery component. I succeeded, but with quite a workaround.
As stated in bug report 0016076 (not authored by me) TSQLQuery gives errors if you try to create a new record and think TSQLQuery would all handle it. The issue addressed in the bug report is that all databases address making new records in different ways. The suggestion is to provide additional SQL lines to gain control over that process. Here I would like to discuss (and hopefully learn) about other things I found. 1) TSQLQuery doesn't really create a new record at insert or append. Instead, the only thing that is done is adding a row in the dataset [InsertInternal doesn't do anything]. The insert is not done and at ApplyUpdates the insert is lost because the dataset doesn't know the difference between updates and inserts. The insert is lost, because the new row is used to try to update a record that doesn't exist result in a fail. However, the InsertSQL line is executed which leads to a new record and the next observation. 2) The InsertSQL is executed, but since these are fixed strings I cannot put my new values into the strings. Trying to change the InsertSQL leads to an error because you cannot change the InsertSQL on an active dataset (rightfully so). So, the InsertSQL can create a new record but not with the right values. What I would like to see here is a insert statement with parameters and a possibility to provide parameters. Is that already there? 3) At ApplyUpdates the dataset is forwarded to the database as a list of updates. Which doesn't necessarily have to be true. What I would suggest here is an implementation of the Unit of Work pattern ( http://martinfowler.com/eaaCatalog/unitOfWork.html). Using the Unit of Work pattern you can make a difference between update, insert and delete actions. The pattern also allows to make the processing of the dataset one compact unit with some favorable effects on atomicity and concurrency. 4) Creating an ID for a new record can be done at two moments a) at the moment you want the new record b) at the moment you really save the new record. With (a) you can use the ID in references immediately without too much complexity, with (b) you don't waist IDs at the moment you are not sure you will ever save the record. So, it would be nice to have a property to choose when the InsertSQL is executed. Of course, I am not experienced enough (with FreePascal) to implement this or even try to. But I hope my suggestions will help the development of TSQLQuery. I noticed there is quite a bit of discussion about the database components lately, but I don't know the development plans. Also, if I have overlooked anything I am happy to learn. Regards, Albert
-- _______________________________________________ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
