Home > Archive > MySQL ODBC Connector > February 2006 > LOAD DATA INFILE and BIT columns









You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

 

Author LOAD DATA INFILE and BIT columns
Julie Kelner

2006-02-25, 9:44 am

------ =_NextPart_000_0044_
01C63980.B82A12C0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

Hi. I'm using MySQL 5.0.18, and I'm trying to use LOAD DATA INFILE into =
tables that have BIT(8) columns. No matter
what format I use, the result is not what I expect (see example below.) =
Anyone know how to properly format the data for loading into a BIT =
column? Thanks!


$ cat /tmp/bit_test.txt
01010101
2
b'010'
b\'010\'
0x2
000000000000000002


mysql> create table bit_test (b bit(8));
Query OK, 0 rows affected (0.01 sec)

mysql> load data infile '/tmp/bit_test.txt' into table bit_test;
Query OK, 6 rows affected, 3 warnings (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 3

mysql> select bin(b+0) from bit_test;
+----------+
| bin(b+0) |
+----------+
| 11111111 |
| 110010 |
| 11111111 |
| 11111111 |
| 11111111 |
| 11111111 |
+----------+
6 rows in set (0.00 sec)



Thanks!

------ =_NextPart_000_0044_
01C63980.B82A12C0--

sheeri kritzer

2006-02-28, 8:28 pm

Hi Julie,

If you notice after your import, you have 3 warnings. This intrigued
me, so I created a test case (also running 5.0.18 standard):

create table bit_test (b bit(8));

cat /tmp/bit_test.txt

01010101
2
b'010'
b'010'
0x2
000000000000000002

mysql> load data infile '/tmp/bit_test.txt' into table bit_test;

Query OK, 6 rows affected, 5 warnings (0.05 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 5
---------------------------------------------------------------------------=
---------------------------------------------

Hrm. I got 5 warnings; you'd only gotten 2. Weird!

---------------------------------------------------------------------------=
---------------------------------------------
mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'b' at row 1 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 3 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 4 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 5 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 6 |
+---------+------+-----------------------------------------------------+
5 rows in set (0.02 sec)

---------------------------------------------------------------------------=
---------------------------------------------
What this says to me is that the values were too big, for all but row 2.
---------------------------------------------------------------------------=
---------------------------------------------
mysql> select bin(b+0) from bit_test;
+----------+
| bin(b+0) |
+----------+
| 11111111 |
| 110010 |
| 11111111 |
| 11111111 |
| 11111111 |
| 11111111 |
+----------+
6 rows in set (0.05 sec)
---------------------------------------------------------------------------=
---------------------------------------------
so the 11111111 values make sense -- the values were larger than the
largest value, so it truncated it to the largest value. But why, when
I insert a 2, does it use 11010 instead of 10?

Let's test:
---------------------------------------------------------------------------=
---------------------------------------------

mysql> insert into bit_test VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> select bin(b+0) from bit_test;
+----------+
| bin(b+0) |
+----------+
| 11111111 |
| 110010 |
| 11111111 |
| 11111111 |
| 11111111 |
| 11111111 |
| 10 |
+----------+
7 rows in set (0.00 sec)

---------------------------------------------------------------------------=
---------------------------------------------
That makes sense! the last value is 10, which makes sense for a
binary value of 2. On a hunch, I tried to see what happened if it
treated 2 as a string, not an integer:
---------------------------------------------------------------------------=
---------------------------------------------

mysql> insert into bit_test VALUES ('2');
Query OK, 1 row affected (0.00 sec)

mysql> select bin(b+0) from bit_test;
+----------+
| bin(b+0) |
+----------+
| 11111111 |
| 110010 |
| 11111111 |
| 11111111 |
| 11111111 |
| 11111111 |
| 10 |
| 110010 |
+----------+
8 rows in set (0.01 sec)

---------------------------------------------------------------------------=
---------------------------------------------
Aha! the culprit -- it was thinking that the "2" in the file was a
string, not an int.
---------------------------------------------------------------------------=
---------------------------------------------

Hope this helped,

-Sheeri

On 2/24/06, Julie Kelner <biz@rubymuse.com> wrote:
> Hi. I'm using MySQL 5.0.18, and I'm trying to use LOAD DATA INFILE into t=
ables that have BIT(8) columns. No matter
> what format I use, the result is not what I expect (see example below.) A=
nyone know how to properly format the data for loading into a BIT column? T=
hanks!
>
>
> $ cat /tmp/bit_test.txt
> 01010101
> 2
> b'010'
> b'010'
> 0x2
> 000000000000000002
>
>
> mysql> create table bit_test (b bit(8));
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> load data infile '/tmp/bit_test.txt' into table bit_test;
> Query OK, 6 rows affected, 3 warnings (0.00 sec)
> Records: 6 Deleted: 0 Skipped: 0 Warnings: 3
>
> mysql> select bin(b+0) from bit_test;
> +----------+
> | bin(b+0) |
> +----------+
> | 11111111 |
> | 110010 |
> | 11111111 |
> | 11111111 |
> | 11111111 |
> | 11111111 |
> +----------+
> 6 rows in set (0.00 sec)
>
>
>
> Thanks!
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Julie Kelner

2006-02-28, 8:28 pm

Sheeri,

Wow. That was my first forum email and I thought it would go unnoticed. I
sure was wrong.

You are exactly right, because apparently with LOAD DATA INFILE, everything
in the file is treated as a string. I'm using PHP to create the text file,
so I tried PHP's pack() function to write '2' as binary data. And...it
worked!

Thanks so much for your input.
~ Julie

----- Original Message -----
From: "sheeri kritzer" <awfief@gmail.com>
To: "Julie Kelner" <biz@rubymuse.com>
Cc: <mysql@lists.mysql.com>
Sent: Monday, February 27, 2006 12:50 PM
Subject: Re: LOAD DATA INFILE and BIT columns


Hi Julie,

If you notice after your import, you have 3 warnings. This intrigued
me, so I created a test case (also running 5.0.18 standard):

create table bit_test (b bit(8));

cat /tmp/bit_test.txt

01010101
2
b'010'
b'010'
0x2
000000000000000002

mysql> load data infile '/tmp/bit_test.txt' into table bit_test;

Query OK, 6 rows affected, 5 warnings (0.05 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 5
------------------------------------------------------------------------------------------------------------------------

Hrm. I got 5 warnings; you'd only gotten 2. Weird!

------------------------------------------------------------------------------------------------------------------------
mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'b' at row 1 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 3 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 4 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 5 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 6 |
+---------+------+-----------------------------------------------------+
5 rows in set (0.02 sec)

------------------------------------------------------------------------------------------------------------------------
What this says to me is that the values were too big, for all but row 2.
------------------------------------------------------------------------------------------------------------------------
mysql> select bin(b+0) from bit_test;
+----------+
| bin(b+0) |
+----------+
| 11111111 |
| 110010 |
| 11111111 |
| 11111111 |
| 11111111 |
| 11111111 |
+----------+
6 rows in set (0.05 sec)
------------------------------------------------------------------------------------------------------------------------
so the 11111111 values make sense -- the values were larger than the
largest value, so it truncated it to the largest value. But why, when
I insert a 2, does it use 11010 instead of 10?

Let's test:
------------------------------------------------------------------------------------------------------------------------

mysql> insert into bit_test VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> select bin(b+0) from bit_test;
+----------+
| bin(b+0) |
+----------+
| 11111111 |
| 110010 |
| 11111111 |
| 11111111 |
| 11111111 |
| 11111111 |
| 10 |
+----------+
7 rows in set (0.00 sec)

------------------------------------------------------------------------------------------------------------------------
That makes sense! the last value is 10, which makes sense for a
binary value of 2. On a hunch, I tried to see what happened if it
treated 2 as a string, not an integer:
------------------------------------------------------------------------------------------------------------------------

mysql> insert into bit_test VALUES ('2');
Query OK, 1 row affected (0.00 sec)

mysql> select bin(b+0) from bit_test;
+----------+
| bin(b+0) |
+----------+
| 11111111 |
| 110010 |
| 11111111 |
| 11111111 |
| 11111111 |
| 11111111 |
| 10 |
| 110010 |
+----------+
8 rows in set (0.01 sec)

------------------------------------------------------------------------------------------------------------------------
Aha! the culprit -- it was thinking that the "2" in the file was a
string, not an int.
------------------------------------------------------------------------------------------------------------------------

Hope this helped,

-Sheeri

On 2/24/06, Julie Kelner <biz@rubymuse.com> wrote:
> Hi. I'm using MySQL 5.0.18, and I'm trying to use LOAD DATA INFILE into
> tables that have BIT(8) columns. No matter
> what format I use, the result is not what I expect (see example below.)
> Anyone know how to properly format the data for loading into a BIT column?
> Thanks!
>
>
> $ cat /tmp/bit_test.txt
> 01010101
> 2
> b'010'
> b'010'
> 0x2
> 000000000000000002
>
>
> mysql> create table bit_test (b bit(8));
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> load data infile '/tmp/bit_test.txt' into table bit_test;
> Query OK, 6 rows affected, 3 warnings (0.00 sec)
> Records: 6 Deleted: 0 Skipped: 0 Warnings: 3
>
> mysql> select bin(b+0) from bit_test;
> +----------+
> | bin(b+0) |
> +----------+
> | 11111111 |
> | 110010 |
> | 11111111 |
> | 11111111 |
> | 11111111 |
> | 11111111 |
> +----------+
> 6 rows in set (0.00 sec)
>
>
>
> Thanks!
>
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links





Also available: Server administration forum archive | Web Design forum archive | Software forum archive | Hardware reviews archive | Programming forum archive

Copyright 2009 droptable.com