Re: DBI and field type 'bit'

[hw]

Chas. Owens schrieb:

The URL for the bug report is 
https://rt.cpan.org/Ticket/Display.html?id=118207&results=52bad5c05e442e5750731e7011056012

Thank you very much!

I resorted to use an integer instead of a bitfield,
and it works fine with things like


... SET `status` = `status` + 32 WHERE (NOT (`status` & 32)) AND foo ...


I´m not sure what might happen if endianess ever changes, though.

On Sat, Oct 1, 2016 at 11:44 AM Chas. Owens <chas.ow...@gmail.com 
<mailto: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 <http://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 
<mailto: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 
<mailto: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 
<mailto: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 
<mailto:beginners-unsubscr...@perl.org>
                For additional commands, e-mail: beginners-h...@perl.org 
<mailto:beginners-h...@perl.org>
                http://learn.perl.org/

--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/