Red_Dragon has asked for the wisdom of the Perl Monks concerning the following question:

Most generous Perl Monks,

I have come to you in the past and you have never failed me. I come to you now with yet another query.

Is there some variable, that is set upon return from execution of an SQL command, within a Perl script, that can be interrogated for success or failure.

Thanks again for your generous indulgence.
R_D

Replies are listed 'Best First'.
Re: Perl/SQL Query
by gmax (Abbot) on Sep 19, 2003 at 21:28 UTC

    The short answer to your question is "yes, there is," but the explanation would require much more text.

    Almost every method in the DBI can be tested for a return value to signify success or failure. Some return just a "true" value, some return an object, some return a count of something that was done (e.g. a row count for update, insertion, or deletion).

    I recommend that you get to reading some documentation, and you should start from our Tutorials. In particular, read Reading from a database and Tricks with DBI.

    Also, it may be enlightening for you to read Before asking a database related question ....

     _  _ _  _  
(_|| | |(_|><
 _|

    [download]
[reply]
[d/l]
Re: Perl/SQL Query
by qmole (Beadle) on Sep 19, 2003 at 21:15 UTC
    An example in Perl In a Nutshell reads:
    $st_handle->execute("1-56592-286-7", "Perl in a Nutshell") 
    || die $db_handle->errstr;

    [download]

    It looks like errstr is what you're looking for.
[reply]
[d/l]
[select]
Re: Perl/SQL Query
by tcf22 (Priest) on Sep 19, 2003 at 22:38 UTC
    It really depends on the DB module that you are using. At home I use DBI, but at work I'm forced to use Win32::ODBC. Check out CPAN for the documentation on your current module. This should include any error variables.

    - Tom

[reply]
Re: Perl/SQL Query
by Anonymous Monk on Sep 19, 2003 at 21:15 UTC
    Go read the DBI documentation. Among many other useful things, it will tell you that execute() returns true if it succeeds, and "undef" if an error occurs.

    -- am
[reply]
[d/l]
Re: Perl/SQL Query
by cavalive (Novice) on Sep 20, 2003 at 02:06 UTC
    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

[reply]
[d/l]
[select]
      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]
Re: Perl/SQL Query
by granddad (Initiate) on Sep 20, 2003 at 09:49 UTC
    I think if you: 
    eval { $sth=$dbh->prepare("SELECT blurge
                           FROM blurge
                           WHERE blurge=\'$blurge\'") };
my $oops="oops. Look at this:\n$@";
return \$oops if $@;
eval { $sth->execute };
$oops="ooops. Look at this:\$@";
return $oops if $@;

    [download]
    And so on.....

    update (broquaint): added formatting

[reply]
[d/l]
      Yeah, that's another way to do it. I think that only works if you set $handle->{RaiseError} = 1, though. That was the impression I got from the documentation.
[reply]
[d/l]
        That's right. When you do the connect.
[reply]

Back to Seekers of Perl Wisdom