Why am I getting a binary result from my query?

I am using Coldfusion and MySQL.

My test query is:
<cfquery name="qGetMembers1" datasource="#application.defDSN#">
	SELECT	DISTINCT CASE WHEN LENGTH(TRIM(FirstName)) = 0
			THEN username
			ELSE
			CONCAT(FirstName, " ", LastName) 
			END
			as Name
			,ru.userID as userID
			,CONCAT(CASE WHEN LENGTH(TRIM(FirstName)) = 0
			THEN username
			ELSE
			CONCAT(FirstName, " ", LastName)
			END,"_",ru.userID,"_",ru.RegistrationDate) as nameID, firstname, lastname, ru.userid
	FROM	registeredusers ru
	LEFT JOIN group_registrations gr ON gr.userID = ru.userid
</cfquery>

Open in new window


Using the same database on my localhost I get the result:
bad.PNG

On my server I get the correct result:
good.PNG
I don't understand why my result on my localhost is a binary result
WestCoast_BCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:
CONCAT(CASE WHEN LENGTH(TRIM(FirstName)) = 0
                  THEN username
                  ELSE
                  CONCAT(FirstName, " ", LastName)
                  END,"_",ru.userID,"_",ru.RegistrationDate

try casting (Or converting) your non-string values to strings.
I don't know the function for mySQL, but something like this..

      cast(ru.user_id as varchar)  

and do the same with the date field.

You are concatenating a mix of numbers, characters and dates - change them all to strings

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WestCoast_BCAuthor Commented:
I have learned that if I use CAST for the userid it works on my localhost:
<cfquery name="qGetMembers1" datasource="#application.defDSN#">
	SELECT	DISTINCT CASE WHEN LENGTH(TRIM(FirstName)) = 0
			THEN username
			ELSE
			CONCAT(FirstName, " ", LastName) 
			END
			as Name
			,ru.userID as userID
			,CONCAT(CASE WHEN LENGTH(TRIM(FirstName)) = 0
			THEN username
			ELSE
			CONCAT(FirstName, " ", LastName)
			END,"_",CAST(ru.userID as CHAR(15)),"_",ru.RegistrationDate) as nameID, firstname, lastname, ru.userid
	FROM	registeredusers ru
	LEFT JOIN group_registrations gr ON gr.userID = ru.userid
</cfquery>

Open in new window


Why do I need to use CAST on my localhost?
WestCoast_BCAuthor Commented:
Thank you gdemaria.  I don't understand why it works on my server but not on my localhost
gdemariaCommented:
Yeah, good question.   Perhaps a difference in database version?
_agx_Commented:
Edit: Expanded example

It usually depends on A) what operation is performed, AND B) the db drivers and C) db version.  

For Concat, the MySQL docs say:

Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form.

That suggests one ore more of the fields you're concatenating is being handled as binary.  In my experience, the behavior varies a lot depending on the db drivers (and version of course). Even using exact same db, you can get different results from different drivers.  For example, querying the same db with CF10 and  CF11.  A little annoying, but ... most of the time, the only thing you can to do is CAST to the correct type and move on.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.