Stefano, I'm copying this to the mailing list. I think it is a lot better if we have discussions of this kind on the mailing list so that others can also learn from them, either now or in the future via the mailing list archive.
I'm glad to hear that you solved your data loading problem. You've chosen an unusual solution. I'm not sure why it is important that the size of the column is exactly 595 characters but that is up to you. As I understand it, the BLOB and TEXT column types only store the amount of data that you give them; if I am right about that, your BLOB or TEXT column will only contain 595 characters of data, not 595 characters of data plus thousands of trailing blanks. (If I am misreading this, I hope someone else will jump in and correct me!) That means that your data will be in one field and you you won't have to concatenate it. With regards to your second problem, I could certainly do what you describe in a Java program - provided the MS Word documents are small enough to fit inside a BLOB! - but I'm not so sure how to do it without using a programming language. I just had a look at LOAD DATA INFILE to see if it can handle BLOBs but it says explicitly that it *CANNOT* handle BLOBs. Since the mysqlimport utility is just a front-end to LOAD DATA INFILE, I assume it has the same limitation. However, this page http://dev.mysql.com/doc/mysql/en/blob.html has some discussion of techniques to load BLOBs into tables. See the user comments in the bottom part of the page. All of them seem to use programming languages though. The user comments also raise an important issue: sometimes, it is better to store only a URL pointing to the document in the database and keep the actual document in the file system rather than the database. They suggest some guidelines to help you decide which approach is best for you. If you haven't considered these guidelines, you should. It might turn out that it is better for you to leave the documents outside of MySQL. Sorry I couldn't be more help. Rhino ----- Original Message ----- From: <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Sent: Monday, April 04, 2005 12:24 PM Subject: Re: Load data infile and text fields Rhino, many thanks for your answer! My problem is that I need a filed with precision for a field of exactly 595 characters! Only text field type with precision is the char type but its limit is 256 char. I've tried with text type, but precision were been ignored and my sql silently truncate it at 256 value. I solved my problem creating a temp table with my field splitted in three char fields: 250+250+95, then I've rebuild each line with the function concat()! Now I've another problem: after I've imported all txt files with a fully automated query, I need to import into a table with a blob field, a lot of little msword documents. Each record should get a single word file. I'd like to write a query (I hope without using api as php or other languages) that imports automatically all .doc files stored in a fixed directory. Have any idea? Thanks a lot Stefano >-- Messaggio originale -- >From: "Rhino" <[EMAIL PROTECTED]> >To: <[EMAIL PROTECTED]>, > <mysql@lists.mysql.com> >Subject: Re: Load data infile and text fields >Date: Sat, 2 Apr 2005 09:44:07 -0500 > > >Stefano, > >The behaviour you are describing is normal, assuming that the column in your >MySQL table is defined as CHAR(255) or VARCHAR(255). > >You didn't say which version of MySQL you are using. However, unless you >are >using MySQL 5.0.3 or later, 255 is the largest size available for a CHAR >or >VARCHAR column. (Starting with version 5.0.3, the maximum size of a VARCHAR >is 65,536.) > >Assuming you are not on 5.0.3 or later, you should change your table >definition to use one of the BLOB or TEXT datatypes: TINYBLOB, BLOB, >MEDIUMBLOB, LONGBLOB or TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT. You should >look at the definitions of these column types in the manual - >http://dev.mysql.com/doc/mysql/en/storage-requirements.html - to see which >one best >suits your requirements; only you know the largest value that you want to >store in the column. Basically: >- TINYBLOB and TINYTEXT are for values less than 256 characters long (which >is no better than CHAR or VARCHAR in your case) >- BLOB and TEXT are for values less than 65536 characters long >- MEDIUMBLOB and MEDIUMTEXT are for values less than 16777216 characters >long >- LONGBLOB and LONGTEXT are for values less than 4294967296 characters long > >Rhino > >----- Original Message ----- >From: <[EMAIL PROTECTED]> >To: <mysql@lists.mysql.com> >Sent: Saturday, April 02, 2005 6:51 AM >Subject: Load data infile and text fields > > >First of all I hope you can be patient for my english.... >I'm working with data import into mysql from a txt file. I'm using LOAD >DATA INFILE >command but I cannot correctly import a text column of 595 characters. >I receive this (very large) file from an external organization and this >file is made >without separators bitween fields. I know only the exact lenght of each >field. All is fine for fields < of 256 char, but I cannot import this text >field of 595 characters. It's imported truncated at 255th character. >Help me please! >Stefano (osso) > > > > > >-- >No virus found in this outgoing message. >Checked by AVG Anti-Virus. >Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 01/04/2005 > -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 01/04/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 01/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
