?
Solved

Using BLOB with CF/Oracle 8i

Posted on 2002-03-15
17
Medium Priority
?
837 Views
Last Modified: 2013-12-24
My client wants that i store Word documents in the DB as BLOB. I can't find an easy way to do this... i found several solutions using for example java but not "direct solution" and clear answer ;(

So, could someone tell me if it's possible by using only CF(4.5.2 or 5) and Oracle (8i) ?

Thanks a lot,
Cyril
0
Comment
Question by:Cyril_H
[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
  • 6
  • 4
17 Comments
 
LVL 19

Expert Comment

by:cheekycj
ID: 6867995
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6867997
or just make a java object to do the insert/update for you and call it using CF.

CJ
0
 
LVL 2

Author Comment

by:Cyril_H
ID: 6868030
Thanks CJ, it's one of the article i already read. But i was wondering if there was another easier solution by using only CF and Oracle ?
0
Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

 
LVL 19

Expert Comment

by:cheekycj
ID: 6868366
Another article:
http://cfhub.com/contributions/oracle/

Uses Java too.

I am beginning to think it may be the only approach :-(

CJ
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6868400
Something I found:
You can do this in CF.  In your update or insert queries use <cfqueryparam>
and in CF Admin in the ODBC section under the Datasource for you DB, be sure
to click the option to Enable retrieval of long text.

Example query:
<CFQUERY NAME="qBLOB" DATASOURCE="#DSN#">
    Insert into {table_name}
    (col1, col2, col3, col4)
    Values
    (#value1#, #value2#, #value3#, <CFQUERYPARAM VALUE="#Value4#"
CFSQLType="CF_SQL_BLOB">)
</CFQUERY>
0
 
LVL 11

Expert Comment

by:jimmy282
ID: 6868468
This is the code which should work but I when I run it on my SQl2k and CF5 I get an error while retrieving the data (I could Insert successfully)


<cffile action="ReadBinary"
 file="#ExpandPath(".")#\GD.DOC"
 variable="theDocFile">

 <cfset base64 = #toBase64(theDocFile)#>
<CFQUERY NAME="qBLOB" DATASOURCE="tajseed">
   Insert into Doctable
    Values
   (2,'#tobase64(theDocFile)#')
</CFQUERY>

<cfquery name="readDoc" datasource="tajseed">
 select docFile from DocTable where  id = 2;
</cfquery>

<cfset TempFile = GetTempFile("c:\temp\","tmpFile")>

<cffile action="Write"
 file="#TempFile#"
 output="#toBinary(readDoc.docFile)#"  
 addnewline="No">

<cfcontent type="application/msword" file="#TempFile#" deletefile="Yes">

Jimmy

0
 
LVL 2

Author Comment

by:Cyril_H
ID: 6876169
cheekycj,

it seems that CF5 does not recognise CF_SQL_BLOB ;) Here's the error message...

Error Diagnostic Information
CFSQLTYPE

'CF_SQL_BLOB' is not a valid CFSQLTYPE setting for CFQUERYPARAM. When specified, the CFSQLTYPE attribute must be set to one of the following: CF_SQL_BIGINT CF_SQL_BINARY CF_SQL_BIT CF_SQL_CHAR CF_SQL_CLOB CF_SQL_DATE CF_SQL_DECIMAL CF_SQL_DOUBLE CF_SQL_FLOAT CF_SQL_IDSTAMP CF_SQL_INTEGER CF_SQL_LONGVARBINARY CF_SQL_LONGVARCHAR CF_SQL_MONEY CF_SQL_MONEY4 CF_SQL_NUMERIC CF_SQL_REAL CF_SQL_REFCURSOR CF_SQL_SMALLINT CF_SQL_TIME CF_SQL_TIMESTAMP CF_SQL_TINYINT CF_SQL_VARBINARY CF_SQL_VARCHAR
0
 
LVL 2

Author Comment

by:Cyril_H
ID: 6876176
jimmy,

i cannot insert the doc file in the dB ! I got the following message :

Error Diagnostic Information
Oracle Error Code = 1704

ORA-01704: string literal too long


Thanks,
Cyril
0
 
LVL 11

Accepted Solution

by:
jimmy282 earned 800 total points
ID: 6876181
1)You have to use CF_SQL_CLOB instead of CF_SQL_BLOB.

2)I used the ntext datatype in SQL server. I suppose its BLOB for ORacle. The doc should go in....I think.
0
 
