Re: MySqlBulkLoader BIT columns
Hi,
As an update/correction on this, you can actually use bit columns with load data/select .. infile, but there are some tricks to consider
First assuming these scripts:
create table TableWithBitColumns (
id int auto_increment primary key,
MyName varchar( 20 ),
Flagged bit( 1 )
)
insert into TableWithBitColumns( id, MyName, Flagged ) values ( 1, 'C#', 1 );
insert into TableWithBitColumns( id, MyName, Flagged ) values ( 2, 'C++', 0 );
insert into TableWithBitColumns( id, MyName, Flagged ) values ( 3, 'Java', 1 );
insert into TableWithBitColumns( id, MyName, Flagged ) values ( 4, 'Python', 1 );
insert into TableWithBitColumns( id, MyName, Flagged ) values ( 5, 'F#', 0 );
insert into TableWithBitColumns( id, MyName, Flagged ) values ( 6, 'Haskell', 1 );
insert into TableWithBitColumns( id, MyName, Flagged ) values ( 7, 'Scala', 1 );
then generating a file like
select t.Id, t.MyName, t.`Flagged`
into outfile 'c:\\temp\\data.txt'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from TableWithBitColumns t
will generate a data.txt file when the third column of each row is a non printable character (ASCII 0 or 1).
Then you can 'load data' that file and will work fine.
On the other hand if you have a file custom made like this:
1,"C#",1
2,"C++",0
3,"Java",0
4,"Python",1
5,"F#",1
6,"Haskell",0
7,"Scala",1
you'll need to feed it using a command like this:
load data infile 'c:\\temp\\data.txt' into table TableWithBitColumns FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' (id,MyName,@v) set Flagged=cast( conv( @v, 10, 2 ) as unsigned )
So two issues here
- I think you are getting true for the column, because that is the default value,
- and Connector/NET is not using the pattern "set Flagged=cast( conv( @v, 10, 2 ) as unsigned )" for any bit columns, and thats a Connector/NET bug.
as workaround for now, I suggest you use a "load data" sentence with MySqlCommand instead of MySqlBulkLoader (which internally uses load data, but cannot be customized).