Sure enough, the problem is with the CFM query. It looks like ColdFusion
(which I am in no way responsible for) gets the data from the database
as a series of two-character hex representations of a single byte
(for instance, 65 for A). So one hideously poor way they found to
parse it is below. Someone mentioned I might use a text type instead
of varchar, and if that doesn't work we'll probably store the location
of an include file containing the content in the database rather
than the data itself.
Thanks,
-John
<cfloop query="GetImageData">
<cftry>
<cfset str_val=toString(GetImageData.ImageType)>
<cfset data64=toBase64(GetImageData.ImageType)>
<cfset str_64_val=toString(data64)>
<cfoutput>
<cfset m_url_str="%">
<cfset m_len=Len(str_val)-1>
<cfloop index="i" from="1" to="#m_len#" step="2">
<cfset
m_url_str=ListAppend(m_url_str,mid(str_val,i,2),"%")>
</cfloop>
<cfset m_final_str="%#m_url_str#">
<br>Encoded string: #m_final_str#
<br>Decoded string: <b>#URLDecode(m_final_str)#</b>
<br>
</cfoutput>
<cfcatch>
<br>ERROR IN FUNCTION
</cfcatch>
</cftry>
</cfloop>
At Monday, 15 April 2002, "Sterin, Ilya" <[EMAIL PROTECTED]> wrote:
>You are getting binary data back, what did you expect? The field
stores
>binary, so you are getting whatever you are asking for. I don't
know how
>cold fusion handles it's types. One way to test is to try to store an
>actual binary file (image or some doc) in it, retrieve and output the
>correct content-type, then see if that file still works.
>
>Also, not sure if you are already doing so, but use
>
>binmode STDOUT;
>binmode STDIN;
>
>at beginning of your script.
>
>Ilya
>
>-----Original Message-----
>From: John
>To: [EMAIL PROTECTED]
>Sent: 4/15/02 12:54 PM
>Subject: Storing text in SQLServer image column
>
>I need to store more than 9,000 bytes of text in a column in a SQL
>Server database and use ColdFusion to extract the data. I have been
>trying with an image column. When I query wtih cfm i get a bunch
>of gibberish, I can't tell if the insert isn't working or if the
>query can't handle the binary results.
>
>I'm sure these problems (both insert and cfm query) have been solved
>and at least the Perl one posted to the list but I can't find searchable
>
>archives. I guess one alternative is to store the data in an include
>file and the address of the include in the database, but I would
>like to avoid this.
>
>I have been having trouble subscribing to this list - please respond
>directly.
>
>Thanks in advance,
>
> -John
>
>So far the closest code I think I have written is this (no errors
>reported at insert - error trapping removed):
>
>my $long = "A23456789B" x 1000;
>$long = $dbh->quote( $long, SQL_LONGVARBINARY );
>my $sql = "INSERT INTO TestImage( ImageType ) VALUES ( ? )";
>my $sth = $dbh->prepare( $sql );
>$sth->bind_param( 1, $long, { TYPE => SQL_LONGVARBINARY } );
>my $ret = $sth->execute( $long );
>
