?
Solved

Base64 Binary image from database

Posted on 2003-03-01
24
Medium Priority
?
795 Views
Last Modified: 2013-12-24
Hello,

I am connecting to a SQL 7 database that has a field: medium_photo, where BLOBs of JPG images are stored. (this isn't my database, so I have to get the info. out of the database and can't have it stored in a directory). With CFMX, I was able to do this with no problem. I am looking at a new host that is running CF5 and I can no longer retrieve the data. I get an error message that it is not Base64 data, and it also truncates the data to 38 characters. Here is the code I am trying to use:

<CFQUERY NAME="ResDaily" DATASOURCE="mlsarsql" CACHEDWITHIN="#CreateTimeSpan(1,0,0,0)#">
  SELECT
     id, medium_photo
  FROM dbo.idxrusmls1
  WHERE id = 208248
</CFQUERY>
<CFLOCK NAME="RESDAILY" TYPE="EXCLUSIVE" TIMEOUT="30">
<cfloop QUERY="ResDaily">
<cfoutput>
<CFSET MyBase64=#ResDaily.medium_photo#>
<CFSET MyBinary=ToBinary(MyBase64)>
<CFFILE ACTION="write"
     FILE="/www/htdocs/vhosts/russellvillerentals.com/images/res/#ResDaily.id#.jpg"
     OUTPUT=#MyBinary#>
</cfoutput>
</cfloop>
</CFLOCK>

Is this possible with CF5? Thanks!!!

Bob

=============================================
This question has been deleted with no points refunded.  05/02/2004 12:41PM PST

ee_ai_construct - (re-order part number #xm34)
Community Support Moderator
=============================================
0
Comment
Question by:Snypa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
  • 2
  • +3
24 Comments
 
LVL 2

Expert Comment

by:weeezl
ID: 8049245
0
 
LVL 2

Expert Comment

by:weeezl
ID: 8049276
You can also try this and see if it works in CF5.

<CFQUERY NAME="ResDaily" DATASOURCE="mlsarsql" CACHEDWITHIN="#CreateTimeSpan(1,0,0,0)#">
 SELECT
    id, medium_photo
 FROM dbo.idxrusmls1
 WHERE id = 208248
</CFQUERY>

<cfcontent type="image/jpeg"><cfheader name="Content-Disposition" value="filename=208248.jpg">
<cfoutput>#ResDaily.medium_photo#</cfoutput>
0
 

Author Comment

by:Snypa
ID: 8049395
weeezl,

Tried the second approach with cfcontent/cfheader. No luck. The data that CF is retrieving is only 37-38 characters long. The actual data as you would expect, is much larger (and IS correct in the database). I'm looking into jBlob and CFX_Image now, but I'm beginning to think it is either: 1. A bug with CF5
2. A problem with the Merant SQL Server Driver 3. A problem with the OS/Webserver.

Don't know why the data is being truncated...

Thanks,

Bob
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:Snypa
ID: 8052314
Just an update,

Haven't been able to get either custom tag to work yet. Still trying...

Does anyone know why CF5 would return a truncated string for the data?

Thanks again!

Bob
0
 
LVL 14

Expert Comment

by:Scott Bennett
ID: 8053448
In coldfusion administrator open the details of the datasource you are accessing and check to make sure that you have checked the option "Enable retrieval of long text". If you have not checked this box then you will not be able to retrieve BLOBs, CLOBs, or any othe long text objects from the database.

-Scott
0
 

Author Comment

by:Snypa
ID: 8053660
Scott,

Sorry I didn't mention that earlier, but I do have retrieve long text enabled. Doesn't seem to make any difference in this case...

Thanks for the input...

Bob
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8056130
hi try going thru these & see if it helps u out !

the string u get - u'll need a image converter to convert the string back to a image to display it - its normally very easy to do all this in application languages .. but in web - u need to do things like mentioned in hte following articles :

http://www.cfhub.com/contributions/oracle/

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=45&lngWId=9

http://support.microsoft.com/default.aspx?scid=KB;en-us;q210486

K'Rgds
Anand
0
 

Author Comment

by:Snypa
ID: 8057249
Hi Anand,

Some good information there. I've pretty much decided that the problem is with the Solaris OS, the web server (Apache) or with Coldfusion 5. I've even given up trying to do anything with the BLOB data until I can figure out if it's possible to retrieve ALL of it (I'm still only getting about 38 characters retrieved from the field).

Thanks for taking the time to forward the articles!

Bob
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8062343
i guess it stores it in 38 bit format - even i get my image files & word documents stored in a string format of 38 chars long

so i think - u just need to use a proper image converted to get thigns right

dont worry abt the length - ur getting the proper string - u just need to convert it to get the image file back again !

K'Rgds
Anand
0
 

Author Comment

by:Snypa
ID: 8064085
Anand,

