|
|
-
Saving Images in Oracle:
Note: The Oracle-related material presented here is largely ColdFusion-version independent. However,
please note that most of the ColdFusion code
has been developed and tested on older versions of ColdFusion (4.0 through 6.0).
Consequently, some CFML code snippets may need to be modified to work optimally on more recent versions of the
product.
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:
<cffile action = "readbinary"
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():
<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">
insert into
clob_test
values
(<cfqueryparam value=#toBase64(image_blob)#
CFSQLType="CF_SQL_CLOB">)
</cfquery>
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:
<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">
select
from
clob_test
where id=1
</cfquery>
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:
<cffile action = "write"
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.
Back to the top
|
