Re: Checking for entry in table?

On Tue, 2005-03-29 at 11:38 -0500, KEVIN ZEMBOWER wrote:
> I'm still trying to check whether a record exist in a table, and act
> accordingly. While I appreciate Garry's suggestion, I want to
> understand it fully, and I'm stuck.

My suggestion is to just insert the row you care about without checking
to see if it is there.  If it is already in the table, the insert will
fail on duplicate key (assuming that you have some column(s) defined as
unique or primary key).  The failure will produce $sth->err() == 1062,
if the insert is a duplicate.  Otherwise, you have your record inserted.

(See the DBI(3) manual page, section "METHODS COMMON TO ALL HANDLES" for
the $h->err() method and see "Chapter 24. Error Handling in MySQL" in
the MySQL manual for a list of the error codes.)

The reason I suggest this approach is that it eliminates a race
condition.  Between the time that you select a row which is not found
and the time you insert the record, another process can insert the same
record.  You cannot guarantee winning the race.

Another reason to just insert and handle the error is that it is
simpler.  There is only one query and the database handles the race
instead of you. 

> My program has this loop in it:
> my $kwsth = $dbh->prepare("SELECT keywordid FROM keywords WHERE TRIM(keyword)=?");
> while (<>) { #While there's more lines in the file called on the command line, of POPLINE document numbers and keywords
>    $ln++;
>    my ($popno, $kws) = split("\t"); #split on the tab following the POPLINE number

The split() function takes a regular expression as its first operand.
You can give it a string and Perl will convert that to a regular
expression, but why not just write it this way to avoid confusion?

    split(/\t/);

or simply

    split /\t/;

>    chomp($kws);
>    print "$popno:\n" if $debug;
>    my (@keywords) = split('\|', $kws); #split on the pipe symbol that separates keywords

    ... split /\|/, $kws;

>    foreach (@keywords) {
>       $_ = uc($_);
>       print "\t$_ " if $debug;
>       my $kwsthrv = $kwsth->execute($_) or warn "Problem with execute: $DBI::errstr\n";
>       if (! $kwsthrv) { print "Keyword $_ not found\n" } else {
>          my $keywordid = $kwsth->fetchrow_array or warn "Problem with fetch: $DBI::errstr\n";

The fetchrow_array() method returns a list (even if your select only
asks for one column).  You are creating scalar context by assigning it
to a scalar.  Although this is defined for a single column query, it is
not what you want.

    my ($keywordid) = $kwsth->fetchrow_array;

If there are no rows meeting the select criteria, the fetchrow_array()
method will return an empty list.  This is not considered an error (or
problem), but it is a false value.  If you get back an empty list the
only way to distinguish no rows from an error is to call $sth->err().

(See "fetchrow_array" and "execute" in the "DBI STATEMENT HANDLE
OBJECTS" section of the DBI(3) manual page.)

[snip]

> I want to detect, at either the execute or fetch phase, whether or not
> the record exists, 

The execute() is not the place.  See DBI(3).

As mentioned above, fetchrow_array() will return an empty list when
there is no record.  That is not an error -- it's expected.  But an
empty list will evaluate to false.  Your code is assuming that is an
error instead of interpreting it as no record found.

> and then either add it and get the mysql_insertid, or just fetch the
> id of the already inserted record. I don't want the errors that show
> up, or the warn() execution.

-- 
Garry Williams, Zvolve Systems, Inc., +1 770 813-4934
                                Cell: +1 404 353-2983


-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]