RE: [SQL] Getting primary key from insert statement - Mailing list pgsql-sql

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
AMTo:   Michael J Davis; pgsql-sql@postgreSQL.orgSubject:    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
writesanother form that accesses the same table, and fails to do so, or
leaves itto 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 hasnot 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 itstill allows entering non-sequence values. But at least it's a step
in theright direction. Putting the logic in the client side is the wrong
thing todo.
To finish in a humorous note, I wouldn't use programming practicesencouraged by Microsoft products as pillars of
truth...
Herouth
--Herouth Maoz, Internet developer.Open University of Israel - Telem projecthttp://telem.openu.ac.il/~herutma