Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Handle MySQL BIT data type in Perl

by pg (Canon)
on Nov 01, 2005 at 03:23 UTC ( [id://504470]=perlquestion: print w/replies, xml ) Need Help??

pg has asked for the wisdom of the Perl Monks concerning the following question:

In MySQL database, I created a table test, with only one column called a, and the type for a is BIT(3).

From MySQL command interface, I tried:

insert into test(a) value(3)

It works fine.

When I did from Perl with:

use DBI; use Data::Dumper; use strict; use warnings; my $dsn = "DBI:mysql:database=test;host=foo"; my $dbh = DBI->connect($dsn, 'root', 'root', {RaiseError => 1}); my $insert = "insert into test(a) value(3)"; my $select = "select * from test"; my $insert_st = $dbh->prepare($insert); my $select_st = $dbh->prepare($select); $insert_st->execute() or die $dbh->errstr; $select_st->execute(); my $ref = $select_st->fetchall_arrayref(); print Dumper($ref);

It also worked. But when I tried to do it with placeholder:

use DBI; use Data::Dumper; use strict; use warnings; my $dsn = "DBI:mysql:database=test;host=foo"; my $dbh = DBI->connect($dsn, 'root', 'root', {RaiseError => 1}); my $insert = "insert into test(a) value(?)"; my $select = "select * from test"; my $insert_st = $dbh->prepare($insert); my $select_st = $dbh->prepare($select); $insert_st->execute(3) or die $dbh->errstr; $select_st->execute(); my $ref = $select_st->fetchall_arrayref(); print Dumper($ref);

It fails with:

DBD::mysql::st execute failed: Data too long for column 'a' at row 1 a +t math1.pl line 11. DBD::mysql::st execute failed: Data too long for column 'a' at row 1 a +t math1.pl line 11.

Looks like that the placeholder was not properly handled (data type was not correctly understood), so I played a bit and found a workaround:

use DBI; use Data::Dumper; use strict; use warnings; my $dsn = "DBI:mysql:database=test;host=foo"; my $dbh = DBI->connect($dsn, 'root', 'root', {RaiseError => 1}); my $insert = "insert into test(a) value(?)"; my $select = "select * from test"; my $insert_st = $dbh->prepare($insert); my $select_st = $dbh->prepare($select); $insert_st->execute(pack('b3', '110')) or die $dbh->errstr; $select_st->execute(); my $ref = $select_st->fetchall_arrayref(); print Dumper($ref);

That worked fine.

Is there any way that's more obvious with the placeholder? I don't really care the pack part, there are different ways to pack. But ideally the placeholder (DBI/DBD)should be self-sufficient, or at least allow me to indicate data type.

Replies are listed 'Best First'.
Re: Handle MySQL BIT data type in Perl
by jZed (Prior) on Nov 01, 2005 at 03:41 UTC
    See the DBI docs on bind_param. It allows you to bind parameters using data type constants that you can import with "use DBI qw(:sql_types)". For example:
    $sth->bind_param(1, $value, SQL_DATETIME);
    
    The same constants can be used in an execute.

      SQL_BIT is defined, when I ran:

      use DBI qw(:sql_types); use Data::Dumper; #use strict; use warnings; foreach (sort @{$DBI::EXPORT_TAGS{sql_types}}) { printf "%s=%d\n", $_, &{"DBI::$_"}; }

      One of the output line says:

      SQL_BIT=-7

      So there was some hope... However the following code compiled okay, but still gave the same error (data too long):

      use DBI qw(:sql_types); use Data::Dumper; use strict; use warnings; my $dsn = "DBI:mysql:database=test;host=foo"; my $dbh = DBI->connect($dsn, 'root', 'root', {RaiseError => 1}); my $insert = "insert into test(a) value(?)"; my $select = "select * from test"; my $insert_st = $dbh->prepare($insert); my $select_st = $dbh->prepare($select); $insert_st->bind_param(1, 3, SQL_BIT); $insert_st->execute() or die $dbh->errstr; $select_st->execute(); my $ref = $select_st->fetchall_arrayref(); print Dumper($ref);

      This further proves to me that, just like Zaxo and I diiscussed in private, this is a bug, integer was not trimed to fit.

      By the way, I tested Zaxo's chr(3) bit, and that worked.

      Or we can put it in virtualsue's way, the DBD has not yet updated for MySQL 5. By the way, MySQL only started to support BIT data type in version 5.

        I know I'm 12 years late but I just had the same problem and it's still not magically solved in recent versions. However, I found another solution that might be interesting for someone like me who stumbles across this and has the same problem!

        Similar to pg, I tried to insert a 1 or a 0 to a BIT(1) field in a MySQL table via DBI with placeholders. It did not work, same error. But I remembered that I previously had to convert the values from a query on said field that were returned by DBI (i.e. make "perl numbers" from the binary data):

        my $perl_value = unpack("b", $dbi_returned_value);

        Et voilá, the reverse (pack) worked fine with DBI, I didn't even needed to use bind_params()!

        use strict; use warnings; use DBI; my $dbh = DBI->connect("DBI:mysql:database=Foo", "user", "password", { +mysql_enable_utf8 => 1, RaiseError => 1, AutoCommit => 0}) or die "Ca +nnot connect to database: $DBI::errstr"; my $statement = "insert into testtable(bitfield) values(?);"; my $insertnumber = pack("b",0); my $sth = $dbh->prepare($statement); $sth->execute($insertnumber); $dbh->commit; $dbh->disconnect;
        MySQL v4.1 and below had stub-like support for BIT, which is why the current version of DBD::mysql sorta supports it. From the MySQL 4.1 Reference Manual:

        In versions of MySQL up to and including 4.1, BIT is a synonym for TINYINT(1).

        Whereas in version 5:

        This data type was added in MySQL 5.0.3 for MyISAM, and extended in 5.0.5 to MEMORY, InnoDB, and BDB. Before 5.0.3, BIT is a synonym for TINYINT(1)

Re: Handle MySQL BIT data type in Perl
by ait (Hermit) on Feb 09, 2022 at 21:56 UTC

    If you are using anything SQL::Abstract based like Mojo::mysql and you are using ->insert(... %hash) then you could do something like:

    [...] $new_user{REMOVED} = &mysqlBitField($data->{REMOVED}); [...] sub mysqlBitField($field){ return undef unless defined($field); return pack("b", 0) if $field == 0; return pack("b", 1); }

    Thank you mable for the pack b idea!!

Re: Handle MySQL BIT data type in Perl
by virtualsue (Vicar) on Nov 01, 2005 at 04:07 UTC
    Are you using MySQL 5.*? The BIT datatype, among other things, has changed since MySQL 4, and it looks to me as though the authors of DBD::mysql haven't had time to release an updated version. MySQL has a large number of changes & new features. If you are just messing around it doesn't matter, but if you are writing real code you have to consider whether you want to risk using a database version that isn't supported yet by the DBI. I wouldn't do it unless forced. Early adoption often leads to a lot of wasted time chasing incompatibilities between key software components as well as breakage due to brittle, non-road-tested new features.

    New MySQL Features

Re: Handle MySQL BIT data type in Perl
by Zaxo (Archbishop) on Nov 01, 2005 at 03:44 UTC

    Does the failure case work with chr(3)?

    After Compline,
    Zaxo

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://504470]
Approved by GrandFather
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (4)
As of 2024-03-19 02:19 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found