Re^2: Handle MySQL BIT data type in Perl

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::$_"};
}
[download]

One of the output line says:

SQL_BIT=-7
[download]

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);
[download]

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.

Comment on Re^2: Handle MySQL BIT data type in Perl Select or Download Code

Replies are listed 'Best First'.
Re^3: Handle MySQL BIT data type in Perl by mable (Initiate) on Aug 16, 2017 at 15:15 UTC
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;` [download]	[reply] [d/l] [select]
Re^3: Handle MySQL BIT data type in Perl by virtualsue (Vicar) on Nov 01, 2005 at 10:15 UTC
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)	[reply]