in reply to Perl/SQL Query

Our friend monks already gave many alternatives, but here is generaly what I do when using the DBI; 

die $db_handle->errstr;
my $sth = $dbh->prepare('SELECT * FROM table WHERE column LIKE ("%"?"%
+") LIMIT 1') or $error = "$db_handle->errstr");
my $rv = $sth -> execute($column_value);

[download]
You can later in your code check if there where any errors by saying 
if ($error) {do something}

[download]
Sometimes you dont have any errors, but your query resulted in zero rows!! You can check the number of rows affected by the $rv.

Best Wishes
Cav

update (broquaint): tided up formatting

Replies are listed 'Best First'.
Re: Re: Perl/SQL Query
by runrig (Abbot) on Sep 20, 2003 at 23:38 UTC
    Not to nitpick or anything, but that code is just so wrong. Your first statement dies, so the rest will not get executed, but if they were executed, the next statement prepares a SQL statement which does not do what I think you think it does. At least not portably (I'm not even sure it would work at all). If you want wildcards on either side of an argument, it is better done as:
    my $sth = $dbh->prepare("select ... where column like ?");
my $rv = $sth->execute("%$argument%");

    [download]
    Also, methods are not interpolated inside quotes, so $error will not be set to what you think it should be. LIMIT is not portable (the OP didn't mention which database, so this at least is a nitpick). Lastly, $rv is only set to the number of rows affected on non-select statements. Some DBD's might set it to the number of rows selected (I remember a thread about DBD::SQLite recently where the behavior was changing), but that is not necessarily the case. The only reliable way to know the number of rows selected is to fetch and count each one.
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom