[CDBI] DBI's last_insert_id and Class::DBI's _auto_increment_value

David Steinbrunner carbon at pobox.com
Mon Nov 21 20:47:01 GMT 2005


Kingsley Kerce wrote:

> David -- Can you post code for the simplest test case for which you
> receive the "Can't get last insert id" error when running with
> PostgreSQL?

Attached is a simple script that I quickly put together.  I works under
MySQL and under Postgres when CDBI's _auto_increment_value uses
last_insert_id.

To get Postgres to work without modifying CDBI I have the sequence being
defined but that then breaks MySQL.  The sequence lines are currently
commented out in the script so you can test the first case first.

--
David Steinbrunner

-------------- next part --------------
#!/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', } );



More information about the ClassDBI mailing list