4 Replies Latest reply on Sep 24, 2014 11:26 AM by webmandman8

    [Coldfusion 10] convert SQL image data type to pdf file

    webmandman8

      The data is of image type in my database table. How do I save this data as a pdf file. The data in the table is already pdf type 0x151C240002000000080008001400... I tried select boxid, datapdf from records where datapdf is not null FileWrite("data.pdf", records.datapdf[1]); It did save the file as a pdf, but upon opening the file is said "it is either not a supported file type or because the file has been damaged".

        • 1. Re: [Coldfusion 10] convert SQL image data type to pdf file
          Carl Von Stetten Adobe Community Professional & MVP

          By "image type", do you just mean that it's a SQL image column (rather than varchar() or int or other column type)?  If so, you still have to know what the data being stored in there is (e.g. TIF, JPG, PDF, etc.).  If the content in that column didn't come from a valid PDF, then you can't write it to a file and expect it to be a valid PDF.

           

          -Carl V.

          • 2. Re: [Coldfusion 10] convert SQL image data type to pdf file
            webmandman8 Level 1

            Perhaps you should know that I imported this database data from an access database. In Access I can double click the cell and it opens up the PDF. In the SQL column the data looks like this:  0x151C390002000001100140014002500FFFFFFFF... Yes, it is a SQL image column and I'm pretty sure its pdf data.

            • 3. Re: [Coldfusion 10] convert SQL image data type to pdf file
              Carl Von Stetten Adobe Community Professional & MVP

              Maybe, maybe not.  Access unfortunately has a way of screwing up binary (blob) data that is stored in it by adding a wrapper around the data.  I've run across this before, but I don't recall the specifics of what was happening - I think it had something to do with OLE objects versus actual image file data (JPG photos).  I ended up having to have the contractor who was putting data in go back and reinsert the images correctly.

               

              In my case, some of the JPGs were loaded correctly (and migrated into SQL Server correctly), but others were not.  You may have to resort to opening the records in Access and saving the images out to files, then running a separate process to load the images into your database system (you haven't specified which one you are using).

               

              -Carl V.

              • 4. Re: [Coldfusion 10] convert SQL image data type to pdf file
                webmandman8 Level 1

                Thanks for the quick reply Carl. I believe it is exactly what is wrong with the data. Googling this issue I came across php scripts that can extract the data correctly (I guess removing the unwanted access wrapper stuff), but I don't have the patience for that right now. I don't even run php at work and I've never ran queries against Access DB with Coldfusion let alone PHP. Unfortunate for me that the access db is somewhat corrupted because I can only open one PDF at a time. I have to close and reopen the file before extract another PDF. Oh brother. I did find a shortcut to copying the PDFs. When I click on the PDF cell in Access DB - a temp copy is created in AppData/Local/Temp, which I can then copy from. I just have to click on the next cell and copy the next temp file. Only one temp file exists - everytime I click on a new cell it replaces the previous temp file. LOL.