SQL Update with a delete / update / insert in one go
in [Visual Objects]
|
Prev: Register problem Report Pro
Next: Error in OLE Client.
From: johan.nel on 21 Oct 2007 08:57 > I'm not a rugby follower of any note - it is considered a minor Well I enjoy my sport, being a keen participant at school (rugby and cricket) and have done competitive cycling till the young age of 35. > ...just can't take sport that seriously <sigh> I am a keen follower of sport but don't go tail between my legs if my team don't do well though. However seeing that what we call sport (war) is work to others, I can see why some people gets so upset. However cannot really see programming in that context, a whole country in mourning because you did not perform up to standards. Well maybe we should advocate that programming is declared a sport. Just think of all the people cheering us on to write code and making lots of money for having sponsorship logos on our shirts. Yip I think that would be a good idea <g> And next weeks match would be between C# Cowboys and VO Bulldogs <VBG>
From: Geoff Schaller on 21 Oct 2007 09:05 Johan. Then I don't know what your problem is. You seem to have the solution so there doesn't seem to be a question. > I really do give your ideas attention, but if I can see it not going > to work, should I just keep quiet? You haven't demonstrated that you understood what we suggested - it is of equal use for DBF or SQL and bBrowser with arrays does not imply lesser performance. Far from it. I find the array approach to be conceptually easier and the user gets to make all their changes before data is committed to server. > Nope I only want to ensure logtags are not duplicated in the system. You only have two options: PK/FK or not. One way is automatic and the other manual. You choose. > So in reality Log50 on FormNo X gets the same TagNo as Log01 on X + 1, > which is only realised when the forms gets back to the office. So what do you want to do? Allow duplicates or correct the tags? > my real question that I asked. What is an efficient solution to > update the TagNo's in Form X + 1 as all of the tagno's should be > incremented by 1. Exactly that. Update MyTable Set Tagno = Tagno + 1 where xxxxxxxx etc > Yes they are unique but we working with humans here and human errors > do occur. The above example should explain the scenario. But this doesn't change the requirement. You have to work around it and you either adjust the computer records or you change the tags. You choose. (I've said that before <g>) > I agree fully, although a proper setup in a SQL database, could save > you lots of coding that you would have to do on DBF. And has the No, its basically the same. > I differ on this one. No way you can protect a DBF from a user using > a simple DBU style tool (except if you encrypt DBF table), but even > that is not idiot proof. However I can setup a SQL database to keeps > its integrity immaterial of what tool the user use to modify data. News Flash... there are SQL tools that allow the same thing as DBU <g>. > To some extend yes, however we do have: > if lSuccess > commit() > else > rollback() > endif But as I said before, with our array approach lSuccess is never false. Rollback is never necessary. Geoff
From: Karl Faller on 21 Oct 2007 09:55 Johan, one thing i can't quite see in your scenario is the importance of "closed lines" in numbering. If your tags come from some prefabricated spool, as i presume, similiar e.g. to parcel tagss, IMO it's totally irrelevant "which" number is on any given log. The only thing what counts is it's uniqueness, and that should be ensured by it being prefabricated... The real number has (should not!) have any meaning by itself. Or possibly i don't understand your procs at all, then never mind ;-) Karl
From: johan.nel on 21 Oct 2007 10:36 Geoff, > Then I don't know what your problem is. You seem to have the > solution so there doesn't seem to be a question. > You haven't demonstrated that you understood what we suggested - Well again, my question was give me example of how you would update a table where you have PK of KeyFld + ChildFld and a unique ChildFld and the update/insert could lead to duplicate ChildFld values due to human error, and most of what I got back was a discussion around why on earth do I need a unique ChildFld. > lesser performance. Far from it. I find the array approach to be > conceptually easier and the user gets to make all their changes > before data is committed to server. I agree and I also make use of ArrayServers in my coding. However in this scenario I use a Scatter / Gather approach with control names preceded by a prefix, and yes all are validated before writing to the database (check for duplicate PK and valid control values), except unique TagNo's, which I thought would be easier to manage if I only trap it when the server could not update the database. > So what do you want to do? Allow duplicates or correct the tags? Correct the Tags, however the wrong tag could be on another Form and not always on the current form where the error lies. > Exactly that. Update MyTable Set Tagno = Tagno + 1 where xxxxxxxx Yip I know that although not 100% that simple in this scenario. > you either adjust the computer records or you change the tags. Yes I know the computer records need to be updated to reflect their real tagno's. But now again, cause TagNo is unique so when it gets incremented you run into problems again as the new tagno could already be in use and voila this update does not get done. > News Flash...there are SQL tools that allow the same thing as DBU Yip but if my SQL database is setup to have a PK no way that tool can create a duplicate record. Combine that with a decent UPDATE/DELETE/ INSERT Trigger and proper user access rights on the database, I can at least trace what a user did in this sqlDBU <g>. > But as I said before, with our array approach lSuccess is > never false. Rollback is never necessary. Yes in my 20+ years of programming I can also say that my Scatter/ Gather with proper validation did not ever return false on lSuccess, until I started converting this application to SQL. The method of testing in DBF just seemed like a lot of overhead on a SQL database hence my question again which I have not have answered yet... <VBG>. Johan
From: johan.nel on 21 Oct 2007 11:03
Hi Karl, Tags used in our timber industry can be of two kinds, barcoded, or a plastic tag attached to some form of nail that is driven into the thin end of a log by a special hammer. http://www.hangtags-labels.com/pics/photo7.jpg is an example. Tags are prefabricated and gets ordered in sequential ranges of numbers. So each log sold is recorded against its tagno. And yes the number is only there for uniqueness. However the sequential numbering is just a convenience from a data capturing point of view infield (just enter the first number on form and only when there is a break in the sequence), as well as computer capturing, as it can then be auto-created given a starting value when a form is added instead of having as part of the capturing to also capture the tagno. The problem arise not so much from the tag numbers, it has to do with human error where a tag number is recorded wrongly and hence it generates "duplicates". Not all companies use barcode scanners and dataloggers which limits the human error factor to a large extent. The system should however cater for the smaller companies too that capture still using paper log tally sheets. HTH, Johan |