Solved

How do insert a Document/Image into Oracle?

Posted on 2001-08-24
7
185 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

690 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