SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jul 2002
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Importing 'BLOB' fields into MySQL

    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..
    Last edited by Brodie_McRae; Jul 17, 2002 at 11:29.

  2. #2
    SitePoint Wizard Goof's Avatar
    Join Date
    Feb 2001
    Location
    Pittsburgh, PA
    Posts
    1,154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Nathan Rutman
    A slightly offbeat creative.

  3. #3
    SitePoint Member
    Join Date
    Jul 2002
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking The solution

    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •