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 07:07 Geoff, > Michael and I are in complete agreement. > Doesn't that suggest something? <g> Yip both of you don't see the real life issues I have to deal with in storing the stuff in the database <g> > Precisely! That is why our suggestion is appropriate. Not so sure about this one. > You need to modify the physical tag to match the computer record. Ermmmm, so you suggest I supply each logtally operator a tool to change the number on the tag? > But you haven't said why "sql" should be any different. Well I can drop code from my DBF system and leave the unique tag number checking to the Server and only respond if there are problems. Its about easier management of data integrity in SQL. >You can equally do the hard way in SQL too <g>. Yip I know, and that is why I asked the question to hear others ideas on how to write the data back into the database in the best way. Johan. > > Geoff
From: johan.nel on 21 Oct 2007 07:11 < celebration off > < poker face on > Ok sorry won't mention the weekend's rugby < poker face off > < celebration on > Did we not beat them 36-0 a couple of weeks ago? <VBG> Johan
From: Geoff Schaller on 21 Oct 2007 07:24 Johan. > Yip both of you don't see the real life issues I have to deal with in > storing the stuff in the database <g> Perhaps you aren't giving our suggestion enough attention? You don't think that similar issues can't arise with factories allocating bar codes or serial numbers to manufactured output? Number duplication is a daily toil. Your logs don't have a monopoly on this <g>. > Ermmmm, so you suggest I supply each logtally operator a tool to > change the number on the tag? You seem to want it both ways. Either the tags are unique and that is sacrosanct or duplications occur and must be accommodated. You don't say which but it matters not. If you need to retain duplicates then a PK is out of the question for the child ID - you can answer this. So how do you solve your numbering problem? What ever you do in the DBF world you also do in the SQL. > Well I can drop code from my DBF system and leave the unique tag > number checking to the Server and only respond if there are problems. > Its about easier management of data integrity in SQL. There is no difference. As I see it, none of this matters whether it be a DBF or SQL application. The principles of data integrity don't change. Nor do the mechanisms for storage. Write to SQL in exactly the same way you would for DBF. Geoff
From: Geoff Schaller on 21 Oct 2007 07:28 I'm not a rugby follower of any note - it is considered a minor sport down this neck of the woods <g> but I did note our boys coming home, tail between their legs. The general feeling is that they didn't perform as well as expected. They seem to get paid too well and so over-enjoy themselves when not on the park. Having said that, New Zealanders have gone into national self-flagellation after their defeat. They consider their loss a disgrace and are beating themselves up accordingly <g>. ....just can't take sport that seriously <sigh> "johan.nel(a)xsinet.co.za" <johan.nel(a)xsinet.co.za> wrote in message news:1192965082.363980.118810(a)v23g2000prn.googlegroups.com: > < celebration off > > < poker face on > > Ok sorry won't mention the weekend's rugby > < poker face off > > < celebration on > > > Did we not beat them 36-0 a couple of weeks ago? > > <VBG> > > Johan
From: johan.nel on 21 Oct 2007 08:39
Geoff, > Perhaps you aren't giving our suggestion enough attention? I really do give your ideas attention, but if I can see it not going to work, should I just keep quiet? > think that similar issues can't arise with factories allocating > bar codes or serial numbers to manufactured output? Number > duplication is a daily toil. Your logs don't have a monopoly on > this <g>. Yes I fully agree with you on this one, logs don't have a monopoly, except maybe that its outside and not in a roofed structure that these logs are found with other safety issues not found in a typical factory. > You seem to want it both ways. Nope I only want to ensure logtags are not duplicated in the system. Consider this, an operator fill in the start number on the next sheet when he start with the previous form (TagNo + 50). However while recording, a tag is missed from the batch (dropped between stacked logs, etc), but "forget " to correct the starting tagno on his next sheet. 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. It could happen that Form X + 1 gets captured before X, however when Form X is captured and saved, an error is raised when Log50 is saved to the database (same tagno as Log01 on From X + 1). Which comes to 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. > Either the tags are unique and that is sacrosanct or duplications Yes they are unique but we working with humans here and human errors do occur. The above example should explain the scenario. > So how do you solve your numbering problem? By having the TagNo as a unique field in the database. > What ever you do in the DBF world you also do in the SQL. 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 advantage that your database integrity stay in place even if the user uses a "DBU style" tool to fiddle with the data outside of your application... > > Its about easier management of data integrity in SQL. > There is no difference. As I see it, none of this matters whether > a DBF or SQL application. 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. > The principles of data integrity don't change. Agree 110%, however its easier to implement on SQL than in DBF. Specifically for users that knows how to use DBU style tools, which you can't really enforce on a DBF based database. > Nor do the mechanisms for storage. Write to SQL in exactly the > same way you would for DBF. To some extend yes, however we do have: if lSuccess commit() else rollback() endif Which needs a lot more code to do on a DBF. Johan. |