Orafusion.com: Saving Images in Oracle

Orafusion.com: Saving Images in Oracle


Here's a great tutorial on working with Oracle and images in the database.


Saving Images in Oracle:


Many applications require that images be saved to the database. One common example is an HR database, which stores photographs along with other employee details. There seems to be a fair amount of discussion as to the best way to do this ColdFusion. Some folks claim that one has to resort Java, others say that PL/SQL (using Oracle's built in DBMS_LOB package) is the way to go. Below I describe a technique that requires only CFML.

The code I describe here saves an image to Oracle, and then retrieves and saves it to another location on the CF server filesystem.

To get started, create a test table to store the image:


create table clob_test(
id number,
clob_column clob
primary key (id))


Here I'm going to assume that the primary key "id" is auto-generated by an Oracle sequence and a trigger (details on how to do this are available here). The column that will hold our image is of type CLOB - Character Large OBject. At this point, the astute reader is going to ask, "but isn't the image in binary format?" Yes indeed it is, but the problem is that ColdFusion's internal type conversion mechanism makes it difficult to pass a binary object directly to the database. To get around this we'll convert the image to a character string before saving it, and reconvert it to binary on retrieval.

Now that our table is created, let's move on to the CFML. As you'll see it's pretty straightforward. First we read the image using CFFILE:


file = "c:\temp\image.jpg"
variable="image_blob">


Next we save the image to the database. Note the use of CFQUERYPARAM and the conversion to character type using the CF function toBase64():


datasource="yourDSN"
username="yourUsername"
password="yourPwd">

insert into
clob_test
values
( CFSQLType="CF_SQL_CLOB">)





The image is now in the database, albeit as a character string. To verify this, fire up SQLPlus or your favourite Oracle query tool and issue the following SQL:


select
dbms_lob.getlength(clob_column)
from
clob_column


The result (assuming that you have access to the built-in package DBMS_LOB)is in bytes, and should show an image size fairly close to that of the original. It won't be exact match, of course, because of the conversion.

The image retrieval is just as straightforward:


datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
from
clob_test
where id=1




Lastly, we save the image to a different location on the CF server file system, rembering to convert it back to binary using the appropriate CF function:


file = "c:\image.jpg"
output=#toBinary(select_clob.clob_column[1])#
addnewline="no">


All that remains is to check the integrity of the image by viewing it in your favourite viewer.

Before I close this article, there is one issue that I should cover - which JDBC drivers work and which don't. The code should work with the Macromedia supplied (Datadirect) driver and the Oracle OCI driver. It does NOT work with the Oracle thin driver. If you are new to Oracle and CF, please check here for detailed steps on setting up connectivity between CFMX and Oracle.

That's it, I hope this code works in your particular situation. If you find other ways to do this, I'd appreciate your letting me know.