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 );

[download]

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];
}

[download]

[ ar0n -- want job (boston) ]

In reply to Disappearing results with DBI and bind_columns by ar0n

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.