How to convert ByteArray to String?

[gmu]

We are using SQL Server, one of the columns is ByteArray datatype, when I tried
to output it with the <cfoutput> tag, I got ' ByteArray objects cannot be
converted to strings' error. I tried the tostring function also with the
tobase64 function, still it doesn't convert the byteArray correctly into a
string. Any idea? Thanks in advance.

[gmu]

Sorry, the data type is varbinary, but I do get the 'ByteArray objects cannot
be converted to strings'' error, I have tried anything, tried toString() and
tobase64(), also CONVERT function inside the SQL statement, still nothing is
working. Any one can help? Many thanks.

[paross1]

How about CAST(varbinary_colum AS VARCHAR) within your SQL?

SELECT CAST(column_name AS VARCHAR) AS new_col_name
FROM table
WHERE etc...

Phil

[gmu]

Thanks Phil, I did that also.... COVERT and CAST, nothing works :-( , it did
covert/cast the varbinary to a string but the output value is just not correct.
Actually we used Hash() to encrypt a string and inserted it in a varbinary
column, then simply retrieved it with a SELECT statement even without any
COMVERT or CAST or toString function, it worked just fine with the older
version of CF 4.x and SQL 7, but once we upgraded to MX and SQL 2000, it
doesn't work any more. Any idea? Thanks again in advance.

[JacobG]

Please try using the function CharsetEncode() for converting byte[] to string.
A sample code snippet is given below.

<cfquery name="testQuery" datasource="mssql">
select data from TestTable where SNo = 1
</cfquery>

<cfoutput>
<cfset strData = CharsetEncode(testQuery.data,"ISO-8859-1")>
The string value is #strData#
</cfoutput>

Hope this helps.

Jacob

[gmu]

Thanks Jacob. But it still doesn't work, I got ' Variable CHARSETENCODE is
undefined' error, I even tried BinaryEnocde and also got 'Variable
BINARYENCODE is undefined' error. Then I checked out the CharsetEncode function
on the Web, found out that these two functions (CharsetEncode and BinaryEncode)
is added to MX 7, but we are using MX 6. Any other idea? Thanks again in
advance.

[Nikade]

I'd appreciate some help on this as well.
My .cfm worked fine in CF4.5. Just upgraded to CFMX6.
Now, once we retreive the value from the mySQL database, I want to perform
simple string functions on it. (replacing <p> with <p class="text2"> for
example)

However, I get the same error: ByteArray objects cannot be converted to
strings.
Thanks!

[gmu]

Nikade, did you get the correct text when using #tostring()#? Yes, the
tostring() function did get rid of the 'ByteArray objects cannot be converted
to strings' error, but when I output it, the text is not really correct.

[JacobG]

Hi,
Sorry that I am not aware of any built-in functions in CF 6.0 that can
solve your problem right away. But, one of the workarounds I can suggest is to
create a user defined function. Given below is the CF code snippet that makes
use of a custom function for the same.

<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(argum ents.bytes,arguments.encoding)

>

<cfreturn strData>
</cffunction>

<cfquery name="testQuery" datasource="mssql">
select data from TestTable where myID = 1
</cfquery>

<cfoutput query="testQuery">
<cfset strData = getString(data,'ISO-8859-1')>
Retrieved from database is #strData#
</cfoutput>

Hope this helps.

Best Regards

[supermario29]

JacobG:
Your UDF works like a charm; Thanks for the help!

[gmu]

Jacob, thanks for your second reply. Unfortunately it still doesn't work, I
tried your built-in function but the result is the same as when I was just
using tostring() function. We can select that varbinary column from the DB
table and use either tostring() or the built-in function you provided to
convert the varbinary value to a string, however, the string we got is still
some garbage that we can't use for string comparison. It looks like it's still
in binary version. This doesn't happen in CF4.x/SQL 7.

[JacobG]

Hi,
Sorry to hear that your problem still persists. Would like to try one more
thing. In the UDF provided, the encoding is given as ISO-8859-1. Are you sure
that ISO-8859-1 was the encoding used to create the binary data present in the
DB. If you hadn't provided any specific character set encoding, it should be
the default encoding supported by your OS. Please use that encoding format (if
any) instead of ISO-8859-1. Please try this and let me know.

Best Regards

[gmu]

Thanks again, Jacob. I did try tostring(var, 'iso-8859-1') where var is the
value retrives from a varbinary database column, it doesn't make any difference
if I use iso-8859-1 or not. I am starting to think there's something different
between SQL7 and SQL2000 and CF may not be able to convert sql2000's varbinary
to string.....

[paross1]

I know that I used to be able to get away with more implicit type conversions
when using CF 4.5 than with MX, so that is why you probably didn't need to do
any conversions wneh using the older version.

For what it is worth, I did a little experiment by creating a table with a
varbinary column in my SQL Serer 2k database. I then attempted to perform a
direct insert into the table with a 'string' value, and also a
'#hash("string")#', and got the error message Macromedia][SQLServer JDBC
Driver][SQLServer]Disallowed implicit conversion from data type varchar to data
type varbinary, table 'dbo.Table1', column 'testval';. Use the CONVERT function
to run this query. When I CAST the value of the string to varbinary using
cast('#hash("string")#' as varbinary), the insert then worked without error.

However, in order to retrieve the value from the table within a select
statement, I had to use cast(testval as varchar) to explicitly convert the
varbinary column to varchar, otherwise I got the [i/ByteArray objects cannot be
converted to strings error message. So, it looks like whatever you get wneh you
CAST your column to a varchar is what is actually in the database. Have you
attempted to perform the same select statement with CAST() directly in SQL
Server using Enterprise manager? If you are returning the same value as you get
when running the query in CF, then I'm afraid that is what is actually stored
in the database.

Phil

[gmu]

Thanks Phil. Yes, I did try cast() within a select statement, what I got is
just some garbage, for example, one of the strings I got is: <???#????e*
As you can see, it contains some special symbols/characters, this string was
encrypted by the hash() funcation and then inserted into the varbinary column,
I don't think it's the 'right' string since hash() function won't encrypt a
string with specials symbols/characters like those.

[paross1]

Were you using a different encoding when the string was hashed than what you are using now?

Phil

[gmu]

I didn't write the code, but the person who wrote the code simply used
Hash(string), I don't see any encoding is used with the hash() function. Is the
hash() fuction on CF 4.0 different from CF MX? Speaking of which, actually I
am still wondering how come the application has been using the hash() function
on 4.0 with no problem, I thought hash() was not available till 6.0??? And
those special symbols/characters I mentioned in my previous message is what I
got when cast(0 it to varchar.

[paross1]

Does it look any differen/better/worse if you CAST() it to NVARCHAR?

;)
Phil

[gmu]

It makes worse, if I cast() to nvarchar, some asian characters come up :)