Database stuff

Raziel has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks, I am working with the DBI module, and I have a small problem. I have a select statement that will retrieve rows, based on a name. What I want to do is using the name, I want to determine if the name already exists in the database. If it doesn't, I want to add it to the database. How do I check to see if the name already exists? Here is my code so far:

sub checkDuplicateName
{
    my($db, $name) = @_;

    my $sth;
    my $rv;
    my @row;
    my $duplicate;
    
    $sth = $db->prepare('SELECT "Names" FROM Customers WHERE "Names" =
+ ?');
    $rv = $sth->execute($name);

    while(@row = $sth->fetchrow_array)
    {
        next;
    }

    if(undef $row[0])
    {
        $duplicate = 0;
    }
    else
    {
        $duplicate = 1;
    }

    return $duplicate;
}
[download]

Thanks for any help

Comment on Database stuff Download Code

Replies are listed 'Best First'.
Re: Database stuff by mpeppler (Vicar) on Jan 21, 2003 at 19:54 UTC
There are a number of issues with the way you propose to do this check. But first, let's correct your code. In your code, when the fetch loop exits you will be left with an empty @row array, so your test will always be false. You should instead do something like this: `while(@row = $sth->fetchrow_array) { ++$duplicate; }` [download] I think you also have a problem with the actual SELECT statement, where I would drop the quotes around `Names`. Now there is a more fundamental problem with this approach: What happens if someone inserts the same name between the time you run your check and the time you try to insert it? Unless you are certain that your script will be running all by itself you should let the database check for duplicates for you. This involves creating a unique index (or a unique constraint) on the `Names` column, attempting the insert and checking for a `duplicate key` error. Using RaiseError and eval makes this quite easy: my $dbh = DBI->connect($dsn, $usr, $pwd, {RaiseError => 1}); my $sth = $dbh->prepare("insert into Customers(Names, ...) values(?, . +..)"); eval { $sth->execute($new_name, ...); }; if($@) { # Got an error! # The insert failed - probably due to the duplicate key constraint # You can check the $@ variable which holds the error # string that caused the insert to fail to see what # error happened. # Handle this the same way that you would handle the # case where your checkDuplicateName() returns true } [download] Good luck! Michael	[reply] [d/l] [select]
Re: Database stuff by CountZero (Bishop) on Jan 21, 2003 at 19:44 UTC
Try the following SQL: `SELECT count("Names") FROM Customers WHERE "Names" = ?` If the result is zero, the name was not in the database. If you are sure that there are no duplicate names in the database, you could assign the result of the query directly to $duplicate: `($duplicate) = $sth->fetchrow_array` and drop the whole while loop. CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply] [d/l] [select]
Re: Database stuff by gt8073a (Hermit) on Jan 21, 2003 at 19:54 UTC
One simple solution: `sub checkDuplicateName { ## args my($db, $name) = @_; ## important! do not forget to quote name $quotedName = $db->quote( $name ); ## select statement to count number of occurences my $select = "select count(*) from customers where names=$quote +dName"; ## atomic fetch my( $toReturn ) = $db->selectrow_array( $select ); ## return value return $toReturn; }` [download] Will perl for money JJ Knitis (901) 756-7693 gt8073a@industrialmusic.com	[reply] [d/l]
Re: Database stuff by OM_Zen (Scribe) on Jan 21, 2003 at 22:30 UTC
Hi , `my $sth = $dbh->prepare("Insert into customers (Names) select ? from +dual where not exists (select Names from Customers where Names = ?") $sth->execute($name,$name);` [download] This shall work in Oracle rdbms ,the inserts actually inserts the Name that is not in the database's entity already and hence takes care of the duplicate by itself	[reply] [d/l]