Date: Thu, 27 Sep 2007 07:21:29 GMT From: Veselin Slavov <vess@slavof.net> To: freebsd-gnats-submit@FreeBSD.org Subject: ports/116685: Class::DBI-inserting multiple records in posgresql schema faild Message-ID: <200709270721.l8R7LTFC057006@www.freebsd.org> Resent-Message-ID: <200709270730.l8R7U1ub066635@freefall.freebsd.org>
next in thread | raw e-mail | index | archive | help
>Number: 116685
>Category: ports
>Synopsis: Class::DBI-inserting multiple records in posgresql schema faild
>Confidential: no
>Severity: critical
>Priority: high
>Responsible: freebsd-ports-bugs
>State: open
>Quarter:
>Keywords:
>Date-Required:
>Class: sw-bug
>Submitter-Id: current-users
>Arrival-Date: Thu Sep 27 07:30:00 GMT 2007
>Closed-Date:
>Last-Modified:
>Originator: Veselin Slavov
>Release: FreeBSD 5.5-STABLE i386
>Organization:
>Environment:
FreeBSD server.slavof.net 5.5-STABLE FreeBSD 5.5-STABLE #3: Tue Apr 3 11:25:33 EEST 2007 root@server.slavof.net:/usr/obj/usr/src/sys/server i386
>Description:
PostgreSQL DB have shema 'public' and new one 'new_schema'.
Package TestDBI use base 'Class::DBI' have __PACKAGE__->table('new_schema.table_name');
When try:
for($i;i<=10;$i++){ __PACKAGE__->insert({ data => 1 })}
Error:
Can't insert new TestDBI: DBD::Pg::db last_insert_id failed: Could not find the table "new_shema.table1" [for Statement "SELECT c.oid FROM pg_catalog.pg_class c
WHERE relname = ?"] at /usr/local/lib/perl5/site_perl/5.8.8/Class/DBI.pm line 609.
>How-To-Repeat:
Run this script please.
#!/usr/bin/perl -w
use strict;
package MYDBI;
use base 'Class::DBI';
__PACKAGE__->connection('dbi:Pg:database=testdbi', 'pgsql','', { AutoCommit => 1 });
1;
package TestDBI;
use base 'MYDBI';
__PACKAGE__->table( 'test.table1' );
__PACKAGE__->columns( Primary => 'id');
__PACKAGE__->columns( All => qw( data date));
1;
package MAIN;
use DBI;
my $dbh = DBI->connect("dbi:Pg:database=postgres", 'pgsql');
$dbh->do("create database testdbi");
$dbh->disconnect;
$dbh = DBI->connect("dbi:Pg:database=testdbi", 'pgsql');
$dbh->do("create schema test");
my $sql=<<_SQL;
create table test.table1 (
id serial primary key,
data integer,
date timestamp default current_timestamp
);
_SQL
$dbh->do( $sql );
$dbh->disconnect;
for( my $i=1; $i <= 10; $i++ ){
TestDBI->insert({ data => $i });
}
my $records = TestDBI->retrieve_all;
while( my $r = $records->next ){
printf "[%s][%s]\n",$r->data,$r->date;
}
>Fix:
Apply attached diff file.
Patch attached with submission follows:
--- DBI.pm.orig Wed Sep 26 15:51:33 2007
+++ DBI.pm Wed Sep 26 16:06:04 2007
@@ -599,12 +599,25 @@
my $self = shift;
my $dbh = $self->db_Main;
+ # In PostgreSQL when using more than 1 schema and
+ # $self->table =~ 'schema_name.table_name'
+ # geting id failed. So must separete $self->table to
+ # to 2 components - schema and table name
+ my $table = $self->table;
+ my $schema;
+ if ( $table =~ /^(\w+)\.(\w+)$/ )
+ {
+ $schema = $1;
+ $table = $2;
+ }
+
# Try to do this in a standard method. Fall back to MySQL/SQLite
# specific versions. TODO remove these when last_insert_id is more
# widespread.
# Note: I don't believe the last_insert_id can be zero. We need to
# switch to defined() checks if it can.
- my $id = $dbh->last_insert_id(undef, undef, $self->table, undef) # std
+ #my $id = $dbh->last_insert_id(undef, undef, $self->table, undef) # std
+ my $id = $dbh->last_insert_id(undef, $schema, $table, undef) # std
|| $dbh->{mysql_insertid} # mysql
|| eval { $dbh->func('last_insert_rowid') }
or $self->_croak("Can't get last insert id");
>Release-Note:
>Audit-Trail:
>Unformatted:
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?200709270721.l8R7LTFC057006>