msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Writing/Reading images to/from DB

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


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


Reader's Comments

  1. |

    can you help me with my problem.. i need to search a string form a word file, i have a multiple msword file and i need to search wich file contains a specfic string for me to know what file need only for editing.. im using a coldfusion.. please help.

  2. |

    Well, i sure pray this tutorial work for me, coz’ i’m yet to try it, i just started in coldfusion and i’m really doing well and now i’m about to build one of the largetst site in my country using coldfusion. To all using it, its a great cgi script so simple and cool, I’ve been using j2ee for quit sometime and i know this coldfution is good.Good luck coding.

  3. |

    thanks, anyway.. i had searched the verity search functionality of cf, and it works perfectly.

  4. |

    I am try to insert file into database, I did follow your code, after I insert file and have error below. The error occurred in cfsqltype=”cf_sql_blob”. Can you help me? Thanks,