This queue is for tickets about the Class-DBI CPAN distribution.

Report information
The Basics
Id:
16520
Status:
resolved
Priority:
Low/Low
Queue:

People
Owner:
Nobody in particular
Requestors:
carbon [...] pobox.com
Cc:
AdminCc:

BugTracker
Severity:
Important
Broken in:
(no value)
Fixed in:
(no value)



Subject: DBI's last_insert_id and Class::DBI's _auto_increment_value
This issue has been discussed on the CDBI archived here: http://www.spanner.org/lists/cdbi/2005/11/21/94e15371.html Super Short Summary: Applying attached patch will add usage of DBI's last_insert_id while still running previous code for backward compatibility with older versions of DBI and DBDs.
From: carbon@pobox.com
Show quoted text
> Applying attached patch
The attachment did not seem to take. Trying again.
574,577c574,583 < < # the DBI will provide a standard attribute soon, meanwhile... < my $id = $dbh->{mysql_insertid} # mysql < || eval { $dbh->func('last_insert_rowid') }; # SQLite --- > > # DBI's standard attribute for auto_increment_value / last_insert_id > my $id = $dbh->last_insert_id(undef, undef, $self->table, undef ); > > # If last_insert_id fails lets get less abstract > unless (defined $id) { > $id = $dbh->{mysql_insertid} # mysql > || eval { $dbh->func('last_insert_rowid') }; # SQLite > } >
From: carbon@pobox.com
[guest - Thu Dec 15 14:16:03 2005]:
Show quoted text
> This issue has been discussed on the CDBI archived here: > http://www.spanner.org/lists/cdbi/2005/11/21/94e15371.html
Also, attaching test case script referred to in CDBI archive.
#!/usr/bin/perl use strict; package DB; use base 'Class::DBI'; # MySQL #my $dbd = 'mysql'; #my $dbuser = 'root'; #my $dbpass = 'pass'; # Postgres my $dbd = 'Pg'; my $dbuser = 'postgres'; my $dbpass = 'pass'; my $dbname = 't'; my $dbhost = '127.0.0.1'; my $dsn = 'dbi:' . $dbd . ':dbname=' . $dbname . ';host=' . $dbhost; __PACKAGE__->connection( $dsn, $dbuser, $dbpass, { PrintError => 0, RaiseError => 1, ChopBlanks => 1, ShowErrorStatement => 1, AutoCommit => 1, } ); package DB::Table1; use base 'DB'; __PACKAGE__->table('table1'); __PACKAGE__->columns( All => qw/id name/ ); #__PACKAGE__->sequence( 'public.table1_id_seq' ); __PACKAGE__->has_many( table2 => 'DB::Table2' ); sub create_table { my $self = shift; my $dbh = $self->db_Main(); my $table = <<TABLE; CREATE TABLE table1 ( id SERIAL NOT NULL, name CHAR(12) NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); TABLE $dbh->do($table); } package DB::Table2; use base 'DB'; __PACKAGE__->table('table2'); __PACKAGE__->columns( All => qw/id ref_id name/ ); #__PACKAGE__->sequence( 'public.table2_id_seq' ); __PACKAGE__->has_a( ref_id => 'DB::Table1' ); sub create_table { my $self = shift; my $dbh = $self->db_Main(); my $table = <<TABLE; CREATE TABLE table2 ( id SERIAL NOT NULL, ref_id INT NOT NULL REFERENCES table1(id), name CHAR(12) NOT NULL, PRIMARY KEY (id) ); TABLE $dbh->do($table); } package main; DB::Table1->new(); DB::Table2->new(); DB::Table1->create_table; DB::Table2->create_table; my $table1Rec = DB::Table1->insert( { name => 'table1Rec', } ); my $table2Rec = DB::Table2->insert( { ref_id => $table1Rec->id, name => 'table2Rec', } );
From: carbon@pobox.com
[guest - Thu Dec 15 14:43:15 2005]:
Show quoted text
> > Applying attached patch
> > The attachment did not seem to take. Trying again.
Resubmitting patch in "diff -Bub" format. This patch is against Class-DBI-v3.0.12.
--- /Users/carbon/Desktop/Class-DBI-v3.0.12/lib/Class/DBI.pm 2005-11-04 04:22:56.000000000 -0500 +++ /Library/Perl/5.8.6/Class/DBI.pm 2005-12-15 14:01:27.000000000 -0500 @@ -572,9 +572,15 @@ my $self = shift; my $dbh = $self->db_Main; - # the DBI will provide a standard attribute soon, meanwhile... - my $id = $dbh->{mysql_insertid} # mysql + # DBI's standard attribute for auto_increment_value / last_insert_id + my $id = $dbh->last_insert_id(undef, undef, $self->table, undef ); + + # If last_insert_id fails lets get less abstract + unless (defined $id) { + $id = $dbh->{mysql_insertid} # mysql || eval { $dbh->func('last_insert_rowid') }; # SQLite + } + $self->_croak("Can't get last insert id") unless defined $id; return $id; }
Show quoted text
> Applying attached patch will add usage of DBI's last_insert_id while > still running previous code for backward compatibility with older > versions of DBI and DBDs.
Thanks, applied. New version on its way to CPAN now. I'm assuming that last_insert_id can't return 0, so just did this as an OR chain. I'm pretty sure that's true on MySQL, but not so sure for other databases. If it can, then I need to put back the defined check. Tony


This service runs on Request Tracker, is sponsored by The Perl Foundation, and maintained by Best Practical Solutions.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.