MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL Text/Blob Processing

with 9 comments

This page demonstrates how to upload, store, and manage Text (Character Large Data Streams) and BLOBs (Binary Large Objects). It provides MySQL equivalent instructions to those for manaing LOBs in an Oracle database. As covered in Chapter 8 in my Oracle Database 11g PL/SQL Programming book.

Before you begin these steps, you should have already installed Zend Server Community Edition. If you haven’t done so, please click here for instructions.

If you find any problems, please let me know. I’ll be happy to fix them.

Written by maclochlainn

July 9th, 2009 at 2:35 am

Posted in Uncategorized

9 Responses to 'MySQL Text/Blob Processing'

Subscribe to comments with RSS or TrackBack to 'MySQL Text/Blob Processing'.

  1. I found an error in the select statement troubleshooting area, it’s missing commas, and you probably want item_desc where you have item_clob.

    Michael

    9 Jul 09 at 1:51 pm

  2. Thanks, I’ve updated the blog page with the corrections. :-)

    maclochlainn

    9 Jul 09 at 2:27 pm

  3. For those revisiting the blog page, the code zip file is updated and you should use it rather than the originally posted code.

    maclochlainn

    10 Jul 09 at 12:22 am

  4. Somebody was nervous about my zip files. I completely understand that. So, I put the full code in a nested folding section.

    maclochlainn

    25 Jul 09 at 12:08 am

  5. I’m having a problem getting the data back out of the database. I do the same bind and fetch but when I do a var_dump of the variable that I bound it into I only get the first 256 characters of the blob out.

    Nevins

    17 Sep 09 at 12:46 pm

  6. I upgraded to the newest dev snapshot of php and if fixed this problem.

    Nevins

    18 Sep 09 at 8:09 am

  7. Steve’s re-posting content from Andrey at the URL below.

    If you select LOBs use the following order of execution or you risk mysqli allocating more memory that actually used

    1. prepare()
    2. execute()
    3. store_result()
    4. bind_result()

    If you skip #3, or exchange #3 with #4 then mysqli will allocate memory for the maximal length of the column. That’s 255 for TINYBLOB, 64K for BLOB (still OK), 16MB for MEDIUMBLOB – quite a lot, and 4G for LONGBLOB (good if you have so much memory). Queries which use the correct order are a bit slower when there is a LOB but this is the price of not having memory exhaustion in seconds.

    http://php.net/manual/en/mysqli-stmt.bind-result.php

    Steve Williamson

    4 Dec 09 at 3:05 pm

  8. Two quick items:

    Steve’s right but I’ve opted to leave the code for smaller memory footprints.

    The code examples are now completely in text display are compatible with PHP 5.3, but the zip files aren’t compatible with PHP 5.3 yet. I’ll update this when I fix the zip files.

    maclochlainn

    27 Feb 10 at 1:36 pm

  9. […] generic PHP/MySQL solution […]

Leave a Reply