>
> Just to confirm that I am using the safest method, this is
> what I have working right now.
>
> My table structure is this:
> CREATE TABLE person
> (
> id integer NOT NULL, --changed to autonumber with Access GUI
> fname varchar(20) NOT NULL,
> mname varchar(20) NULL,
> lname varchar(20) NOT NULL,
> CONSTRAINT person_pk
> PRIMARY KEY(id)
> );
> /* Unfortunately, the data coming in isn't "clean" right now,
> so name is not
> part of the primary key, but may be in the future.
> There is code in my interface that double checks
> with the user before entering duplicate names.
> */
>
> $dbh->connect() with AutoCommit on.
>
> #turn off AutoCommit for the insert
> $dbh->{AutoCommit} = 0;
>
> $sql = "INSERT INTO person....";
> $sth = $dbh->do($sql);
Do doesn't return a statement handle...
> #error check
>
> #insert was good, get the id
> $sql = 'SELECT max(id) FROM person WHERE fname=$inserted_fname, etc';
> #prepare, execute, fetch
I'd recommend pre-preparing and using placeholders...
>
> $dbh->commit;
> $dbh->{AutoCommit} = 1;
I wouldn't do this. I'd explicitly control the committing in your app.
Jeff
>
>
> -----Original Message-----
> From: Jeff Urlwin [mailto:[EMAIL PROTECTED]
> Sent: Friday, March 14, 2003 9:16 AM
> To: 'Paul Boutros'; 'Joshua Caesar'
> Cc: [EMAIL PROTECTED]
> Subject: RE: Retrieving autonumber after insert
>
>
> If you turn off AutoCommit, you can *should* be able to
> select max(id) from the table. I don't know, offhand, if
> Access Supports, say select @@identity (in fact, I believe it
> doesn't).
>
> Be careful: don't commit before trying to select it...
>
> Jeff
> >
> > I'll be interested in hearing answers from "wiser" list
> members, but
> > my feeling was that 99% of the time tables using an
> auto-number also
> > had a unique field elsewhere. So indeed my strategy has
> always been
> > to do exactly what you say. If this alternate unique tuple exists,
> > then you don't even have to worry about race conditions. If you do
> > *not* have any unique set of fields in the table, then I
> suppose you
> > are stuck relying on hoping to avoid race conditions.
> >
> > Just my two cents.
> > Paul
> >
> > > -----Original Message-----
> > > From: Joshua Caesar [mailto:[EMAIL PROTECTED]
> > > Sent: Friday, March 14, 2003 1:40 AM
> > > To: [EMAIL PROTECTED]
> > > Subject: Retrieving autonumber after insert
> > >
> > >
> > > Is there a general DBI call , or DBD::ODBC call, to retrieve the
> > > autonumber generated after an insert ? Or is my best bet
> to do the
> > > insert, then do a select for the id, using the inserted
> values in a
> > > where clause, and depend on the fact that there hopefully
> > shouldn't be
> > > race conditions on an app that will be for one user only
> > 99% of it's
> > > life.
> > >
> > > I am 150 miles away from most of my documentation, so
> > please excuse me
> > > if this is something easily looked up. I am using Perl
> > 5.6.1 built for
> > > cygwin-multi, DBI 1.30, and DBD::ODBC 1.01 against an Access 2000
> > > database.
> > >
> > > Thanks in advance,
> > >
> > > Josh Caesar
> > > [EMAIL PROTECTED]
> > >
> >
> >
>
>
>
>
