Solved

How do insert a Document/Image into Oracle?

Posted on 2001-08-24
7
183 Views
Last Modified: 2013-12-24
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?
0
Comment
Question by:wiseguy21
7 Comments
 
LVL 6

Expert Comment

by:dash420
ID: 6424418
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 .

like
----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"
     nameconflict="MAKEUNIQUE">
       
       <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#')
       </cfquery>
      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
</cfquery>

<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">
       <cfelse>&nbsp</cfif>
</cfoutput>

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

0
 

Author Comment

by:wiseguy21
ID: 6428276
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.
0
 
LVL 6

Expert Comment

by:dash420
ID: 6443726
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.

0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:wiseguy21
ID: 6444566
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.
0
 

Author Comment

by:wiseguy21
ID: 6444574
Thanks to all, but I solved the problem.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10837438
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:

PAQ/Refund

Please leave any comments here within the next four days.

mrichmon
EE Cleanup Volunteer
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 10864895
PAQed, with points refunded (50)

GhostMod
Community Support Moderator
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question