Re: mySQL with Perl

Before you insert into the database, you can run a simple test like this (I handle query's slightly differently):

# This is untested -- no perl here
my $sth = $dbh->prepare("SELECT name FROM members WHERE username = ?")
+;

$sth->execute($username);  # this replaces the '?' above

if ($sth->rows != 0) {
  my $user = $sth->fetchrow_array;
  print "This username is in use by $user.  Try another username.\n";
} else {
  $sth = $dbh->prepare("INSERT INTO member (username, password, email,
+ name, url, date) VALUES (?, ?, ?, ?, ?, ?)");
  $sth->execute($username, $password, $email, $name, $url, $time);
  print "User Created Normally";
}
[download]

The first query is used to see if anyone exists in the database with that name. If it does not, it will return 0 rows ($sth->rows), and the user is created. If rows are returned, an error is put out.

Now you might ask why I handle queries that way. I use question marks for two primary reasons. One, I don't have to worry about punctuation so much. This helps me to avoid bugs. Second, I do this so that I can define all my queries at the top of a subroutine ($sth_user, $sth_articles, etc). I might define more than one, because I might try to nest some of my queries within while loops and if statements. It helps me to maintain clean code in the long run. Your method isn't incorrect...just that I find my method more logical for what I do. (Proof that there is 100 ways to do 1 thing).

Hope it helps.

--Coplan

Comment on Re: mySQL with Perl Select or Download Code

Replies are listed 'Best First'.
Re: Re: mySQL with Perl by VSarkiss (Monsignor) on Mar 05, 2002 at 23:41 UTC
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.)	[reply] [d/l] [select]
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]
Re (4): mySQL with Perl by VSarkiss (Monsignor) on Nov 11, 2002 at 22:08 UTC
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]