DBI & primary keys

[Bigus]

I'm inserting a new record into a MySQL table using Perl DBI. I need to
retrieve the unique primary key that's created automatically by the db as
the new record is inserted. Since this will be a multi-user application, and
two different users could be creating new records in that db within a
split-second of each other, I'm not sure whether performing a subsequent
SELECT statement to find out the max number in that column will be
water-tight enough. I would have thought it must be a fairly common desire
to want to know the primary key for a newly inserted record, but can't find
any mention of it in the DBI instructions. Any ideas?

Thanks
Bigus

 

[Gregory Toomey]

It was a dark and stormy night, and Bigus managed to scribble:

I'm inserting a new record into a MySQL table using Perl DBI. I need to
retrieve the unique primary key that's created automatically by the db as
the new record is inserted. Since this will be a multi-user application,
and two different users could be creating new records in that db within a
split-second of each other, I'm not sure whether performing a subsequent
SELECT statement to find out the max number in that column will be
water-tight enough. I would have thought it must be a fairly common desire
to want to know the primary key for a newly inserted record, but can't
find any mention of it in the DBI instructions. Any ideas?

Thanks
Bigus

See http://www.mysql.com/doc/en/example-AUTO_INCREMENT.htm
"You can retrieve the most recent AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function"

gtoomey

 

[Tore Aursand]

I'm inserting a new record into a MySQL table using Perl DBI. I need to
retrieve the unique primary key that's created automatically by the db as
the new record is inserted.

You could try this approach:

# do the insert
my $id = $stInsert->{'mysql_insertid'};

As you can see, this approach is very MySQL'ish. If you are 110% sure
that your application always will make use of MySQL, you could stick with
this one.

A better solution must be to use transactions, but I'm not quite updated
on the transaction support in MySQL. You could of course give PostgreSQL
a try, which is a fairly more "mature" RDBMS.

In the early days I created an application which would run on a set of
different database systems. The solution was to use an extra field in
every table as a lookup field (see above). Surely not a clever way to do
it, but it worked very well.

my $using_mysql = ( $Config->{'DB_TYPE'} eq 'mysql' ) ? 1 : 0;
my $temp_id = ( $using_mysql ) ? $Misc->random_number() : 0;
my $insert_id = 0;

my $stInsert = $dbh->prepare('INSERT INTO user (..., temp_id)
VALUES (..., ?)' );
$stInsert->execute( $temp_id );
$stInsert->finish();

if ( $using_mysql ) {
$insert_id = $stInsert->{'mysql_insertid'};
}
else {
my $stLookup = $dbh->prepare('SELECT user_id
FROM user
WHERE temp_id = ?');
$stLookup->execute( $temp_id );
( $insert_id ) = $stLookup->fetchrow();
$stLookup->finish();

my $stRemoveTempID = $dbh->prepare('UPDATE user
SET temp_id = 0
WHERE user_id = ?');
$stRemoveTempID->execute( $insert_id );
$stRemoveTempID->finish();
}

Quite hairy, huh?

 

[Eric J. Roode]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm inserting a new record into a MySQL table using Perl DBI. I need
to retrieve the unique primary key that's created automatically by the
db as the new record is inserted. Since this will be a multi-user
application, and two different users could be creating new records in
that db within a split-second of each other, I'm not sure whether
performing a subsequent SELECT statement to find out the max number in
that column will be water-tight enough. I would have thought it must
be a fairly common desire to want to know the primary key for a newly
inserted record, but can't find any mention of it in the DBI
instructions. Any ideas?

MySQL provides a built-in function to achieve this: LAST_INSERT_ID. See
http://www.mysql.com/doc/en/Miscellaneous_functions.html#IDX1412

The server maintains this value on a per-connection basis, so you simply do
not have to worry about other concurrent inserts on the table.

Yay MySQL.

- --
Eric
$_ = reverse sort $ /. r , qw p ekca lre uJ reh
ts p , map $ _. $ " , qw e p h tona e and print

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 7.0.3 for non-commercial use <http://www.pgp.com>

iQA/AwUBP5XaFmPeouIeTNHoEQIc9QCcCR3FxynfHYmXQ9iJ/doymg5o4/0AmwUZ
2QCGubO5Dtq0G4cH/MXjo309
=Qyvg
-----END PGP SIGNATURE-----

 

[Bigus]

You could try this approach:

# do the insert
my $id = $stInsert->{'mysql_insertid'};

That's work excellently and is easier than using the LAST_INSERT_ID. I'm
likely to stick with mySQL aswell since along with the phpmyadmin web
interface it's nice and easy to use I was gonna ask where
'mysql_insertid' comes from but just found it in the DBD-mysql docs.

In the early days I created an application which would run on a set of
different database systems. The solution was to use an extra field in
every table as a lookup field (see above). Surely not a clever way to do
it, but it worked very well. [..code..]
Quite hairy, huh?

Yes That's what I was kind of considering I might have to do.

Bigus

 

[Tore Aursand]

That's work excellently and is easier than using the LAST_INSERT_ID. I'm
likely to stick with mySQL aswell since along with the phpmyadmin web
interface it's nice and easy to use I was gonna ask where
'mysql_insertid' comes from but just found it in the DBD-mysql docs.

Nice of you to read the documentation; not everyone cares about such
things nowadays.

Anyway: Be careful about your design. Suddenly (...) you have to change
from MySQL to something else, and then you're in deep shit. For me it was
quite easy, as I had a base class for handling database work from which
every other class derived from.

I'm not an RDBMS expert or anything, but I've been playing around with a
few of them for a few years. What strikes me is that there's no universal
method around to get the last auto-increment id from a table? Is it?

 

[Bart Lateur]

I'm inserting a new record into a MySQL table using Perl DBI. I need to
retrieve the unique primary key that's created automatically by the db as
the new record is inserted. Since this will be a multi-user application, and
two different users could be creating new records in that db within a
split-second of each other, I'm not sure whether performing a subsequent
SELECT statement to find out the max number in that column will be
water-tight enough.

For mySQL, look up mysql_insert_id, both in the DBD::mysql man page and
on mySQL.com's site. As you can see, this value is unique per database
connection (= per $dbh). As long as you ask for it in the same session,
and you haven't inserted (or updated) anything in another table in this
session , you're safe.


<http://search.cpan.org/~rudy/DBD-mysql-2.9002/lib/DBD/mysql.pm#DATABASE_HANDLES>

<http://www.mysql.com/doc/en/mysql_insert_id.html>


p.s. Why does almost every reply to every question on MySQL here have to
end up with mySql-bashing?

 

[Tore Aursand]

p.s. Why does almost every reply to every question on MySQL here have to
end up with mySql-bashing?

Doesn't that speak for itself?

 

[Lawrence D¹Oliveiro]

I'm not an RDBMS expert or anything, but I've been playing around with a
few of them for a few years. What strikes me is that there's no universal
method around to get the last auto-increment id from a table? Is it?

I believe SQL is like that. The official standard is missing lots of
functionality that you need to use it in real-world applications. So
every SQL-based product is forced to invent its own ways to deal with
these needs.

ObMySQLNonBash: Not only do I like MySQL, but I also think Perl DBI is a
much superior interface to it than PHP's built-in functions.

Lawrence
once got tripped up by PHP's !@#$% default "magic_quotes_gpc = On"
setting