Poking around in the source, it does not appear to be well tested WRT bind variables (see the test file below). I will file a bug at https://rt.cpan.org/Public/Dist/Display.html?Name=DBD-mysql but I wouldn't hold my breath on seeing anything other than a documentation change. There is probably too much code in the darkpan that counts on the current behavior (ie making it sensible would break existing code that expects it to be broken).
use strict <https://metacpan.org/source/strict>; use warnings <https://metacpan.org/source/warnings>; use Test::More <https://metacpan.org/source/Test::More>; use DBI <https://metacpan.org/source/DBI>; use vars <https://metacpan.org/source/vars> qw($test_dsn $test_user $test_password); use lib <https://metacpan.org/source/lib> '.', 't'; require 'lib.pl'; sub VerifyBit ($) { } my $dbh; my $charset= 'DEFAULT CHARSET=utf8'; eval {$dbh = DBI->connect($test_dsn, $test_user, $test_password, { RaiseError => 1, AutoCommit => 1}) or ServerError() ;}; if ($@) { plan skip_all => "no database connection"; } else { plan tests => 15; } if (!MinimumVersion($dbh, '4.1')) { $charset= ''; } ok $dbh->do("DROP TABLE IF EXISTS dbd_mysql_b1"), "Drop table if exists dbd_mysql_b1"; ok( $dbh->do('CREATE TABLE dbd_mysql_b1 (b BIT(8))') ); ok ($dbh->do("insert into dbd_mysql_b1 set b = b'11111111'")); ok ($dbh->do("insert into dbd_mysql_b1 set b = b'1010'")); ok ($dbh->do("insert into dbd_mysql_b1 set b = b'0101'")); ok (my $sth = $dbh->prepare("select BIN(b+0) FROM dbd_mysql_b1")); ok ($sth->execute); ok (my $result = $sth->fetchall_arrayref); ok defined($result), "result returned defined"; is $result->[0][0], 11111111, "should be 11111111"; is $result->[1][0], 1010, "should be 1010"; is $result->[2][0], 101, "should be 101"; ok ($sth->finish); ok $dbh->do("DROP TABLE dbd_mysql_b1"), "Drop table dbd_mysql_b1"; ok $dbh->disconnect; On Sat, Oct 1, 2016 at 11:34 AM Chas. Owens <chas.ow...@gmail.com> wrote: > Whoops, meant to include links for the docs to those two functions: > > http://perldoc.perl.org/functions/pack.html > http://perldoc.perl.org/functions/vec.html > > On Sat, Oct 1, 2016 at 11:31 AM Chas. Owens <chas.ow...@gmail.com> wrote: > > 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/ > > >
