Upload a binary image to SQL with ColdFusion

[burakzeytinci]

Hi,

I need to upload an image from my computer and convert(cast) it to varbinary and store it in my SQL table's IMG(varbinary) column.

I tried somethings but i always get new errors.

First i tried to choose img on html page and then UPDATE NULL column on my already created data's.

html page
<cfform action="#request.page#?event=do.test.abz_upload" name="upload" method="post"> <input type="file" name="fpic" accept="image/*"> <cfinput type = "submit" name = "Submit6" value = "Yükle"> </cfform>

action page
<cfquery name="upload" datasource="test"> DECLARE @img varbinary; SET @img = CAST(#fpic# AS varbinary); <!--- also tried CONVERT ---> UPDATE dbo.FILMS SET IMG = @img WHERE FILM_ID = 3 ; </cfquery> <cflocation url="#request.page#?event=vi.test.abz_test" addtoken="no">
<cfquery name="upload" datasource="test"> UPDATE dbo.FILMS SET IMG = CAST(#fpic# AS varbinary) WHERE FILM_ID = 3 ; </cfquery> <cflocation url="#request.page#?event=vi.test.abz_test" addtoken="no">

error
Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'Pulp'. my image name is 'Pulp-Finction1.png' if i change it to 3.png it gives this error: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'png'.

Then i tried insert new data to my table

html page
<cfform action="#request.page#?event=do.test.abz_upload" name="upload" method="post"> <input type="text" name="fname"> <input type="file" name="fpic" accept="image/*"> <cfinput type = "submit" name = "Submit6" value = "Yükle"> </cfform>

action page
INSERT INTO dbo.FILMS(FILM_NAME, IMG) VALUES(#form.fname#,#form.fpic#)

Error, 3.png
Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'png'.

last i tried this kind of a thing but gives me BytesArray cannot be cast to String

last game
<cffile action = "readBinary" file = "C:\Users\BURAK\Desktop\Pulp-Fiction1.png" variable = "fpic">  <!--- also tried //myserver..../3.png ---> <cfquery name="upload" datasource="test"> UPDATE dbo.FILMS SET IMG = #fpic# WHERE FILM_ID = 3 ; <!--- also tried CAST and CONVERT ---> </cfquery>

Anyone have an idea how can i do it?

[WolfShade]

Use CFFILE to save the upload to the server, then use CFIMAGE to convert the file to binary, THEN insert/update the database.

HTH,

^ _ ^

[burakzeytinci]

How can i convert it to binary with CFIMAGE, it can only change the image file type like .png to .jpg(i understand like that, based on this article on helpxAdobe) :

Convert an image file format

<cfimage

required

action = "convert"

destination = "absolute pathname|pathname relative to the web root"

source = "absolute pathname|pathname relative to the web root"|URL|#cfimage variable#

optional

isBase64 = "yes|no"

name = "cfimage variable"

overwrite = "yes|no">

[WolfShade · Accepted solution]

I did some more looking around on Google, and found a better way.

<cfquery>
 INSERT INTO Image (Jpg)
 VALUES (<cfqueryparam CFSQLType="CF_SQL_BLOB" value="#ToBase64(FileReadBinary(form.fieldname))#">)
</cfquery>

This should bypass any need to save it to the server, first, which will reduce CPU and disk i/o.  (I'm not positive that you need to use "ToBase64()", give it a shot without it.)

Another thing.  I don't know anything about a datatype "varbinary".  If it will support images, fine.  But I've always used BLOB datatype for storing files in a database.

And I would be remiss if I did not mention replacing CFFORM (and all related elements) with a standard FORM and elements.  I know very few professional developers who still use CFFORM.  It was nice when it was first introduced, but has become somewhat of an albatross.

HTH,

^ _ ^

[burakzeytinci]

i think this is the right answer. but my workplace is using CF7 because of old customers have CF7 on their server, and our test server is also CF7.
FileReadBinary is supported after CF8.

I need to find another way to do it with CF7. Thanks for help.

My recent progress is:

<cfform action="#request.page#?event=do.test.abz_upload" name="upload" method="post">

<cffile action = "readBinary" file = "\\datapath..\3.png" variable = "fpic">

<cfoutput>#len(fpic)#</cfoutput> <!--- it actually reads --->

<cfoutput>tostring(fpic)</cfoutput> <!--- you can try this to see it actually reads --->

<cfinput type = "submit" name = "Submit1" value = "FILM SQL">

</cfform>

[BKBK]

burakzeytinci  wrote

I need to find another way to do it with CF7.

Form page (CFM, not HTML as you said earlier):

<cfform action="#request.page#?event=do.test.abz_upload" name="upload" method="post">

<cfinput type="file" name="fpic">

<cfinput type = "submit" name = "Submit6" value = "Yükle">

</cfform>

Action page example:

<!---

Accepted uploads: JPEG and PNG

File uploaded to directory c:\uploads

--->

<cffile action = "upload"

fileField = "fpic"

destination = "c:\uploads"

accept="image/jpeg,image/jpg,image/pjpeg,image/png"

nameConflict = "overwrite">

<!--- Read the binary from the destination --->

<cffile action="readbinary" file="c:\uploads\#cffile.serverfile#" variable="binaryFileContent" >

<!--- Save the binary in the database as a BLOB --->

<cfquery name="saveUploadInfo" datasource="cfmx_db">

    insert into uploadedfile (datetimecreated, serverdirectory, serverfile, filecontent)

    values (#cffile.timecreated#,'#cffile.serverdirectory#', '#cffile.serverfile#', <cfqueryparam cfsqltype="CF_SQL_BLOB" value="#binaryFileContent#">)

</cfquery>

[samh60076132]

One thing I note might be happening is the dot (.) before the extension. SQL may be mistaking the filename for a SCHEMA.

[WolfShade]

Okay.. here's the thing.  If it says that it's expecting a binary but a string won't do, and if it's erroring out on the NAME of the file, then the name of the file is what you're trying to insert into the database, not the file itself.

If you use CFFILE to save the file to the server, first, remembering to NOT use hashmarks (#) around the value, then you can upload it to the database.  Yeah, it's going to use more disk I/O and be a bit slower, but if you save it to the server HD first, then insert it into the database, then erase the file from the server HD, this should go smoothly.

HTH,

^ _ ^