Importing 'BLOB' fields into MySQL

[Brodie_McRae]

Hello. If you want the meat, go to "The Problem"

Background
===
I'll try to be a straightforward as possible. I am migrating a SQL 7 database into MySQL. Everything works except for one table with a MEDIUMBLOB type.

To explain: this database is a bunch of products. One particular table has the product ID & image (stored within the DB as binary).

SQL 7 refuses, by ODBC, to transfer these fields into the MySQL database either citing that blob entries can not be inserted using a query or that the source buffer's too small.

For the time being I've decided to export the table into a text file.


The Problem
===
I have a text file that is tab delimited, rows separated by \n, optionally " contained. One of the fields in the dataset is binary. SQL 7 has it stored as one big string of hexidecimal code.

If, for example, I put in db.tbl.image=0x[string], it will convert the string to hex and insert the binary code properly. ([string] would be any one of the strings from a given row in the file)

What I want to know is, how can I load a whole table, including this one troublesome binary filed (stored as HEX) into the database given that I have about 2000 rows?

/*So far I can load the file into the database, but I cannot get MySQL to realize that the hex codes are not text. MySQL just assumes the field is text and I can't get past this. */

Alternatively
===
How can I transfer 2000 binary fields from SQL 7 to MySQL without it giving me one of the following messages:

-Query-based insertion of Blob entries is not supported
-Error at source: buffer not large enough

(exact error messages can be provided)

Any suggestions??? I have no idea how to solve this, it's killing me..

[Goof]

If there isn't a way to natively do it, you could always just write a script that takes binary chunks out of one table in one datasource and pushes them in the other. But that would be a last straw type of solution.

Goof

[Brodie_McRae]

This is how I eventually solved the problem:

1. Import everything into MySQL (including hex code)
-Row 1, for example, now has a field imagedata with
a bunch of "FFDA...." hex

2. Increase PHP timeout to 5 mins in php.ini

3. Create PHP script:
a) select id, imagedata from imagetable;
b) while ($row = mysql_fetch_array($query))
{read hex, convert to bin, plug back into imagedata}

In other words, run a php script to go through the database converting the hex to binary. The trick was that although mysql couldn't do the conversion, I could use something else that could. Just had to learn PHP. I like it.