chiragforever has asked for the wisdom of the Perl Monks concerning the following question:

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();

[download]
2)And trynig to get last inseted id 
my $out = $dbh->last_insert_id(undef, undef, "<Table Name>", undef);

[download]
"$out" value is null.

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

Regards,
Chirag

Replies are listed 'Best First'.
Re: Getting null value from last_insert_id()
by ferreira (Chaplain) on Apr 05, 2007 at 11:09 UTC
    If that: 
    $tmp_query="insert into <TableName> values('','');";
$sth = $dbh->prepare($tmp_query); 
$sth->execute(); 
$sth->finish();

    [download]
    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.

[reply]
[d/l]
[select]
Re: Getting null value from last_insert_id()
by Krambambuli (Curate) on Apr 05, 2007 at 11:35 UTC
    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...
}

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

    [download]
[reply]
[d/l]
[select]
Re: Getting null value from last_insert_id()
by robot_tourist (Hermit) on Apr 05, 2007 at 12:08 UTC

    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.

    How can you feel when you're made of steel? I am made of steel. I am the Robot Tourist.
    Robot Tourist, by Ten Benson

[reply]
      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 .

      [download]
[reply]
[d/l]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom