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 | |
by pg (Canon) on Nov 01, 2005 at 05:00 UTC | |
by mable (Initiate) on Aug 16, 2017 at 15:15 UTC | |
by virtualsue (Vicar) on Nov 01, 2005 at 10:15 UTC | |
Re: Handle MySQL BIT data type in Perl
by ait (Hermit) on Feb 09, 2022 at 21:56 UTC | |
Re: Handle MySQL BIT data type in Perl
by virtualsue (Vicar) on Nov 01, 2005 at 04:07 UTC | |
Re: Handle MySQL BIT data type in Perl
by Zaxo (Archbishop) on Nov 01, 2005 at 03:44 UTC |
Back to
Seekers of Perl Wisdom