Re: Checking for entry in table?

On Tue, 2005-03-29 at 17:18 -0500, Rudy Lippan wrote:
> On Tue, 29 Mar 2005, Garry Williams wrote:
> 
> > 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.
> 
> Of course some DBs will abort a transaction in the event of a failed insert 
> because of the way the SQL spec is written.

I must misunderstand your point.  (Which, if you know me is not
surprising. :-)  That is exactly what I am advocating.  The problem of
insert if not there, retrieve otherwise is a common one.  Just insert
and catch the error and make sure it's because of duplicate key.  The
MySQL database defines this error as 1062.  Catch that error and
retrieve the existing record.  If no error, then the insert that you
wanted is done.

At least that's how I understood the original poster's problem.

> > record.  You cannot guarantee winning the race.
> 
> 
> $dbh->do(q{LOCK TABLE foo})
> ...
> 
> or
> 
> $dbh->{AutoCommit} = 0; # use transactions
> $dbh->do(q{SET TRANSACTION ISOLATION LEVEL SERALIZABLE}); # paranoid

OK, yes I realized that.  The original poster was not using
transactions.  And I thought that was getting silly just to do an
insert, if and only if the record does not already exist.  The insert
must fail, if there's a record there already and it will succeed
otherwise (assuming the disk array didn't catch on fire or
something :-).

-- 
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]