CODEWITHSUNDEEP
mysqlcoldfusioncfml
John
John

Reputation: 1338

How do I convert binary data from MySQL to a string in ColdFusion 11?

I have a field in my MySQL database that is set to data type "BIT". The value in the field is 101101. I am trying to read this value using Coldfusion (version 11)

I simply use the following code:

<cfquery name=q1 datasource=#data_source#>
  select * from mytable
</cfquery>

<cfoutput>
 #q1.mybitfield#
</cfoutput>

I have tried using CAST and CONVERT on the MySQL side and I have tried CharSetEncodeing on the CF side along with every option of ToString, ToBase64, and ToBinary that I can think of.

I still can not get my output to look like it does in the database.

Upvotes: 1

Views: 6279

Answers (2)

John
John

Reputation: 1338

Thanks Leigh for your help. I was never able to get it to work with just one step so I used a two step solution (if you want to call it that). What I ended up doing was setting up my MySQL statement to convert the field to an Unsigned interger (although Decimal or a signed integer would have also worked), then once I read in the decimal value I was able to convert it to binary using your suggestion of FormatBaseN(q1.myfield,2). So thanks for the reminder of FormatBaseN. I had forgotten about that one.

My final code ended up looking like this:

The MySQL statement:

SELECT *, CONVERT(item , UNSIGNED) as di from mytable

And the Coldfusion looked like this:

<cfset d = FormatBaseN(q1.di, 2)>

Edit

After writing this, I decided to go with Leigh's answer above since it was a better solution.

Upvotes: 2

Leigh
Leigh

Reputation: 28873

Actually, I was probably thinking of SQL Server's bit type. For MySQL, a simpler option might be to use the bin() function which:

Returns a string representation of the binary value of N, ...This is equivalent to CONV(N,10,2). Returns NULL if N is NULL.

For example:

  SELECT  bin(YourBitColumn) AS YourBitColumn FROM YourTable
  ... or 
  SELECT  bin(YourBitColumn+0) AS YourBitColumn FROM YourTable

NB: High-order 0 bits are not displayed in the converted value. That applies the CF function as well.

Upvotes: 2

Related Questions