• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Upload a binary image to SQL with ColdFusion

Community Beginner ,
Jul 20, 2017 Jul 20, 2017

Copy link to clipboard

Copied

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?

TOPICS
Database access

Views

3.8K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

LEGEND , Jul 20, 2017 Jul 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 alw

...

Votes

Translate

Translate
LEGEND ,
Jul 20, 2017 Jul 20, 2017

Copy link to clipboard

Copied

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

HTH,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jul 20, 2017 Jul 20, 2017

Copy link to clipboard

Copied

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">

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jul 20, 2017 Jul 20, 2017

Copy link to clipboard

Copied

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,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jul 21, 2017 Jul 21, 2017

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jul 21, 2017 Jul 21, 2017

Copy link to clipboard

Copied

LATEST

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 20, 2017 Jul 20, 2017

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jul 20, 2017 Jul 20, 2017

Copy link to clipboard

Copied

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,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation