insert blob in oracle (CF_SQL_BLOB) not working

[shahzad]

I've the following code that is supposed to upload an image as a BLOB in the
oracle 9i db (I am using cf mx 7) <cffile action='readbinary'
file='C:\projects\java\insertblob\tiger.jpg' variable='aBinaryObj'> <cfif
isBinary(aBinaryObj)> comes here <cfset base64 = toBase64(aBinaryObj)>
<cfelse> <cfabort showerror='The file does not appear to be a binary file'>
</cfif> <cfquery name='insertImage' username='pcqureshis' datasource='arkdv'
password='yellow200'> INSERT INTO images (image) values (<cfqueryparam
value='#base64#' cfsqltype='CF_SQL_BLOB'> ) </cfquery> When i run this code,
i get the following error
--------------------------------------------------------------------------------
-- Error casting an object of type to an incompatible type. This usually
indicates a programming error in Java, although it could also mean you have
tried to use a foreign object in a different way than it was designed. The
error occurred in C:\Program Files\Apache
Group\Apache2\htdocs\Cableways\uploadImage.cfm: line 21 19 : 20 : <cfquery
name='insertImage' username='pcqureshis' datasource='arkdv'
password='yellow200'> 21 : INSERT INTO images (image) values (<cfqueryparam
value='#base64#' cfsqltype='CF_SQL_BLOB'>) 22 : </cfquery> 23 :
------------------------------------------------------------------------------
so, is this a bug in cfmx 7 or what else could it be? I really need help on
this, very urgent thank you

[JacobG]

Hi,
I was wondering why you want to convert the binary image to Base64. So, here
we will consider insertion of pure binary as well as base64 data. First, lets
consider the insertion of pure binary data.

Here, I assume that you are having an Oracle table with column type BLOB.

/*
Inserting binary file as BLOB
*/
<cffile action="readbinary" file="C:\projects\java\insertblob\tiger.jpg"
variable="aBinaryObj">

<cfif NOT isBinary(aBinaryObj)>
<cfabort showerror="The file does not appear to be a binary file">
</cfif>

<cfquery name="insertImage" username="pcqureshis" datasource="arkdv"
password="yellow200">
INSERT INTO images (image) values (<cfqueryparam value="#aBinaryObj#"
cfsqltype="CF_SQL_BLOB"> )
</cfquery>


In case, you want to put Base64 value in DB, please make the following changes.
1) Change the DB table column type to CLOB
2) Make subsequent changes in code snippet as follows


/*
Inserting base64 data as CLOB
*/
<cffile action="readbinary" file="C:\projects\java\insertblob\tiger.jpg"
variable="aBinaryObj">

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

<cfquery name="insertImage" username="pcqureshis" datasource="arkdv"
password="yellow200">
INSERT INTO images (image) values (<cfqueryparam value="#base64#"
cfsqltype="CF_SQL_CLOB"> )
</cfquery>

Hope this helps.



Best Regards,
Jacob

[shahzad]

I am an idiot. I was actually copying the code from
[url]http://www.macromedia.com/cfusion/webforums/forum/messageview.cfm?catid=6&amp;th[/url]
readid=843872&amp;enterthread=y and didn't even think as to why he was
converting the image to base64 and even the error msg didn't make sense to me.
As soon as i left, it hit me but it was too late and then your reply confirmed
it. thank you for ur help