Writing/Reading images to/from DB

Tweet

In ColdFusion it is very simple and straight forward to store and retrieve images to/from an Oracle database. Here is an example of how you can do it:

First, let’s create an Oracle table that will hold the images:

create table test_img (
    id number,
    image blob
)
/

We are going to store the image in the database in its binary format, that’s why the image column has a data type of blob, or “binary large object”.

Then, in your ColdFusion template, let’s read an image stored on the server:

<cffile 
    action = "readbinary" 
    file = "c:\temp\image_in.jpg" 
    variable="image_blob">

image_blob contains the binary representation of the image. Now, let’s store the image in the database:

<cfquery name="q" datasource="yourDSN">
    insert into test_img (id, image)
    values (
        1, 
        <cfqueryparam 
            value="#image_blob#" 
            cfsqltype="cf_sql_blob">
    )
</cfquery>

You can double check that the image was inserted into the database by issuing the following SQL:

select 
  id,
  dbms_lob.getlength(image) image_size_bytes 
from test_img

Now, back to the ColdFusion template. Let’s read the image back from the database:

<cfquery name="q" datasource="yourDSN">
   select image
   from test_img
   where id = 1
</cfquery>

And write it back to a file on the server:

<cffile action = "write"
    file = "c:\temp\image_out.jpg"
    output=#q.image#
    addnewline="no"> 

And voila!

Note: I have tested the above in CFMX 7 and Oracle 8.1.7

Related articles:

• Saving/Downloading files to/from Oracle using ColdFusion
• Oracle PLSQL in CFQUERY
• That Fine Code

Filed in ColdFusion, Tips on 21 Jul 05 | Tags: database, image

---