EasyCFM.COM ColdFusion Forums / MS SQL Server Related Issues / Images in SQL

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Images in SQL

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

dunn4now
03-22-2006 @ 10:43 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 6
Joined: Sep 2004

I have inherited a sql database that holds images (the field is image data type) that I need to display. I can not find information on how to display this field using CF.

Any suggestions would be greatly appreciated, I'm in over my head!!!

Sivart
03-22-2006 @ 11:27 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 235
Joined: Apr 2004

What exactly does the db contain?  Is it the image, link to the image or just the image name or something else?

-Sivart

Webmaster
03-22-2006 @ 12:07 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

This is usually referred to BLOB field in the DB.

Check out this blog entry (for further clarification or help).

http://awads.net/wp/2005/07/21/writingreading-images-tofrom-db/

Pablo Varando
Senior Application Architect
EasyCFM.COM, LLC.

904.483.1457 \\ mobile
webmaster@easycfm.com \\email

dunn4now
03-22-2006 @ 4:45 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 6
Joined: Sep 2004

  Smile  Smile  Confused Question  
Thanks for such a quick response!
You are right it is a blob. I went to the site
http://awads.net/wp/2005/07/21/writingreading-images-tofrom-db/ and was singing Hallelujah all the time I was printing the article.

But, it is not working. I checked in CFAdmin, that the enable Blob is checked in the datasource. We are running Windows 2000, I don't know if that has an effect.

Here's my code. I would appreciate it if you would look it over and tell me if I am missing something. Obviously, I am. I just don't know what. Thanks again.

<cfquery name="q" datasource="hpjdb">
select Id, BlobData
from ShBlobData
Where Id = '368'
</cfquery>



Image should display in this area!<br>
<cfoutput>#q.Id#</cfoutput>


<cffile action="WRITE" file="c:\Temp\BlobData_out.jpg" output="#q.BlobData#" addnewline="No">



Webmaster
03-22-2006 @ 5:55 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

Where Id = '368'

is this supposed to be an ID? or a text field? In other words, my question is.. does your query return anything?

Pablo Varando
Senior Application Architect
EasyCFM.COM, LLC.

904.483.1457 \\ mobile
webmaster@easycfm.com \\email

dunn4now
03-23-2006 @ 11:51 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 6
Joined: Sep 2004

Where Id = '368'

is the record id. It pulls the blobdata from record 368.


Webmaster
03-23-2006 @ 5:42 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

I get what the record is... but is it numeric or text? (because if it is numeric it does NOT need the single quotes.

But for the more imrpotant part of my question.. is the query RETURNING any data?

Pablo Varando
Senior Application Architect
EasyCFM.COM, LLC.

904.483.1457 \\ mobile
webmaster@easycfm.com \\email

dunn4now
03-23-2006 @ 9:50 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 6
Joined: Sep 2004

Blush
Opps, sorry. It is numeric.
I can return the id, and the field that holds the bytes info. But the Blobdata returns nothing and there are no errors.



dunn4now
03-24-2006 @ 10:06 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 6
Joined: Sep 2004

Thought I could be a little clearer on this topic.

More info...............

Query in sql analyzer


Select Top 5
   Id, BlobBytes, BlobData
   from ShBlobData


RETURNS:

366     3757     0x4646464630303030303030363030303430303030013737414103303030303030303030303543303030303030303002343030303002313031022D37374645374544430230C2030235413030354130023082023335300130C103022D31820230C207023843303544430230820233353002414338014646464681810130828202
367     200     0x3C68746D6C3E0D0A3C686561643E0D0A3C7469746C653E556E7469746C65643C2F7469746C653E0D0A3C2F686561643E0D0A3C626F6479206267636F6C6F723D2223464646464646223E0D0A3C62723E0D0A3C666F6E742073697A653D323E414E4755532042554C4C5320666F722073616C652C2031322D3138206D6F6E74
368     3671     0x252150532D41646F62652D332E3020455053462D332E300D0A2525426F756E64696E67426F783A20302030203133382034340D0A252543726561746F723A204D6163746976650D0A25255469746C653A204164230D0A2525446F63756D656E7450726F63657373436F6C6F72733A20426C61636B0D0A2525446F63756D656E
1468     3337     0x464646463030303030303036303030343030303001373741410330303030303030303030363730303030303030300234303030300231303102393034383132340230C2030235413030354130023082024130300130C103022D31820238430239320239380239440230C2030238433035444302308202413030023136353301
1469     403     0x3C68746D6C3E0D0A3C686561643E0D0A3C7469746C653E556E7469746C65643C2F7469746C653E0D0A3C2F686561643E0D0A3C626F6479206267636F6C6F723D2223464646464646223E0D0A3C7020616C69676E3D2263656E746572223E0D0A4841505059205448414E4B53474956494E473C62723E0D0A544F20414C4C20

CF Query:
<cfquery name="q" datasource="hpjdb">
select Id, BlobData, Blobbytes
from ShBlobData
Where Id = 368
</cfquery>



Image should display in this area!<br>
<cfoutput>#q.Id# #q.Blobbytes#</cfoutput>


<cffile action="WRITE" file="c:\Temp\BlobData_out.jpg" output="#q.BlobData#" addnewline="No">


CF Returns:

Image should display in this area!
ID:(368)—Image Bytes:(3671)




Thanks again for looking at my problem. I greatly appreciate it.

dunn4now
03-27-2006 @ 10:06 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 6
Joined: Sep 2004

Finally got this working.

Here are two site that helped. Plus the site Pablo sited.

http://www.nksd.net/cfdocs/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentation&file=00000416.htm



http://www.macromedia.com/cfusion/webforums/forum/messageview.cfm?catid=6&threadid=976780&enterthread=y


This is the code:

Hope this will be of help to anyone else with this problem.

Thank you so much for the time you spent helping me. easycfm is the first place I look for help and the best place to look.



<cffunction name="getString" access="private" returntype="string" output="false">
<cfargument name="bytes" type="array" required=true/>
<cfargument name="encoding" type="string" required=true>
<cfset var strData = CreateObject("java","java.lang.String").init(arguments.bytes,arguments.encoding)>
<cfreturn strData>
</cffunction>

<cfquery name="testquery" datasource="hpjdb">
select ShBlobData.Id, ShBlobData.BlobData, ShBlobData.Blobbytes
from ShBlobData
Where Id = 1475
</cfquery>
<cfoutput query="testQuery">
<cfset strData = getString(BlobData,'ISO-8859-1')>
<p>Retrieved from database is <br>#strData#</p>
</cfoutput>


Website Designed and Developed by Pablo Varando.