[Dbix-class] how to avoid adding duplicate row to table containing unique field/column

[Rajeev Prasad]

thx Rob,

I tried to use it like this:

for my $aref ( @newusers ) {=A0 =

=A0=A0=A0 print "adding [@$aref], or say $aref->[0]=A0 \n";
=A0=A0=A0 $schema->resultset('Testdbuser')->find_or_create(
=A0=A0=A0 =A0=A0=A0 {
=A0=A0=A0 =A0=A0=A0 =A0 userid =3D> $aref->[0],
=A0=A0=A0 =A0=A0=A0 =A0 password=A0 =3D> $aref->[1],
=A0=A0=A0 =A0=A0=A0 },
=A0=A0=A0 =A0=A0=A0 { key =3D> 'userid' }
=A0=A0=A0 =A0 );
=A0=A0=A0 } =




getting error: =



DBIx::Class::ResultSet::find_or_create(): Unknown unique constraint userid =
on 'testdbusers' at ./insertdb.pl line 25



see below that my table does have a unique constraint on this column.




________________________________
From: Rob Kinyon <rob.kinyon at gmail.com>
To: Rajeev Prasad <rp.neuli at yahoo.com>; DBIx::Class user and developer list=
 <dbix-class at lists.scsys.co.uk>
Sent: Wednesday, October 12, 2011 11:41 AM
Subject: Re: [Dbix-class] how to avoid adding duplicate row to table contai=
ning unique field/column


find_or_create is a method on DBIx::Class::ResultSet


On Wed, Oct 12, 2011 at 12:06, Rajeev Prasad <rp.neuli at yahoo.com> wrote:


>
>
>
>
>
>there is a find_and_create function which can do the job, i am not able to=
 find help on it. does anyone know?
>
>
>
>
>
>
>
>
>
>________________________________
>From: Rajeev Prasad <rp.neuli at yahoo.com>
>To: "dbix-class at lists.scsys.co.uk" <dbix-class at lists.scsys.co.uk>
>Sent: Wednesday, October 12, 2011 10:33 AM
>Subject: [Dbix-class] how to avoid adding duplicate row to table containin=
g unique field/column
>
>
>
>Hello,
>
>
>In my test script below, i am trying to figure out how to suppress the err=
or mesg when the the script try to add a previously presen tentry into the =
table.
>
>
>OR better even, to make sure that the script should not even try to update=
 the table, if the record is already present in table. (table may grow very=
 big in future).
>
>
>
>
table:
>Action
>Keyname
>Type
>Unique
>Packed
>Column
>Cardinality
>Collation
>Null
>Comment
> Edit  Drop =

>PRIMARYBTREE Yes No userid 3 A =

> =

> =

> Edit  Drop =

>useridBTREE Yes No userid 3 A =

> =

> =

>
>
>mysql> desc testdbusers;
>+----------+-------------+------+-----+---------+-------+
>| Field=A0=A0=A0 | Type=A0=A0=A0=A0=A0=A0=A0 | Null | Key | Default | Extr=
a |
>+----------+-------------+------+-----+---------+-------+
>| userid=A0=A0 | char(6)=A0=A0=A0=A0 | NO=A0=A0 | PRI | NULL=A0=A0=A0 |=A0=
=A0=A0=A0=A0=A0 |
>| password | varchar(10) | YES=A0 |=A0=A0=A0=A0 | NULL=A0=A0=A0 |=A0=A0=A0=
=A0=A0=A0
 |
>+----------+-------------+------+-----+---------+-------+
>2 rows in set (0.00 sec)
>
>mysql> =

>
>
>mysql> show index from testdbusers;
>+-------------+------------+----------+--------------+-------------+------=
-----+-------------+----------+--------+------+------------+---------+
>| Table=A0=A0=A0=A0=A0=A0 | Non_unique | Key_name | Seq_in_index | Column_=
name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Co=
mment |
>+-------------+------------+----------+--------------+-------------+------=
-----+-------------+----------+--------+------+------------+---------+
>| testdbusers |=A0=A0=A0=A0=A0=A0=A0=A0=A0 0 | PRIMARY=A0 |=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0 1 | userid=A0=A0=A0=A0=A0 | A=A0=A0=A0=A0=A0=A0=A0=A0 |=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 3 |=A0=A0=A0=A0 NULL | NULL=A0=A0 |=A0=A0=A0=
=A0=A0 | BTREE=A0=A0=A0=A0=A0
 |=A0=A0=A0=A0=A0=A0=A0=A0 |
>| testdbusers |=A0=A0=A0=A0=A0=A0=A0=A0=A0 0 | userid=A0=A0 |=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0 1 | userid=A0=A0=A0=A0=A0 | A=A0=A0=A0=A0=A0=A0=A0=A0=
 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 3 |=A0=A0=A0=A0 NULL | NULL=A0=A0 |=A0=A0=
=A0=A0=A0 | BTREE=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0 |
>+-------------+------------+----------+--------------+-------------+------=
-----+-------------+----------+--------+------+------------+---------+
>2 rows in set (0.00 sec)
>
>mysql> =

>
>
>
>
>
>
>
>
>
>script:
>
>
>#!/usr/bin/perl
>use strict;
>use warnings;
>use lib '../testdb';
>use Mysqltestdb::Schema;
>my $schema;
>my
 @newusers;
>$schema =3D Mysqltestdb::Schema->connect("dbi:mysql:dbname=3Dmysqltestdb:l=
ocalhost:3306", 'root', 'mypassword');
>@newusers =3D (['te1234', 'pass'], ['te4567', 'pass']);
>
>$schema->populate('Testdbuser', [
>=A0=A0 [qw/userid password/],
>=A0=A0 @newusers,
>]);
>
>
>
>
>
>
>
>I get this info message out from script(error):
>
>
>
>DBIx::Class::Schema::populate(): Duplicate entry 'te1234' for key 'PRIMARY=
' for populate slice:
>{
>=A0 password =3D> "pass",
>=A0 userid =3D> "te1234"
>}
>
>
>
>
>
>_______________________________________________
>List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>IRC: irc.perl.org#dbix-class
>SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.c=
o.uk
>
>
>_______________________________________________
>List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>IRC: irc.perl.org#dbix-class
>SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.c=
o.uk
>



-- =

Thanks,
Rob Kinyon
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20111012/616=
11e3b/attachment.htm