Ummm ... does look like something is broke. This example shows the
same results. But what's strange is the first error is null value,
the next two are duplicate values.
You may have to call the execute with the parameters instead of using
bind_param.
#!/usr/local/bin/perl -w
# vim:ts=4:sw=4:ai:aw:
use strict;
use warnings;
use Carp;
use DBI;
my $dbh = DBI->connect() or
croak "Unable to connect: ", $DBI::errstr;
$dbh->{RaiseError} = 0; $dbh->{PrintError} = 1;
{
local ($dbh->{RaiseError}, $dbh->{PrintError});
$dbh->{RaiseError} = 0; $dbh->{PrintError} = 0;
$dbh->do( q{drop table bind_tst} );
}
$dbh->do( q{create table bind_tst (i int4, c varchar(20), ts timestamp, primary key
(i))} );
my $sth = $dbh->prepare( q{insert into bind_tst (i, c, ts) values ( ?, ?,
current_timestamp )} );
my ($i, $c, $rc);
$sth->bind_param( 1, $i );
$sth->bind_param( 2, $c );
for (1 .. 3) {
$i = $_;
$c = "This is number $i";
$rc += ($sth->execute||0);
carp "Error: ", $sth->errstr, "\n" if ($sth->err);
$rc += $sth->execute( $i, $c );
carp "Error: ", $sth->errstr, "\n" if ($sth->err);
}
print "Inserted $rc\n";
my $sel = $dbh->prepare( q{select * from bind_tst} );
$sel->execute;
DBI::dump_results($sel);
$dbh->disconnect;
exit;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'bind_tst_pkey' for table 'bind_tst'
DBD::Pg::st execute failed: ERROR: ExecAppend: Fail to add null value
in not null attribute i at bind_param.t line 34.
Error: ERROR: ExecAppend: Fail to add null value in not null
attribute i
DBD::Pg::st execute failed: ERROR: Cannot insert a duplicate key into
unique index bind_tst_pkey at bind_param.t line 34.
Error: ERROR: Cannot insert a duplicate key into unique index
bind_tst_pkey
DBD::Pg::st execute failed: ERROR: Cannot insert a duplicate key into
unique index bind_tst_pkey at bind_param.t line 34.
Error: ERROR: Cannot insert a duplicate key into unique index
bind_tst_pkey
Inserted 3
'1', 'This is number 1', '2002-06-09 22:39:39.275607-04'
'2', 'This is number 2', '2002-06-09 22:39:39.330434-04'
'3', 'This is number 3', '2002-06-09 22:39:39.399699-04'
3 rows
Tom
On Sun, Jun 09, 2002 at 09:40:05PM -0400, Bill Kurland wrote:
> Tom,
>
> Yes, well, sloppy of me, but not the problem. I added the code to write
> to
> the mysql database after writing to postgreSQL failed - kind of as a
> reality check.
> Removing the my's from the mysql bindings , or reversing the order of
> the bind_params
> has no effect on bindings to postgreSQL.
>
>
> On Sunday, June 9, 2002, at 07:49 PM, Thomas A. Lowery wrote:
>
> > Bill,
> > Reverse the order of the bind_params and see if it fails on mysql
> > now ;-)
> >
> > > $td_sth->bind_param(1,my $line) ;
> > > $td_sth2->bind_param(1,my $line) ;
> >
> > When you later use $line, which my $line is referred to? The first
> > bind_param (the Pg) never receives a value because of the second my
> > $line.
> >
> > use strict;
> >
> > use warnings;
> >
> >
> > my $line;
> > $line = 'A';
> >
> > my $line;
> >
> > print $line, "\n";
> >
> > [tlowery@stllnx1 tlowery]$ perl -w fred.pl
> > "my" variable $line masks earlier declaration in same
> > scope at fred.pl line 11.
> > Use of uninitialized value in print at fred.pl line 13.
> >
> > use warnings and -w are your friend.
> >
> > Tom
> >
> >
> > On Sun, Jun 09, 2002 at 06:54:07PM -0400, Bill Kurland wrote:
> >> Is there something broken with parameter binding in DBD::Pg or am I
> >> just
> >> missing something? Code
> >> that worked fine with mysql is broken when run against a postgreSQL
> >> database. I pulled out the
> >> relevant code and ran it against both DBMS's - works fine in mysql, but
> >> gives an error against postgreSQL :
> >>
> >> ERROR: ExecAppend: Fail to add null value in not null attribute
> >> line_no
> >>
> >> Any help is greatly appreciated. Thanks
> >>
> >> use DBI ;
> >> use strict;
> >>
> >> my($dsn) = "DBI:Pg:dbname=bbreg;host='host'" ;
> >>
> >> my $bbreg_h = DBI->connect($dsn,'username','password', {
> >> PrintError => 0,
> >> RaiseError => 0
> >> }) ;
> >> unless($bbreg_h){
> >> print "\a\a\a" ;
> >> print DBI->errstr . "\tError Number:" . DBI->err ;
> >> exit;
> >> }
> >>
> >> my($dsn2) = "DBI:mysql:bbreg:host2";
> >>
> >> my $bbreg2_h = DBI->connect($dsn2,'username','password' , {
> >> PrintError => 0,
> >> RaiseError => 0
> >> }) ;
> >>
> >> my $td_sth = $bbreg_h->prepare( "
> >> INSERT INTO trans_dtl
> >> (cashier, store, tran_no, line_no, loc, isbn, author, title, price,
> >> course_no, term, retail )
> >> VALUES ('username', 5, 1,?, ?, ?, ?, ? , ? , ? , ? , ?)
> >> ");
> >>
> >> $td_sth->bind_param(1,my $line) ;
> >> $td_sth->bind_param(2,my $where) ;
> >> $td_sth->bind_param(3,my $isbn) ;
> >> $td_sth->bind_param(4,my $author) ;
> >> $td_sth->bind_param(5,my $title) ;
> >> $td_sth->bind_param(6,my $price) ;
> >> $td_sth->bind_param(7,my $course_no) ;
> >> $td_sth->bind_param(8,my $term) ;
> >> $td_sth->bind_param(9,my $retail) ;
> >>
> >> my $td_sth2 = $bbreg2_h->prepare( "
> >> INSERT INTO trans_dtl
> >> (cashier, store, tran_no, line_no, loc, isbn, author, title, price,
> >> course_no, term, retail )
> >> VALUES ('username', 5, 1,?, ?, ?, ?, ? , ? , ? , ? , ?)
> >> ");
> >>
> >> $td_sth2->bind_param(1,my $line) ;
> >> $td_sth2->bind_param(2,my $where) ;
> >> $td_sth2->bind_param(3,my $isbn) ;
> >> $td_sth2->bind_param(4,my $author) ;
> >> $td_sth2->bind_param(5,my $title) ;
> >> $td_sth2->bind_param(6,my $price) ;
> >> $td_sth2->bind_param(7,my $course_no) ;
> >> $td_sth2->bind_param(8,my $term) ;
> >> $td_sth2->bind_param(9,my $retail) ;
> >>
> >> my @prompts = qw( Location: ISBN: Author: Title: Price: Course: Term:
> >> Retail:) ;
> >> my @history ;
> >> my $cnt = 0;
> >> my $max = shift ;
> >>
> >> while(){
> >> my @line ;
> >> push @line, ($cnt) ;
> >> foreach my $prompt (@prompts){
> >> print "Enter " . $prompt ;
> >> while(<>){
> >> chomp ;
> >> print "$_\n";
> >> push @line, ($_) ;
> >> last;
> >> }
> >> }
> >> unshift @history, \@line ;
> >> $cnt++ ;
> >> last if $cnt > $max ;
> >> }
> >>
> >> foreach my $line_no (@history){
> >> ($line,$where,$isbn,$author,$title,$price,$course_no,$term,$retail) =
> >> @$line_no ;
> >> print join ':',
> >> ($line,$where,$isbn,$author,$title,$price,$course_no,$term,$retail),
> >> "\n" ;
> >> $td_sth->execute() or warn DBI->errstr(), "\n";
> >> $td_sth2->execute() or warn DBI->errstr(), "\n";
> >> }
> >> $bbreg_h->disconnect() ;
> >> $bbreg2_h->disconnect() ;
> >>
> >> The client is running under Windows NT.
> >> Active State Perl version 5.6.0 built for MSWin32-x86-multi-thread
> >> DBI v1.23
> >> DBD::Pg v0.95
> >> DBD::mysql 1.2200
> >>
> >> According to ppm, these are the latest available versions.
--
Thomas A. Lowery
See DBI/FAQ http://xmlproj.dyndns.org/cgi-bin/fom
