2

In the database the id for this row is automatically generated. If an INSERT is executed in between the execute in this code and the SELECT I will get the wrong id returned. Is there a better alternative to getting the id and guaranteeing it will be from the row I inserted. I understand that the two statements will happen very close together but I would still like to know if there is a better way of doing this.

my $sthH = $dbh->prepare("INSERT INTO hardware (cpu_type, cpu_count, memory) VALUES (?,?,?)");
$resultH = $sthH->execute($cpu_type, $cpu_count, $memory);
$hardware_id = $dbh->selectrow_array("SELECT MAX(hardware_id) FROM hardware;");
taintedromance
  • 51
  • 2
  • 7
  • 1
    Note that the mysql specific command "last insert id" is available in all clients (since it is implemented server side) and is limited to the client connection. That means unless you do not do any additional insert inside the same client you should be safe! – arkascha Jan 19 '14 at 20:02
  • 1
    already answered here: http://stackoverflow.com/questions/13893519/how-can-i-call-a-mysql-function-like-mysql-insert-id-from-perl-if-im-using-db – Steffen Ullrich Jan 19 '14 at 20:11
  • 1
    And.... this is why all databases need to support `INSERT ... RETURNING`. All this session-level stuff is silly. – David-SkyMesh Jan 20 '14 at 02:35

1 Answers1

2

this is how I am doing it, in case you are still having problems

$sth->execute() or $self->error( __PACKAGE__, __LINE__,  
                                 DBI->errstr, DBI->err, 0, 1, 0);   
$sth->finish();
return ($dbh->{mysql_insertid});
Dennis
  • 104
  • 3