Buddoda! - 30 August 2010 07:38 AM
ERROR
The outfile.txt has varchar values padded to 1000 characters ...
buddoda!
I ran your test scenario on IEE 3.4.2 GA on CentOS 5 and didn’t receive the same results. In my output file the VARCHAR fields were only 4 bytes long and enclosed in double-quotes. Here is my session information for comparison:
[root@localhost tmp]# more tempin.txt
test;test;1
test;test;2
test;test;3
mysql> CREATE TABLE temp_27(
-> C_1 VARCHAR(1000),
-> C_2 VARCHAR(1000),
-> C_3 BIGINT) ENGINE=BRIGHTHOUSE;
Query OK, 0 rows affected (0.18 sec)
mysql> set @bh_dataformat='txt_variable';
Query OK, 0 rows affected (0.00 sec)
mysql> LOAD DATA INFILE '/tmp/tempin.txt' INTO TABLE temp_27 FIELDS TERMINATED BY ';' ENCLOSED BY '';
Query OK, 3 rows affected (1.76 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from temp_27;
+------+------+------+
| C_1 | C_2 | C_3 |
+------+------+------+
| test | test | 1 |
| test | test | 2 |
| test | test | 3 |
+------+------+------+
3 rows in set (0.00 sec)
mysql> select length(C_1) from temp_27 where C_3 = 1;
+-------------+
| length(C_1) |
+-------------+
| 4 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM temp_27 INTO OUTFILE '/tmp/tempout.txt' FIELDS TERMINATED BY ';' ENCLOSED BY '';
Query OK, 3 rows affected (0.01 sec)
[root@localhost tmp]# more tempout.txt
"test";"test";1
"test";"test";2
"test";"test";3
One thing I notice in your post, and this might just be a typographical or copy-and-paste error, is that the LOAD statement says FIELDS TERMINATED BY ‘^’ while the data file’s values are actually separated by semi-colons (;). I don’t know if that’s a possible issue because when I use the same syntax, I receive:
mysql> LOAD DATA INFILE '/tmp/tempin.txt' INTO TABLE temp_27 FIELDS TERMINATED BY '^' OPTIONALLY ENCLOSED BY '' ESCAPED BY '' LINES TERMINATED BY '\n' (C_1,C_2,C_3);
ERROR 2 (HY000): Wrong data or column definition. Row: 1, field: 1.
I suggest you re-try using the syntax I have used above and let us know how it works out.
PS Also review Chapter 6, Character Set Support, page 40 of the User Guide, section on Padding for more information on how Infobright handles padding in querying and loading.
