in reply to Re: mySQL with Perl
in thread mySQL with Perl

Be careful: in a multi-user database, someone can insert the row between the select and insert statements. A better way to do this is to combine the existence test with the insert: 

my $sql = qq{
if not exists
    (select 1 from members where username = ?)
insert into members
    (username, password, email, name, url, date)
values (?, ?, ?, ?, ?, ?)
};
my $sth = $dbh->prepare($sql);
$sth->execute($username, $username,
    $password, $email, $name, $url, $date)

[download]
Note that $username appears twice, the first one to bind to the existence test. (BTW, I haven't tested this, and I'm assuming MySQL supports prepared exists clauses.)

Replies are listed 'Best First'.
Re: Re: Re: mySQL with Perl
by Coplan (Pilgrim) on Nov 11, 2002 at 21:52 UTC
    I'm only really familiar with mySQL. But is that a mySQL specific command? Or is that common among SQL in general? I your reasoning for doing this completely. And now that I've heard your views on the issue, I'd like to implement such a feature into my coding as well. But I'd like to keep my code generalized should I need to change the SQL database in the future (maybe to postgres, or something).

    Thanks,

    --Coplan

[reply]

      The exists clause is standard SQL, at least SQL-92, so it should be supported by most if not all platforms. Note that "supported" doesn't equate to "works well".

      There are several ways to achieve concurrency control, and what I showed is not necessarily the best way (granted that "best" varies from circumstance to circumstance). You could also use user-defined transactions, though I think not every MySQL back-end supports those. Or you could define a unique key on the user name, and catch the error if you try to insert a duplicate. I said "Be careful" to the code you showed mainly because it was leaving a pretty wide window where someone could get a row in between the test and the insert.

[reply]
[d/l]

In Section Seekers of Perl Wisdom