DBI question

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
hi all, i know how you all hate stupid questions, understandbly, but i just
can't find this situation so i have to ask...

at the very bottom is my actual code where essentially in the same table i
search for a scalar in either field ClientID1 or field ClientID2.  now i
didn't write it in ONE search at first for particualar reasons, so if the
first field search failes:


i look for it in the second field.  which actually passes the
unless($matches) test ... yet reads no record info at
my @IAORecord = $sth ->fetchrow_array;

so as a test, just before the second search, i added three lines which
properly close the db connection and restart it...and then my code works.
you can see three lines i added becuase they are marked with a '#'

so have been thinking that somewhere perhaps for a "failed search", an "EOF
" situation there might be something I have to reset?   i AM looking through
my books and online, but so far nothing.  also, YES i can rewrite this upon
further examination to use OR, however, this is a behavior i need to learn
what it means, so want to figure this out before proceeding further....

thanks ahead as always,


here is the actual code, as i said, with the three commented lines NOT used,
the second search fails...., otherwise it works....thanks...

$sth=$db->prepare("SELECT * from ClientFile WHERE ClientID1='$search_id;");

        #    $sth->finish();
        #    $db->disconnect;
        #    $db=DBI->connect(-------------);
        $sth=$db->prepare("SELECT * from ClientFile WHERE

             return 1;
  my @IAORecord = $sth ->fetchrow_array;

Re: DBI question

daniel kaplan wrote:
> so have been thinking that somewhere perhaps for a "failed search", an "EOF
> " situation there might be something I have to reset?  

Not in my experience.  I run many queries off the same instance of a DBI
database connection.  I even use Apache::DBI for connection pooling, so
many web requests may reuse the same database connection.

However, according to the DBI docs, $sth->rows() is not a reliable
measure of the size of the result set for a SELECT query.  It's intended
to be used for non-SELECT operations.  It's hard for the client
interface to know how many rows will result from a query, without
actually fetching them.  Many RDBMS's don't have any support for
reporting this record count before you fetch the data.

Also, $sth->rows() may return -1 if the number is not available (for
instance, for a SELECT query).  Whether it does or not probably depends
on the implementation of the DBD driver for the RDBMS you're using.  So
if you only check "unless($rows)", and not "unless($rows==0)", you could
be getting a pass when you don't intend to.  A value of -1 evaluates to
true in Perl expressions.

One reliable and portable way to determine the number of rows before
fetching them is to execute a COUNT(*) query with the same conditions as
the query you want to perform.

Another method is to execute your desired query, fetch the first row,
and see if it is empty.  If you use fetchrow_array(), this method
returns an empty list at the end of the result set.  If you use
fetchrow_arrayref, it returns undef, and you need to check $sth->err to
make sure it was the end of the results and not an error.

> $db=DBI->connect(-------------);
> $sth=$db->prepare("SELECT * from ClientFile WHERE ClientID1='$search_id;");

I notice some other things, which could potentially be problems for you:

1. You haven't checked if defined($sth).  You need to do some error
checking to make sure there were no errors parsing the SQL.

2. You do in fact have an error in your SQL: you didn't close the
single-quote around $search_id.

3. You haven't filtered $search_id to make sure it won't break your
syntax.  What if I do this before the prepare:
   $search_id = "FOO' <> // ++ Look ma no syntax checking!";
and then use the $search_id in your prepare as you have coded it.
Even something more innocuous would be a problem:
   $search_id = "O'Reilly";

DBI has a db-level method quote() that should help with this.  See the
DBI docs for more info on this method.  Here's an example of its use:

   $sth=$db->prepare("SELECT * FROM ClientFile WHERE ClientID1="
     . $db->quote($search_id));
   unless (defined($sth)) {
     print STDERR "$db->errstr\n";

As an alternative, which I actually prefer, use a query parameter in
place of the constant value:

   $sth=$db->prepare("SELECT * FROM ClientFile WHERE ClientID1=?");
   unless (defined($sth)) {
     print STDERR "$db->errstr\n";

Then feed your parameter to execute().  You don't have to worry about
quoting issues.  The value you provide is treated as a constant value
and won't break your query.  Also check for an undef return value from
execute, which could indicate a different error (privilege problem on
the table you're querying, for instance).

   $retval = $sth->execute($search_id);
   unless (defined($retval)) {
     print STDERR "$sth->errstr\n";

Bill K.

Re: DBI question

[entire OP]

please ignore this post, i finally found "If execute() is called on a
statement handle that's still active ($sth-> is true) then it should
effectively call finish() to tidy up the previous execution results before
starting this new execution."

and that works...actually if i were smart i would have taken those three #
lines and asked, which one does what and why?

but, and I don't know where to post this...is it wrong that without calling
finish before a new execute on the same object that it should SHOW matches
and retrieve NONE?

anyway, let me get this up here before it's too late

Site Timeline