Re: Checking for entry in table? - Garry Williams - com.mysql.lists.perl

7 messages in com.mysql.lists.perlRe: Checking for entry in table?

From	Sent On	Attachments
KEVIN ZEMBOWER	29 Mar 2005 08:37
Greg Meckes	29 Mar 2005 09:29
Greg Meckes	29 Mar 2005 09:32
Garry Williams	29 Mar 2005 14:09
Rudy Lippan	29 Mar 2005 14:17
Garry Williams	29 Mar 2005 15:53
Rudy Lippan	31 Mar 2005 21:03

Subject:	Re: Checking for entry in table?
From:	Garry Williams (gar...@zvolve.com)
Date:	03/29/2005 03:53:17 PM
List:	com.mysql.lists.perl

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}) ...

$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 :-).