?
Solved

How do insert a Document/Image into Oracle?

Posted on 2001-08-24
7
Medium Priority
?
187 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
ATEN's HDBaseT Presentation at InfoComm 2017

Hear ATEN Product Manager YT Liang review HDBaseT technology, highlighting ATEN’s latest solutions as they relate to real-world applications during her presentation at the HDBaseT booth at InfoComm 2017.

 

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 Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month15 days, 2 hours left to enroll

770 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