LVL 11

Expert Comment

by:jimmy282
ID: 6876189
I read few articles the other day which said that using ADODB.Stream, You can insert the Data in DB But then again you have to use CFOBJECT which is not purely CF solution.

I think Java is the best solution for it as CJ suggested.

Jimmy
0
 
LVL 2

Author Comment

by:Cyril_H
ID: 6876200
jimmy,

i cannot insert the doc file in the dB ! I got the following message :

Error Diagnostic Information
Oracle Error Code = 1704

ORA-01704: string literal too long


Thanks,
Cyril
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6876577
is the data type set as clob or blob?

Jimmy: Cyril would have to insert a file as a BLOB not a CLOB.  CLOB would be fine if just the data (text) was going to be inserted.

CJ
0
 
LVL 2

Author Comment

by:Cyril_H
ID: 6876682
It works !!! Thanks a lot to everyone... Thanks Jimmy, thanks CJ !

I can insert a Word document (with pics embedded) in a CLOB field. Here's the full code for it. I'm using Oracle 8i and CF5 :

<!--- the form page - test.cfm --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
     <title>Untitled</title>
</head>

<body>

<FORM name="test" method="post" action="test_action.cfm" enctype="multipart/form-data">

<input type="File" name="myFile">

<INPUT type="Submit">

</FORM>


</body>
</html>


<!--- the insert page test_action.cfm --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
     <title>Untitled</title>
</head>

<body>

<CFFILE ACTION="ReadBinary"
    FILE="#MyFile#"
    VARIABLE="aBinaryObj">


<cfif isBinary(aBinaryObj)>
     <cfset base64 = toBase64(aBinaryObj)>
<cfelse>
     <cfabort showerror="The file doesnt seem to be a binary file">
</cfif>


<CFQUERY datasource="#myDSN#">
INSERT INTO EXPERTS(NAME,CV)
     VALUES ('Cyril',<CFQUERYPARAM VALUE="#base64#"
CFSQLType="CF_SQL_CLOB">)
</CFQUERY>

</body>
</html>


To view the word doc i need 2 pages.

<!--- the Select doc page --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
     <title>Untitled</title>
</head>

<body>
<CFQUERY name="qryGetCV" datasource="#myDSN#">
SELECT CV
  FROM EXPERTS
 WHERE NAME = 'Cyril'
</CFQUERY>

<cffile action="WRITE" file="#GetDirectoryFromPath(GetCurrentTemplatePath())#/tmp.doc" output="#toBinary(qryGetCV.CV)#" addnewline="No">

<cflocation url="result.cfm">

</body>
</html>


<!--- the view word doc page --->
<cfcontent type="application/msword" file="#GetDirectoryFromPath(GetCurrentTemplatePath())#/tmp.doc" deletefile="Yes">
0
 
LVL 2

Author Comment

by:Cyril_H
ID: 6876688
see my last comment.

Thanks to EE too ! ;)
0
 
LVL 11

Expert Comment

by:jimmy282
ID: 6876703
So I was just missing the second file while viewing.
That explains it.
May be the headers are already passed thats why i couldnt get it working.....So now thigns are OK.

Thanks for the Points .

Jimmy
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6876865
Glad you got it working!
0
 
LVL 2

Author Comment

by:Cyril_H
ID: 6876880
Yep me too ;) And by the way, i try to make it work with gif files, and it works without any modifications ! ;)

Thanks again,
Cyril
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

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…
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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

777 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