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);
#error check
#insert was good, get the id
$sql = 'SELECT max(id) FROM person WHERE fname=$inserted_fname, etc';
#prepare, execute, fetch
$dbh->commit;
$dbh->{AutoCommit} = 1;
----------------------------------------------
Thanks for your help.
-Josh
-----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]
> >
>
>
