I cannot update the blobs correctly with mysql.

I exported 3 rows from db2 to mysql database.
There are blobs in the database.
db2 display the 3 rows as
db2 'select cast(type_blob as varchar(200)) from ilwdtypes'

1




-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
x'969696969696969696969696969696969696969696969696 96969696969696969696969696969696969696969696969696 96969696969696969696969696969696969696969696969696 969696969696'


x'979797979797979797979797979797979797979797979797 97979797979797979797979797979797979797979797979797 97979797979797979797979797979797979797979797979797 9797979797979797979797'


x'989898989898989898989898989898989898989898989898 98989898989898989898989898989898989898989898989898 98989898989898989898989898989898989898989898989898 98989898989898989898989898989898'


When I want to add the blob objects back to mysql after the data was
imported,
mysql> update ilwdtypes set type_blob=load_file('./lwdtypes.del.001.lob');

I ended up having the entire blob in each row of the column
mysql> select hex(type_blob) from ilwdtypes where type_char_s =
"1216412567";
|
96969696969696969696969696969696969696969696969696 96969696969696969696969696969696969696969696969696 96969696969696969696969696969696969696969696969696 9696969696747970655F636C6F629797979797979797979797 97979797979797979797979797979797979797979797979797 97979797979797979797979797979797979797979797979797 97979797979797979797979797979797979797979797979774 7970655F636C6F629898989898989898989898989898989898 98989898989898989898989898989898989898989898989898 98989898989898989898989898989898989898989898989898 98989898989898989898989898989898989898989898987479 70655F636C6F62
|

How can I get mysql to update each blob properly to match the ones in db2 ?

db2 version 9.1.5 on 32 bit Linux
mysql on 32 bit linux Server version: 5.0.45 Source distribution

Thanks.

Mary

I found one solution :
I have to split up the lob file from db2 using the offset and size of
each lob into individual files and update each of the rows with the
split load file.
Tedious but works.

Mary Lei wrote:
> I cannot update the blobs correctly with mysql.
>
> I exported 3 rows from db2 to mysql database.
> There are blobs in the database.
> db2 display the 3 rows as
> db2 'select cast(type_blob as varchar(200)) from ilwdtypes'
>
> 1
>
>
>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> x'969696969696969696969696969696969696969696969696 96969696969696969696969696969696969696969696969696 96969696969696969696969696969696969696969696969696 969696969696'
>
>
> x'979797979797979797979797979797979797979797979797 97979797979797979797979797979797979797979797979797 97979797979797979797979797979797979797979797979797 9797979797979797979797'
>
>
> x'989898989898989898989898989898989898989898989898 98989898989898989898989898989898989898989898989898 98989898989898989898989898989898989898989898989898 98989898989898989898989898989898'
>
>
> When I want to add the blob objects back to mysql after the data was
> imported,
> mysql> update ilwdtypes set type_blob=load_file('./lwdtypes.del.001.lob');
>
> I ended up having the entire blob in each row of the column
> mysql> select hex(type_blob) from ilwdtypes where type_char_s =
> "1216412567";
> |
> 96969696969696969696969696969696969696969696969696 96969696969696969696969696969696969696969696969696 96969696969696969696969696969696969696969696969696 9696969696747970655F636C6F629797979797979797979797 97979797979797979797979797979797979797979797979797 97979797979797979797979797979797979797979797979797 97979797979797979797979797979797979797979797979774 7970655F636C6F629898989898989898989898989898989898 98989898989898989898989898989898989898989898989898 98989898989898989898989898989898989898989898989898 98989898989898989898989898989898989898989898987479 70655F636C6F62
> |
>
> How can I get mysql to update each blob properly to match the ones in db2 ?
>
> db2 version 9.1.5 on 32 bit Linux
> mysql on 32 bit linux Server version: 5.0.45 Source distribution
>
> Thanks.
>
> Mary

Mary Lei (mlei@ligo.caltech.edu) writes:
> I found one solution :
> I have to split up the lob file from db2 using the offset and size of
> each lob into individual files and update each of the rows with the
> split load file.
> Tedious but works.

If you post to comp.databases.mysql, you might learn a better solution.
Since this newsgroup is a about different product, your prospects are
somewhat bleak here.o


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx