How to store image blob data in SQL Server 2008?

Question

I have a requirement to store image blob data in a table. I know storing an image in blob format within a database table is not best practice. But doing so is a client requirement. I am using ColdFusion 10 and SQL Server 2008. I have used this snippet to insert the image blob data in SQL server .

<cfimage action="read" name="imdata" source="C:\inetpub\wwwroot\a.jpg" >

<cfquery datasource="localdsn">
  INSERT INTO imtbl(image)
  VALUES #imageGetBlob(imdata)#
</cfquery>

But it is throwing error

ByteArray objects cannot be converted to strings.

I have also tried using #toString(imageGetBlob(imdata))# Still no success.

I have gone through https://forums.adobe.com/thread/60629 But can not find any solution.

Did you enable the BLOB setting on your ColdFusion datasource? — Miguel-F
– Miguel-F, Commented Feb 3, 2015 at 14:51
Have you tried parameterising the blog with cfqueryparam? Something like <cfqueryparam cfsqltype="cf_sql_blob" value="#imageGetBlob(imdata)#"> might help expose whats going on. — Twillen
– Twillen, Commented Feb 3, 2015 at 15:25
Almost. FileReadBinary() expects a file path. You are passing in an image variable. Get rid of the cfimage and use FileReadBinary("C:\inetpub\wwwroot\a.jpg") inside your cfqueryparam. Also, you obviously need parenthesis in your VALUES (...) clause. — Leigh
– Leigh, Commented Feb 4, 2015 at 13:50
I would add the following notice: If you use the image type in SQL Server (will be removed in a future version), please replace it with varbinary(max) - VarBinary vs Image SQL Server Data Type to Store Binary Data? — Igor Micev
– Igor Micev, Commented Jul 10, 2016 at 6:26

Leigh · Accepted Answer · 2015-02-04 14:09:17Z

5

Finally I have fixed the issue. Two things I have done is,

I have enabled BLOB settings for the data source.
I have finally used this query

<cfquery datasource="localdsn"> INSERT INTO imtbl(image) VALUES ( <cfqueryparam cfsqltype="cf_sql_blob" value="#fileReadBinary('C:\inetpub\wwwroot\a.jpg')#"> ) </cfquery>

edited Feb 4, 2015 at 14:09

Leigh

28.9k10 gold badges58 silver badges109 bronze badges

answered Feb 4, 2015 at 14:01

Deepak Kumar Padhy

4,4586 gold badges51 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to store image blob data in SQL Server 2008?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related