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 04:22 Hi Pierre, Thanks for your answer. I will study it in detail and see where it can help. Maybe read my response to Geoff, to see where I am coming from on this topic. Regards, Johan Nel Pretoria, Rugby World Cup country.... <VBG>
From: Michael Haensch on 21 Oct 2007 05:50 Hallo Johan, as I understand your problem, I would do it this way: 1. parent table: primary key on KeyFld 2. child table: - NO (unique) key on ChildFld, because you said it could be duplicated and the user can alter its value - as Geoff said: foreign key on KeyFld to the parent table - new field ID autoincrement, as primary key for the child table 3. - Read the whole data in an arrayserver - show and modify the data with an bBrowser - before save, test the data for duplicated or missing ChildFld - save the data from the arrayserver to the sql table Michael johan.nel(a)xsinet.co.za schrieb: > Lol, > > Geoff maybe I should explain exactly why I am doing things the way it > is done....: > > The table is part of a timber sales monitoring system. > > KeyFld is the Form No relating to a form with log data captured in > field, each Form can contain 50 logs, each log gets a unique tag > (ChildFld) related to a bar coded/plastic tag with a number on it. > Numbers on tags "normally" runs in sequence. Therefor the PK (KeyFld + > ChildFld) and unique ChildFld relating to tag numbers. > > However it sometimes happen that field personnel "miss a tag" and the > sequence in tag numbers is broken, which is trapped when duplicate Tag > numbers are entered into the system, hence the "processd flag", which > is used to indicate field personnel can go back and verify the correct > tag numbers, etc. Therefor ChildFld can be modified on a number of > forms to correct the errors. > > Does this help to make it more clear relating to my question? > > Johan > PS: So let me rephrase the question. > Can I get some ideas about how to go about doing this in SQL as the > DBF way is working fine, however the system is really getting too > large and complex for DBF. >
From: Geoff Schaller on 21 Oct 2007 06:30 Joahn. Michael and I are in complete agreement. Doesn't that suggest something? <g> > KeyFld is the Form No relating to a form with log data captured in > field, each Form can contain 50 logs, each log gets a unique tag > (ChildFld) related to a bar coded/plastic tag with a number on it. Precisely! That is why our suggestion is appropriate. > numbers are entered into the system, hence the "processd flag", which > is used to indicate field personnel can go back and verify the correct > tag numbers, etc. Therefor ChildFld can be modified on a number of > forms to correct the errors. But this doesn't change the nature of data storage. You need to modify the physical tag to match the computer record. The processed flag thus has little relevance. All logs are essentially processed. > PS: So let me rephrase the question. > Can I get some ideas about how to go about doing this in SQL as the > DBF way is working fine, however the system is really getting too > large and complex for DBF. But you haven't said why "sql" should be any different. If the process works then it works. Michael and I would probably inject additional control measures (by way of PK and FK) but none of this means you need to change your process methodology. If you don't like our suggestion then go on doing it your way. I think you are doing this the hard way in DBF but please don't let us dissuade you. You can equally do the hard way in SQL too <g>. Geoff
From: Geoff Schaller on 21 Oct 2007 06:31 Shhhh... <whisper on> Don't mention rugby to the Poms... <whisper off> > Johan Nel > Pretoria, Rugby World Cup country.... <VBG>
From: johan.nel on 21 Oct 2007 06:53
Hi Michael, > 2. child table: > - NO (unique) key on ChildFld, because you said it could be > duplicated and the user can alter its value Yes it gets duplicated due to errors on forms coming back from the field but it should not actually in principle happen, but due to human error it does happen and then should be rectified. Therefor I need to check that values of ChildFld are unique. > - as Geoff said: foreign key on KeyFld to the parent table > - new field ID autoincrement, as primary key for the child table > > 3. > - Read the whole data in an arrayserver > - show and modify the data with an bBrowser > - before save, test the data for duplicated or missing ChildFld > - save the data from the arrayserver to the sql table Don't know if ArrayServer will do the trick though, as I would in any case have to check against the database that Tag numbers are unique, which at least I can leave to the RDBMS to do for me and only respond when there is a problem. Speed is the problem I need to consider though. Can be more than 1000 forms per day that gets in from the field in one company so not sure if bBrowser is the way to go. The form is working correctly and users are happy with the layout and speed. See the form as something like order with order details, except the maximum number of order details is 50 and there is no lookup for OrderItemType, but its generated by the software. The form consists of a header and body part. Header for the parent table details, body for the child details. For each detail record there is a Tag01-50 (ChildFld in server), Diameter01-50 and Length01-50 control on the form all inheriting from Willie's enterSLE as all data are captured on the Numeric keypad with the enter key used to jump to next control. The window however only ask for the first tag number on the form and as the user update/add/delete records (Diameter and Length), it is incremented automatically. When the last line is entered (Diameter50/ Length50) or 0 if there are less than 50 logs on the form, focus goes to the tag number controls (Tag02) and if there is a break in the sequence it can be changed by the user, from where the system uses that new tag number to increment subsequent Tag## controls as long as the sequence pattern is found. Problem is therefor not about the form and which controls to use, its about writing the form back to the database in the most efficient way in SQL and making sure there are no duplicate tags. Hope I provided enough insight on my real issues. Regards, Johan. |