Skip to main content
B
burakzeytinci
Participant
July 20, 2017
Answered

Upload a binary image to SQL with ColdFusion

  • July 20, 2017
  • 2 replies
  • 4158 views

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 @10607085 varbinary;

SET @10607085 = CAST(#fpic# AS varbinary); <!--- also tried CONVERT --->

UPDATE dbo.FILMS SET IMG = @10607085 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?

This topic has been closed for replies.
Correct answer WolfShade

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,

^ _ ^

2 replies

S
samh60076132
Participant
July 20, 2017

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

WolfShade
WolfShade
Legend
July 20, 2017

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,

^ _ ^

WolfShade
WolfShade
Legend
July 20, 2017

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

HTH,

^ _ ^

B
burakzeytinciAuthor
Participant
July 20, 2017

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
WolfShadeCorrect answer
Legend
July 20, 2017

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,

^ _ ^

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices