How can I use mysql bit from using DBIx

Question

Hi,

I am trying to access a mysql bit field in my catalyst app. Here is the table info:

SQL:

create table foo{
 ...
 mybitField bit(1) NOT NULL default b'0'
}

My mapped table:

...

mybitField
{
    data_type => "BIT",
    default_value => "b'0'",
    is_nullable => 0,
    size => undef,
  }
...

Now in my controller, I do a simple

$c->stash->{foos}=[$c->model('DB::foo')->all];

and in my template, I have tried the following:

[% FOREACH foo IN foos -%]
  <tr>
      [%- IF int(foo.mybitField) %]
                <td>The field is True</td>
        [%- ELSE %]
                <td>The field is False</td>
        [% END %]
  </tr>
[% END -%]

I also tried just

[%- IF foo.mybitField %]

but that did not work either. Any other way apart from changing the database field type itself?

Answer 1

I just tested this and MySQL BIT fields come back as the "raw" binary value, as expected. So it seems what you have is a Template Toolkit issue.

I don't know what you mean by:

[%- IF int(foo.mybitField) %]

I don't think TT has an int() function. And Perl's int() function wouldn't do what you want, either. My suggestion would be to either write a function in Perl which converts the packed value into a regular integer, for instance:

my $int = unpack( 'c', $bit_field );

An alternative would to add some column inflation column to your schema classes.

__PACKAGE__->inflate_column('mybitField', {
    inflate => sub { unpack( 'c*', shift ) },
});

However, this still would still fail for updates and I don't know a simple solution for that. I never used the BIT data type myself on MySQL - I usually use a CHAR(1) column.

Also, you might get better answers if you ask on the DBIC mailing list at [email protected] or at the #dbix-class channel on irc.perl.org.