Are you saying that I need to convert it on the way into Coldfusion? If so, do you know of anyway to do that using Solaris, Coldfusion 5 and MS SQL 7? Once again, this works using CFMX on a windows based server. The articles you sent were dealing with ASP (which I can't use) and Oracle databases.

Thanks again for the input!

Bob
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8069612
try this,

Q. How can I store images in a database using the BLOB data type and then output them again?

A. There are two custom tags out there to help you out: cfx_putimage and cfx_getimage.

Create a ColdFusion page called "showImage.cfm" and pass it paramaters to uniquely identify an image. The CF page then sets the content type to, for example, "image/gif", then return the binary data.

<\img src="ShowImage.cfm?ImageID=3456">Microsoft also has an article about serving images from a BLOB field using SQL server. The example is in ASP, but the theory is the same for ColdFusion
http://support.microsoft.com/support/kb/articles/Q173/3/08.asp

This content type reference might also be useful
http://www.utoronto.ca/ian/books/html4ed/appb/mimetype.html

to question

K'Rgds
Anand
0
 

Author Comment

by:Snypa
ID: 8069747
Hello again Anand,

I've tried using CFX_Getimage, but the server says that it can't find the .dll file. It is registered correctly in CF and CF is looking for it in the right path. Tech support says that it must have been compiled in C++ for a different platform and that's why it doesn't work. I was under the impression that if it was compiled in C++ it didn't make any difference, but it's not working, so...

You can see the error message here: http://www.russellvillerentals.com/testing_cffile3.cfm

Thanks again for the help!

Bob
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8069861
hi - it says the dll is missing in path :
(/usr/local/coldfusion/CustomTags/getImage.dll)

make sure u've set ur path right for custom tags & its not looking elsewhere ...

also just chk if the dll is present in this path - if not - just paste it there & then thigns shld be fine

let me know

K'Rgds
Anand
0
 

Author Comment

by:Snypa
ID: 8070042
Hi Anand,

Yes, that is the correct path and the .dll is there. Is it possible that the CFX tag was written for CF4 and won't work? Or maybe it has to be in a specific directory?

Thanks again,

Bob
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8070096
may be - it cld be a possibility - I had used this loooooooong back ... not sure whats up with it as of now [newer version]

I'll see if i can get more info on this for u

K'Rgds
Anand
0
 

Author Comment

by:Snypa
ID: 8123963
Just an update...

Apparently according to MacroMedia, this is a bug in CF 5 and will not work! They suggest converting BLOBs into Text fields to get it to work. Won't help in my case, as I don't have the ability to do that. This was fixed with CF MX.

Thanks everyone for trying to help!
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8125498
ohhhhhhhk

thanks for the update ... i was getting lost with this stuff wondering ....

ne-ways

K'Rgds
Anand
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8126803
btw - take a look at this one ... see if it helps u out

http://web4w3.com/jblob.html

K'Rgds
Anand
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8274610
hello ??????????
0
 

Author Comment

by:Snypa
ID: 8283716
Hello Anand,

Sorry I didn't reply. Never saw your comment. jBlob won't help, because as I said, this is a bug with ColdFusion 5, and there is no known workaround. (other than upgrading to CFMX!). All of the info. may be helpful though, now that I am back with MX...

Thanks again,

Bob
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8289519
did u try updating the MX ???

they have 3 updates available on the net - try them & c if u can get thigns to work out !

K'Rgds
Anand
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8357671
hi - long time - but found this - hope it helps u to get things working [if they arent yet !]

<!--- ========================================================================================================== --->

<!--- the form page - test.cfm --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>Untitled</TITLE>
</HEAD>
<BODY>
<FORM name="test" method="post" action="test_action.cfm" enctype="multipart/form-data">
     <INPUT type="File" name="myFile">
     <INPUT type="Submit">
</FORM>
</BODY>
</HTML>
/*
This file takes the file selected in the previous template, and inserts it into the database.

(Need to insert a DSN, username/password ;-)
*/

<!--- the insert page test_action.cfm --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>Untitled</TITLE>
</HEAD>
<BODY>
<CFFILE ACTION="ReadBinary" FILE="#MyFile#" VARIABLE="aBinaryObj">
<CFIF isBinary(aBinaryObj)>
     <CFSET base64 = toBase64(aBinaryObj)>
<CFELSE>
     <CFABORT showerror="The file doesnt seem to be a binary file">
</CFIF>
<CFQUERY datasource="" username="" password="">
     INSERT INTO PHOTOS(PERSON_ID, PHOTO)
     VALUES (1,<CFQUERYPARAM VALUE="#base64#" CFSQLType="CF_SQL_CLOB">)
</CFQUERY>
</BODY>
</HTML>
/*
This file pulls the image from the database, and then moves to result.cfm, which is where the image "appears"

(Again, supply DSN username/password)
*/

<!--- the Select doc page --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>Untitled</TITLE>
</HEAD>
<BODY>
<CFQUERY name="getImg" datasource="" username="" password="">
     SELECT PHOTO
     FROM PHOTOS
     WHERE PERSON_ID = 1
</CFQUERY>
<CFFILE action="WRITE" file="#GetDirectoryFromPath(GetCurrentTemplatePath())#/tmp.jpg" output="#toBinary(getImg.PHOTO)#" addnewline="No">
<CFLOCATION url="result.cfm">
</BODY>
</HTML>
/*
This is the image from the database!
*/
<CFCONTENT type="image/jpeg" file="#GetDirectoryFromPath(GetCurrentTemplatePath())#/tmp.jpg" deletefile="Yes">

<!--- ========================================================================================================== --->
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10940224
No comment has been added lately, so it's time to clean up this question.
I will leave the following recommendation in the Cleanup topic area:

PAQ/Refund

Please leave any comments here within the next four days.

mrichmon
EE Cleanup Volunteer
0
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
ID: 10973059
PAQed, with points refunded (250)

ee_ai_construct - (re-order part number #xm34)
Community Support Moderator
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question