7 Replies Latest reply on Sep 3, 2009 11:11 AM by JasonH

    ODBC 4096 byte limit



      ODBC 4096 byte limit

      Your post

      Ok, I'm stuck. I setup ODBC and everything works great. I'm pulling info through PHP and for the most part everything is fine. As a side note, % wildcard searches are incredibly slow, but that's not why I'm here.


      I have tried everything I can find but I can't get more than 4k from a picture container.  I have tried a dozen configurations but here's the latest:


      php.ini - set odbc.defaultlrl = 999999


      $sql = 'select getas("Items"."Picture Container",\'JPEG\') from "Items Database" where "Items Database"."Part Number" = \'123456\' '; $rs=odbc_exec($conn,$sql); odbc_binmode ($rs, ODBC_BINMODE_PASSTHRU); odbc_longreadlen ($rs, 0); while (odbc_fetch_row($rs))   {       header("Content-type: image/jpeg"); odbc_result($rs, 1);   }



      And no matter what, I only get 4k of the image.


      Please, does anyone have an idea on how to fix this? I'll try anything! Thanks

        • 1. Re: ODBC 4096 byte limit
             Doing a quick Google search, seems to indicate that this is a limitation of most SQL databases.  I'm ust not sure if it has to do with the database, ODBC driver (client or server), or the calling application.
          • 2. Re: ODBC 4096 byte limit

            Have you checked that the PHP setting is indeed taking effect?


            This is not an ODBC answer,  but the quickest way to fix this is to use the XML web services and grab the container data out that way. This actually work out quite well unless you're dealing with like 20GB of data.

            • 3. Re: ODBC 4096 byte limit

              This is an issue with the underlying odbc.c client library that quite a significant number of programs link to.  The odbc.c API uses a buffer binding for each field returned from the database, with not any pleasant way of dynamically allocating the size.  There is also a size allocation limitation with c. 


              Since the interfacing layer such as PHP does not know what you're doing, it allocate a fix amount of buffer large enough to store just about anything.  You can either make a per row call to odbc to return 1 column of 1 row at a time, then clean it up and only return the needed data.  This let you control the memory issue better,  but at the cost of massive odbc overhead.  Or you can specify an array of buffers,  where it's a fixed value of something like 4KB X 256 Fields  and return an entire row at a time.  This is much much much faster, but memory allocation become a big problem.


              I had to extract about 20GB worth of records out of a FM db using the odbc c client lib on mac,  it was not fun.  Basically have to tweak your program at the C level odbc calls to run something like 

              SELECT container_name FROM mooo WHERE fm_record_id = 'X'  so that the query would only return a single specific field, which you know for a fact will be a big long binary blob.  Then you have to malloc a large enough chunk of memory to store the data stream.The memory allocated to this very unique calling process was something like 64MB in my case, and great care was taken to clean it up very quickly afterward or the system would very quickly crash.


              In a high level abstraction like php,  you can't really let the underlying odbc allocate too much memory.  If the OP is setting a 1M allocation binding, that is per field,  so a 10 fields per row would take 10MB.  If he is returning any significant number of rows, the mem allocation is huge, 100 rows would be 1 GB.  This is why many higher level abstractions doesn't exactly let you go wild in specifying buffer length.  Most also fetches entire row to save I/O,  so it doesn't really let you specify more than a managable amount of buffer per field.  


              Some has other specific methods that let you set the buffer length to be large on a circumstancial basis to get large blobs,  but a lot don't.

              • 4. Re: ODBC 4096 byte limit
                   FluffyBear - Thank you for your detailed explanation. I'm starting to think it might not be an ODBC issue. I used a third-party tool to transfer everything out of Filemaker and into MySQL via the odbc link and when I looked at the image column in MySQL it was all there! Maybe it's a php thing but I have exhausted every option I've found and verified all of the settings are being applied. Really stuck on this one.
                • 5. Re: ODBC 4096 byte limit

                  The ODBC link work,  the problem is in what calls you make to the ODBC API that actually call the FM ODBC adapter.  PHP doesn't let you control this.  The 3rd party tool would need to do what I did and do ODBC API calls directly to transfer.  The 4KB limit in PHP let you go lower only if I remember right.  So you can reduce it, but it's a hard coded upper limit to prevent you from crashing PHP due to memory allocation.


                  I work mostly with PHP too,  go with the XML export option for containers, you'll have far less headaches on migration.   It take a little bit more time to transfer, but well worth it for the simplicity.

                  • 6. Re: ODBC 4096 byte limit
                       What tool did you use to extract the images from the FileMaker database?
                    • 7. Re: ODBC 4096 byte limit
                         "Flyspeed DB Migrate to MySQL" - http://www.activedbsoft.com/overview-migrate-mysql.html