perl - Number of rows affected after insert or ignore into -

perl - Number of rows affected after insert or ignore into -

the perl code gives me number of rows affected 1 though record gets ignored sql statement because of primary key constraint while inserting sqlite database using dbi module.

my $stmt = 'insert or ignore table1 (id, name) values (?,?)'; $sth = $dbh->prepare($stmt); $sth->bind_param( 1, $id, sql_varchar ); $sth->bind_param( 2, $name, sql_varchar ); $row = $sth->execute() or die $dbi::errstr; print "row affected id[$id]- $row\n";

it gives me $row 1 below set of info id & name composite primary key-

id name 1 aaa 1 bbb 1 aaa 2 bbb 2 bbb

anything wrong doing here. need execute section of code based on value 0f $row 0 in case of ignore , 1 in case of insertion.

is there other way handle it?

use strict; utilize warnings; utilize 5.016; utilize dbi; $h = dbi->connect("dbi:sqlite:db=x.db"); eval { $h->do(q/drop table test/); }; $h->do(q/create table test (id integer, name varchar(10), constraint xx unique(id,name))/); $s = $h->prepare(q/insert or ignore test values(?,?)/); @values = ([1, 'aaa'], [1, 'bbb'], [1, 'aaa'], [2, 'bbb'], [2, 'bbb']); foreach (@values) { $affected = $s->execute(@{$_}); join(",", @{$_}) . " = $affected"; } $h->disconnect;

outputs

1,aaa = 1 1,bbb = 1 1,aaa = 0e0 2,bbb = 1 2,bbb = 0e0

for me looks correct. using dbi 1.631 , dbd::sqlite 1.42.

perl sqlite dbi