Solved

How do insert a Document/Image into Oracle?

Posted on 2001-08-24
7
180 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now