DBD::mysql again: last_insert_ids

[william ross]

hello,

Having somehow managed to install DBD::mysql on 10.3 without running 
into the obstacles that others have described here - which is always 
worrying - I now find that it  doesn't quite work. most functions are 
fine, but it fails to get a correct last_insert_id: instead it returns 
zero, the value for a failure-to-insert.

You can still get the correct value directly, which is odd:

print $dbh->{mysql_insertid};                # 0
print $dbh->last_insert_id();                # undef
print $dbh->do("SELECT LAST_INSERT_ID()");   # 11, as it happens
This thoroughly breaks any application based on Class::DBI and I am, as 
usual, baffled. can anyone suggest a way to move forward?

thanks.

will

ps. this is with DBD::mysql 2.0419, the version you get by installing 
the Msql-mysql-bundle, and I'm still using the standard threaded perl 
5.8.1rc3.

Here's a test script in case someone is kind enough to show that the 
problem is confined to my own busted-up machine:



#!/usr/bin/perl

use strict;
use DBI;
my ($dbh, @row);

eval{
	$dbh = DBI->connect('DBI:mysql:database=dbitest;host=localhost', 
'someone', 'something', {'RaiseError' => 1});
};
die "\nconnection failed: $@" if ($@);

print "*** connected to database.\n";
print "*** inserting row.\n";
my $sth2 = $dbh->prepare("INSERT into somethings (title) VALUES (?)");
$sth2->execute('testy');
print "*** mysql_insertid variable is: " , $dbh->{mysql_insertid} . 
"\n";
print "*** last insert id method gives: " . $dbh->last_insert_id . "\n";
print "*** SELECT LAST_INSERT_ID() gives: ";

print $dbh->do("SELECT LAST_INSERT_ID()") . "\n";

print "*** table contents:\n";

my $sth3 = $dbh->prepare("SELECT * FROM somethings where title = ?");
$sth3->execute('testy');
while ( @row = $sth3->fetchrow_array ) {
        print join(' = ', @row) . "\n";
}
print "*** mysql_insertid variable is: " , $dbh->{mysql_insertid} . 
"\n";



__END__

# in mysql:

create database dbitest;
grant all on dbitest.* to [EMAIL PROTECTED] identified by 'something';
use dbitest;
create table somethings (
        id int not null auto_increment,
        title varchar(255),
        primary key (id)
);