Received: from corppo1.uvsg.com (corppo1.uvsg.com [192.40.65.90]) by hub.org (8.9.3/8.9.3) with ESMTP id NAA01531 for ; Tue, 8 Jun 1999 13:27:33 -0400 (EDT) (envelope-from michael.j.davis@tvguide.com) Received: from prevuepo1.uvsg.com ([192.40.65.44]) by corppo1.uvsg.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2448.0) id L4BC4VVQ; Tue, 8 Jun 1999 12:27:29 -0500 Received: by prevuepo1.uvsg.com with Internet Mail Service (5.5.2448.0) id ; Tue, 8 Jun 1999 12:30:25 -0500 Message-ID: <93C04F1F5173D211A27900105AA8FCFC14558C@lambic.prevuenet.com> From: Michael J Davis To: pgsql-sql@postgreSQL.org Subject: RE: [SQL] Getting primary key from insert statement Date: Tue, 8 Jun 1999 12:28:11 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2448.0) Content-Type: text/plain; charset="iso-8859-1" I agree. But, in practice Access97 does a very poor (let me rephrase this, completely unacceptable) job of handling inserts within forms if the primary key is not included in the insert statement. In all the discussion about what is the proper way to this, I have not yet seen an example or suggestion that "works" in all cases with Access97. I'll bet that many of the other third party GUI interfaces work pretty much the same way as Access97 (insert a record and the re-select the record using the values provided in the insert statement). If the application does not re-select the record then updates resulting from triggers will not be shown. Most of these applications are not smart enough to ask for the primary key of the newly inserted record and then re-select the record based on this key. This is because the technique for getting the primary is different for each database if it is even available. I am sorry you (and others) don't care for this approach, but so far, it is the only approach that works. As for Microsoft, I dislike (okay, I even hate some of them) many of the choices they make and I prefer to use other products when they are available. But for the moment Access97, even with its inherent draw backs, is one of the best database front ends on the market for the money. I really want to start using a real database for the backend. I have researched many options and feel there are really only two choices worth considering, MS SQL Server or PostgreSQL. I have chosen PostgreSQL. Please don't make me regret it :-). Like or not, I am stuck with Access97 and would really like to use PostgreSQL as the back end. So, we have two choices, find solutions that work with Access97 or continue to hash out what is the "best" way to approach these types of situations. I am willing to do the later only as long it leads to better solutions. Is it reasonable for me to ask others to provide viable alternatives rather just complaining about the approaches I have chosen to take :-)? I also think it is reasonable to expect programmers to know how to properly interact with the database. I.e. they should know or be trained on the best way to insert records into the database. -----Original Message----- From: Herouth Maoz [SMTP:herouth@oumail.openu.ac.il] Sent: Tuesday, June 08, 1999 10:40 AM To: Michael J Davis; pgsql-sql@postgreSQL.org Subject: RE: [SQL] Getting primary key from insert statement At 19:23 +0300 on 08/06/1999, Michael J Davis wrote: > I accomplish this by trapping the "Before Insert" event in the form used to > add new Status records (the user is NOT entering the StatusID) and doing the > following: > > Form.StatusID = getSeqValue('Status_seq') Thus, you do it in the frontend. Six months later, a programmer writes another form that accesses the same table, and fails to do so, or leaves it to the user. Or he decides that he has a better number generator to use. There are errors. Perhaps his form generates numbers which the sequence has not yet reached. Conflicts ocuur. Boss unhappy. That happens because you failed to centralize the logic of your database. As I said, the "serial" datatype is not a complete fail-safe, because it still allows entering non-sequence values. But at least it's a step in the right direction. Putting the logic in the client side is the wrong thing to do. To finish in a humorous note, I wouldn't use programming practices encouraged by Microsoft products as pillars of truth... Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma