Getting null value from last_insert_id()

[chiragforever]

Here is the actual code which is working fine,but the problem is this code is not working on all the systems.I am using "mysql - 5.0.27". Issue:Value of last insert id comes out Null for some of the system. Assumptions: Database Name:"testDatabase" userName:root Password:root123 TablerName:drmProfile ################## use DBI; $connectionInfo="dbi:mysql:testDatabase:localhost"; $dbh = DBI->connect($connectionInfo,root,root123) or die "$dbh::errstr\n"; die "Could not connect to mysql database..\n" if (!defined($dbh)); my $tmp_query="insert into drmProfile (iddrmProfile,profileID) values ('10','309');"; $sth = $dbh->prepare($tmp_query); $sth->execute(); $sth->finish(); my $out = $dbh->last_insert_id(undef, undef, "drmProfile", undef); print "Last Insert ID is :$out\n"; Issue:Value of last insert id comes out Null for some of the system. ################### Hi,

I am using DBI(1.54) perl module to update the mysql database,i am using "last_insert_id" to get the last inseted value,but it is giving me "null" value.
Intresting thing here is ,it is system specific.On some system("linux") it is working fine.I think it has something to do with DBI version or drives,but could not find the solution.

What i am doing:
---------------
1)Inserting a row in table.

my $tmp_query="insert into <TableName> values('','');";
$sth = $dbh->prepare($tmp_query);
$sth->execute();
$sth->finish();

2)And trynig to get last inseted id

my $out = $dbh->last_insert_id(undef, undef, "<Table Name>", undef);

"$out" value is null.

Please help me out if you have any information on this.

Regards,
Chirag

[ferreira]

If that:

$tmp_query="insert into <TableName> values('','');";
$sth = $dbh->prepare($tmp_query); 
$sth->execute(); 
$sth->finish();

looks like your actual code, you have some problems to work out before getting something from last_insert_id().

First, DBI drivers don't expect ';' ending the statements most of the time (even though I don't know especifically about mysql). I would suggest to remove this terminator.

Second, if you are not using RaiseError => 1, every problem/error is passing unnoticed if you don't check the return of DBI methods like prepare, execute and finish. One of the easiest ways is to add RaiseError => 1 to your connection creation and let DBI throw an exception if something goes wrong.

After getting rid of these issues, you may try with more confidence to interpret the results of last_insert_id() (and it is possible it just works then and does not need more tweaks). Also consider Re: Getting null value from last_insert_id() and make sure you need last_insert_id after all.

[Krambambuli]

You always should check for errors, maybe the insertion just doesn't happen for some reason and that's why there is no last_insert_id.

Depending on how you open/manage your DB connection, you might want either to raise an exception on error that you would trap within an eval {}; block, or explicitely check the result/error code.

Have a look into DBD::mysql, maybe it helps a bit.

Depending on the chosen way to deal with errors, your code probably should look like


my $tmp_query="insert into <TableName> values('','');"; 
eval {
  $sth = $dbh->prepare($tmp_query); 
  $sth->execute();
  $sth->finish();
};
if ($@) { # error...
}

or

  $sth = $dbh->prepare($tmp_query); 
  if (!$sth) {
     die "Error:" . $dbh->errstr . "\n";
  }
  if (!$sth->execute) {
     die "Error:" . $sth->errstr . "\n";
  }
  ...

[robot_tourist]

Have you looked up the docs? I think you almost answered your own question. Tell the monks the specifics of the databases (not the OS) you are using for more help. I can't help you further, but I'm sure there are some monks here who could.

[chiragforever]

Hi,

I got the solution for this problem.
Only thing that we need to do is install DBD::mysql module.
last_insert_id function does not work if this module is not
installed.
I am wondering while installing DBI it does not ask to install DBD::my
+sql .