Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

Disappearing results with DBI and bind_columns

by ar0n (Priest)
on Dec 31, 2001 at 03:48 UTC ( #135292=perlquestion: print w/replies, xml ) Need Help??

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

I'm having some difficulties tracking down a problem with DBI. I have a method called get_picture which pulls out data from a MySQL database and returns a set of objects.

The method get_picture can be called with a column name/value pair to narrow the results, e.g:

my @pics = $db->get_pictures( user => 83, category => 9 );

And it worked fine up until today, when I added two columns to the select query, width and height. The width and height columns were part of the picture table for while, but I hadn't a need for them up until now.

Whereas before $db->get_picture( user => 1 ) would return four Phab::Picture objects, it now returns none. And the weird thing is, it does work if I remove the width and height columns from $sql and the $get->bind_columns call.

The complete get_picture method is below.

Any ideas?

sub get_picture { my $self = shift; my %clauses = @_; my (@pictures, @params); my ($id, $user, $created, $title, $caption); my ($type, $category, $filename, $size); my ($width, $height); my $sql = " SELECT id, user, created, title, caption, category, type, filename, size, width, height FROM picture"; if (%clauses) { $sql .= " WHERE " . join " AND ", map { "$_ = ?" } sort keys %clau +ses; @params = map { $clauses{$_} } sort keys %clauses; } my $get = $self->dbh->prepare($sql); $get->execute(@params); $get->bind_columns( \( $id, $user, $created, $title, $caption, $category, $type, $filename, $size, $width, $height ) ); while ($get->fetchrow) { my $picture = new Phab::Picture ( id => $id, user => $user, created => $created, title => $title, caption => $caption, type => $self->get_type( id => $type ), category => $self->get_category( id => $category ), filename => $filename || "unknown", size => $size, width => $width || 0, height => $height || 0 ); push @pictures, $picture; } return wantarray ? @pictures : $pictures[0]; }

[ ar0n -- want job (boston) ]

Replies are listed 'Best First'.
Re: Disappearing results with DBI and bind_columns
by lachoy (Parson) on Dec 31, 2001 at 04:08 UTC

    Just a couple of ideas, thinking out loud:

    - I know this isn't related to your width/height issue, but is fetchrow() a valid DBI routine? I know that fetch() is an alias for fetchrow_arrayref(), but I've never seen plain old fetchrow() before.

    - Are you sure the initial query runs ok? When something happens that I can't explain, I start by eliminating the immediately obvious things even though I know they can't possibly in a million years be wrong :-) Copying the query and pasting into a SQL shell gives a quick answer.

    M-x auto-bs-mode

      - I know this isn't related to your width/height issue, but is fetchrow() a valid DBI routine? I know that fetch() is an alias for fetchrow_arrayref(), but I've never seen plain old fetchrow() before.

      Holy moly, you're right.

      I merely switched fetchrow to fetch and it worked. I find this highly odd, to tell you the truth. I've used DBI for a few years now, and started using bind_columns about 6 months ago, after a Perl Mongers meeting. Since then, I've always used fetchrow. I'm surprised it hasn't once given me an error message or even a warning.

      Thank you.

      [ ar0n -- want job (boston) ]

        I'm not sure how you could have ever gotten bind columns to work with fetchrow. My understanding is that the fetchrow method is an old alias for fetchrow_array. I thought only plain old fetch returned values in bound columns since the others return their values in some sort of data structure -- an array in this case.

(Ovid) Re: Disappearing results with DBI and bind_columns
by Ovid (Cardinal) on Dec 31, 2001 at 04:10 UTC

    ar0n, I don't see your problem offhand, but have you tried the DBI->trace method? That should tell you exactly what is going on under the hood. I assume that you've already printed out your SQL and all that jazz.

    From the docs:

    DBI->trace($trace_level, $trace_filename)
    DBI trace information can be enabled for all handles using 
    the trace DBI class method. To enable trace information for 
    a specific handle, use the similar $h->trace method 
    described elsewhere. 
    Trace levels are as follows: 
      0 - Trace disabled.
      1 - Trace DBI method calls returning with results or errors.
      2 - Trace method entry with parameters and returning with results.
      3 - As above, adding some high-level information from the driver
          and some internal information from the DBI.
      4 - As above, adding more detailed information from the driver.
          Also includes DBI mutex information when using threaded Perl.
      5 and above - As above but with more and more obscure information.

    I assume you knew about that, but it's the last ditch method that I use when I'm having serious DBI issues. I've never needed a trace level above 3.


    Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

      Yes, I have, actually. At trace level 3. From what I can make out of it, it appears to be finding the rows. So there appears to be some sort of problem between the execute and the fetching of the results.

      Below is the output of DBI->trace

Re: Disappearing results with DBI and bind_columns
by petdance (Parson) on Dec 31, 2001 at 04:11 UTC
    Random thoughts:
    • Check the results of the prepare() and the execute(), even though you've also got RaiseError=>1
    • Dump the results of the built $sql string. Is it reasonable?
    • Parenthesize the sub-compares in the WHERE clause.

    <megaphone> Throw down the gun and tiara and come out of the float! </megaphone>

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://135292]
Approved by root
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (1)
As of 2021-10-16 18:45 GMT
Find Nodes?
    Voting Booth?
    My first memorable Perl project was:

    Results (69 votes). Check out past polls.