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

Greetings all,
This is a smallish issue and not a mission critical one (at least not for me), but I was wondering about the difference in behavior for UPDATE|DELETE|INSERT queries in DBI. Here is the issue. I am running MySQL 4.0 and have noticed over the past few months that return values from DBI calls differ depending on how the query is executed. example: 
###idiosyncratic paranoid method wherein I prepare everything.
my $sth = $dbh->prepare("UPDATE mytable SET value1=?, value2=? WHERE i
+d=?") || die $dbh->errstr;
my $update_success = $sth->execute($val1,$val2,$id) || die $sth->errst
+r;

###non paranoid method cleaner as well.
my $update_success = $dbh->do("UPDATE mytable SET value1='$val1', valu
+e2='$val2' WHERE id=$id") || die $dbh->errstr;

[download]
The issue arises when checking the value of $update_success. The first method does not return a value consistently (at least not consistently enough for me to code with). Where as the latter method returns a value but does not allow for the flexibility of the first method, and the query must be recreated with each successive call.
So, Is there something I am missing or shouldn't both methods return a value? I have already realized that the update failure returns "0E0" and not just "0" as I had originally thought, but aside from this why does preparing any type of query other than a "SELECT" not return values? Should I be checking some property of the statement handle? the database handle? or am I stuck not preparing non-select queries? Has anyone else come across this behavior as well?
Thank you all in advance.
-injunjoel

Replies are listed 'Best First'.
Re: Return values from $dbh->do vs. $sth->execute();
by mpeppler (Vicar) on Nov 24, 2003 at 22:59 UTC
    Both do() and execute() should return undef in the case of an error.

    The thing to realize is that an UPDATE or DELETE that affects 0 rows is not an error, as far as both DBI and MySQL are concerned. This is why you get the 0E0 return value instead of 0: the operation succeeded, but didn't affect any rows.

    Having said all that, you should still be using placeholders for UPDATE/DELETE/INSERT statements. You can do that with do() if you want.

    Michael

[reply]
[d/l]
[select]
      Thank you for the quick response
      I am afraid though, that you misread my question.
      The issue I am having is not between "0E0" vs "0" being returned (that was a side note to indicate that I have already accounted for that), my issue is that the number of rows affected by the query is not being consistently returned. With $dbh->do I get a consistent return value but I cannot prepare with ?'s. If I prepare the statement and assign it to a statement handle my $sth = $dbh->prepare($query) || die $dbh->errstr; I cannot count on getting the number of rows affected. Is this consistent with DBI? or does this behavior seem specific to my system setup? Is there a property of the $dbh I should be checking?

      You also mentioned utilizing placeholders ? within a $dbh->do() call, where could I find information regarding that, or would you be so kind as to offer an example?

      Like I hopefully mentioned earlier this is not a critical issue for me but I figured I should post it as a question in case there was someone else who has seen this behavior and had already found the explaination/solution. Thank you again
      -injunjoel
[reply]
[d/l]
[select]
        For placeholders with do(): 
        # Execute a delete from table foo where bar = "bars_value":
$dbh->do("delete from foo where bar = ?", undef, "bars_value");

        [download]
        For the return codes, the DBI spec says that do() and execute() should return the same thing - that is the number of rows affected by the operation, if that information is available, or -1 if the number of rows affected is unknown.

        Now I don't know much about MySQL, or about DBD::mysql, so there may be something special going on here. You might want to check the DBD::mysql documentation directly.

        Michael

[reply]
[d/l]
[select]
Re: Return values from $dbh->do vs. $sth->execute();
by BUU (Prior) on Nov 25, 2003 at 04:12 UTC
    The difference is the type of queries, not how you call them. Update, delete, etc queries will all return a fixed number of rows they affected. Select queries will not as the database has no idea how many rows it affects until it has finished the statement. This is a basic database design issue, probably for optimization purposes. It's in the dbi docs last I checked.
[reply]

Back to Seekers of Perl Wisdom