Before insertion new record in database how to check if same record is present

Question

Before inserting a new record in the database, how can I check if the same record is present in database table? If present, then it should throw an error message.

My insertion code:

my $sth = $dbh->prepare("INSERT into backlogs (backlogtype, name, startDate, endDate, parent_id) VALUES ('Iteration', '$iter', '" .
                                $iterations{ $iter }->{start} . " 08:00', '" .
                                $iterations{ $iter }->{end} . " 18:00', '" .
                                $project . "');");
$sth->execute() or die $DBI::errstr;
$sth->finish();

make a select with the info you want to insert and check if there's a result — Kiwy, Dec 18 '13 at 10:30
Do you have a unique constraint / index on the target table? If so, your code will automatically throw an error message. — Ross Attrill, Dec 18 '13 at 10:32
if `primary` key is present in your table, DB will automatically throw error. — Krishnachandra Sharma, Dec 18 '13 at 10:33
@KrishnachandraSharma primary key is present but thats on id column i want to add iteration under project so under one project no same iteration should add that too without constraint on that column. shoul throw error before inserting. — samio peter, Dec 18 '13 at 10:35
If you don't want to or can't add a unique constraint, then you should run a select first as per @Kiwy's original comment. Most DBMS's provide unique constraints these days. — Ross Attrill, Dec 18 '13 at 10:43
@RossAttrill I have tried using select but didnt work ..... may i know if you have any other select example by which it can work because i cant add constraint. please show it by example. — samio peter, Dec 18 '13 at 10:45

score 3 · Answer 1 · answered Dec 18 '13 at 10:36

3

If there is any unique constraint in the table like id then code will automatic throw an exception. You just have to handle that exception and show appropriate message/error.

answered Dec 18 '13 at 10:36

pan1490

939
1
7
25

Ross Attrill · Answer 2 · 2013-12-18T20:36:24.597

0

Add a unique constraint or index to your database table. The precise syntax for this will depend on the DBMS you are using. For SQLLite for example this would like something like this:

CREATE UNIQUE INDEX backlogs_uc1 ON backlogs(backlogtype, parent_id, start_date);

Precisely which columns go in your constraint depend on your database design - you want the columns that naturally identify each row.

If you do this then your existing Perl will catch the unique constraint exception from the database and die printing the database error string.

edited Dec 18 '13 at 20:36

answered Dec 18 '13 at 10:39

Ross Attrill

2,594
1
22
31

Can you show it by select query example how can i check and throw error if same record present – samio peter Dec 18 '13 at 10:45
See the two answers below. – Ross Attrill Dec 18 '13 at 21:12

codnodder · Answer 3 · 2013-12-18T11:55:35.380

If you cannot add constraints, here is an example that does a pre-select.

Disclaimer: This method suffers from race conditions and concurrency issues.

use DBI;

# use test; create table x ( y int not null unique );
my $dsn = "DBI:mysql:database=test;host=localhost";
my $dbh = DBI->connect($dsn, DBUSER, DBPASS);

my $sth_select = $dbh->prepare("select count(*) from x where y=?");
my $sth_insert = $dbh->prepare("insert into x (y) values (?)");

for my $i (0..9) {
    my $val = int(rand(3));
    if ($dbh->selectrow_array($sth_select, undef, $val) > 0) {
        warn "[line $i] skipped duplicate entry $val\n";
    }
    else {
        $sth_insert->execute($val);
    }
}

Carlisle18 · Answer 4 · 2013-12-18T12:09:45.527

0

You can check for existence and throw error by having a code similar to this:

my $type = 'the_type';
my $name = 'the_name';

if ( $dbh->selectrow_array("SELECT COUNT(1) FROM backlogs WHERE backlogtype = '$type' and name = '$name'") ) {
    croak "Record with backlogtype '$type' and name '$name' already exists.";
}

Aside from this check, I would still recommend to have the unique constraint so that when a user tries to insert duplicate data straight into your DB without using your tool/script, it will still be caught.

edited Dec 18 '13 at 12:09

answered Dec 18 '13 at 12:03

Carlisle18

389
2
11

may i know whats croak is? – samio peter Dec 18 '13 at 12:34
@sammy, Carp's croak acts similar to die but provides more information. More details here: http://www.perlmonks.org/?node_id=685452 and http://stackoverflow.com/questions/4156391/is-it-better-to-croak-or-to-die-when-something-bad-happens-in-perl. In your case, for trial, you can still use die instead of croak here. – Carlisle18 Dec 18 '13 at 12:49

Before insertion new record in database how to check if same record is present

4 Answers4