DBD::mysql is treating 1 and 3 as their ASCII values on insert due to
quoting. You need to create values that are bit fields themselves. This
being Perl, there are lots of ways of doing that:
$dbh->do("create table bittest (lilbits bit(8))");
my $insert = $dbh->prepare("insert into bittest values (?)");
my $select = $dbh->prepare("select * from bittest where lilbits = ?");
$insert->execute(pack "n", 5);
$insert->execute(pack "b", "101");
vec(my $bitstring, 0, 8) = 5;
$insert->execute($bitstring);
$select->execute(5);
while (my $row = $select->fetch) {
printf "%08b\n", ord $row->[0];
}
The first two use the pack function to pack the input into a binary
representation (n converts integers, b converts strings of 0s and 1s). The
third uses the vec function as an lvalue to build a bitstring.
Interestingly, selecting with bitstrings doesn't seem to work and you have
to use the number. I am not sure if this is a bug or not. The
inconsistent behavior for select and insert is certainly surprising enough
that I would expect it to be mentioned in the docs, but I didn't see it.
On Sat, Oct 1, 2016 at 10:35 AM hw <h...@gc-24.de> wrote:
>
> Hi,
>
> what´s the correct way with DBI to update a field in a mysql database
> when the field type is 'bit'?
>
> I´m getting, for example, converted to integer, 53 instead of 3 into
> the field.
>
> It goes like this:
>
>
> my $sth = $dbh->prepare("INSERT INTO t (`status`) VALUES (?)
> ON DUPLICATE KEY UPDATE `status` = ?");
>
> $sth->execute(($var eq 'x' ? 1 : 0), ($var eq 'x' ? 3 : 0));
>
>
> That should work just fine but doesn´t in that the wrong bits of the
> field are being set.
>
> Is this a bug or a feature?
>
> --
> To unsubscribe, e-mail: beginners-unsubscr...@perl.org
> For additional commands, e-mail: beginners-h...@perl.org
> http://learn.perl.org/
>
>
>
