The URL for the bug report is https://rt.cpan.org/Ticket/Display.html?id=118207&results=52bad5c05e442e5750731e7011056012
On Sat, Oct 1, 2016 at 11:44 AM Chas. Owens <chas.ow...@gmail.com> wrote: > 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/ > > >
