• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

How do insert a Document/Image into Oracle?

I posted this on Allaire's website, and haven't gotten a response yet, maybe someone here can help...

We are using Oracle 7.3 and COLDFUSION 4.5 SP2. I am having problems trying to store MS Word docs into a LONG RAW field. I can get a them into the database if the field is LONG, after reading the file in as Binary and convert it using toBase64. I insert it into as a parameter as "cfsqltype="CF_SQL_LONGVARCHAR"". I can also insert both a Notepad documents and Images, but the problem arises when I try to get them out. The MS Word document has 'nulls', which causes an errors when I attempt to convert it back. (Only Notepad documents convert without error.) Someone suggested to store the documents as LONG RAW instead. I attempted to do that using "cfsqltype="CF_SQL_LONGVARBINARY". And am getting the error...

             ODBC Error Code = 22005 (Error in assignment)

             [Oracle][ODBC Oracle Driver]Error in assignment.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (15:2) to (15:86).

             I think LONG RAW is the way to go. Can anyone help?
1 Solution
you should not enter the data like image to the data base. try to just put the logical path to the image. so that while gettng the data from image fields just put the href to that path so that browser will just link that file.

example gives the user option to choose upload the file .

----data entry page----
<Input type="file" name="myFile">

in the save page
       <cfif form.myFile is not "">
          <CFFILE action="UPLOAD" accept="image/*"
     destination="#uploadPath#" filefield="myFile"
       <CFSET targetFile ="../images" & "/" & "#File.ServerFile#">
       <!--- above statement found out what is the extension of file and path --->

       <cfquery datasource="dsn">
               insert into table1 (ICON) values ('#targetFile#')
      Notes : ICON fields of ur database is varchar2(255) means sholud be larger


while retriving the data from table
<cfquery name="test" datasource="dsn">
    Select  ICON  *  from table1

<cfoutput query="test">
     <cfif icon_file IS NOT "">
           <Img border="0" src="#ICON#" hspace="0"  
      vspace="0" alt="Images comes here." height="32" width="32">

Incase of image put in the Img tag. If that some word document then link it with <a href="#ICON#>

wiseguy21Author Commented:
dash420, thanks for your suggestion, but I should have explain more in detail, right now we are already storing the documents on the webserver. We wanted to store them in the database because they would be more secure from corruption, and to free up space on the server.
if u really want save document/image to database. make that fields as "blob" i.e. binary large object. it will take all doc file and image type file.

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

wiseguy21Author Commented:
Thanks but Oracle 7.34 doesn't recognize blobs. If my company would upgrade I would not be asking this question. After finally getting an Allaire rep respond to my question. My problem was how I was attempting to open the files. When retrieving the files from Oracle you must copy them to a temp file and then open them using <cfcontent>. I had been attempting to open them right from Oracle, which was wrong.
wiseguy21Author Commented:
Thanks to all, but I solved the problem.
No comment has been added lately, so it's time to clean up this question.
I will leave the following recommendation in the Cleanup topic area:


Please leave any comments here within the next four days.

EE Cleanup Volunteer
PAQed, with points refunded (50)

Community Support Moderator
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now