How can I fetch the last row I inserted using DBI?

Question

How can I fetch the last row that was inserted using DBI (DBD::mysql)?

Code sample:

my $sth = $dbh->prepare('INSERT INTO a ( x, y, z ) VALUES ( ?, ?, ? )');
$sth->execute( $x, $y, $z );

How can I get access to the data that was inserted by the above prepare statement? I need to get the primary ID (AUTOINCREMENT) value.

UPDATE:

From DBD::mysql documentation:

An alternative way for accessing this attribute is via $dbh->{'mysql_insertid'}.

Thank you Manni and n0rd for your answers. :-)

score 35 · Accepted Answer · answered Dec 15 '09 at 11:48

35

This is a property of the statement handle. You should be able to access the ID like that:

$sth->{mysql_insertid}

answered Dec 15 '09 at 11:48

innaM

47,505
4
67
87

13

`$dbh->{mysql_insertid}` returns the same. – Eugene Yarmash Mar 29 '13 at 12:48
Couldn't you get a different answer, if there was an insert between your insert and this call? – Sam Selikoff Sep 09 '13 at 18:57
Of course. Your supposed to query `$sth->{ mysql_insertid }` immediately after the INSERT statement your interested in. – innaM Sep 10 '13 at 12:51
Rephrasing something that was said below. . . I believe what Sam is asking is whether the window between inserting and retrieving could have an intervening insert from an outside process. Yes it could, but the mysql_insertid (and don't try this on oracle) is the last insert that *your session* executed, so no worries there. – Vic K Nov 20 '19 at 13:58

Graham Miln · Answer 2 · 2016-06-13T09:27:24.690

34

A database agnostic approach is to use the DBI's last_insert_id method. This approach helps to reduce dependency on a specific database:

$dbh->last_insert_id

$rv = $dbh->last_insert_id($catalog, $schema, $table, $field);

Returns a value 'identifying' the row just inserted, if possible. Typically this would be a value assigned by the database server to a column with an auto_increment or serial type. Returns undef if the driver does not support the method or can't determine the value.

edited Jun 13 '16 at 09:27

answered Sep 23 '14 at 14:04

Graham Miln

2,724
3
33
33

Some DBD drivers work with just `$dbh->last_insert_id()` (e.g., DBD::MariaDB). – rlandster Mar 03 '22 at 22:00

score 6 · Answer 3 · edited Dec 15 '09 at 14:24

6

SELECT LAST_INSERT_ID() query will also return what you want.

edited Dec 15 '09 at 14:24

Alan Haggai Alavi

72,802
19
102
127

answered Dec 15 '09 at 11:51

n0rd

11,850
5
35
56

@Anthony that is incorrect. LAST_INSERT_ID() is related only to *your current* mysql connection and is unaffected by what anybody else are doing on your server. – thelogix Sep 25 '15 at 11:26
Underlying DBI driver maybe not mysql – Eugen Konkov Aug 11 '16 at 17:23

How can I fetch the last row I inserted using DBI?

3 Answers3

$dbh->last_insert_id

Linked

Related