asked on

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:

On my server I get the correct result:

I don't understand why my result on my localhost is a binary result

ASKER CERTIFIED SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

WestCoast_BC

ASKER

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_BC

ASKER

Thank you gdemaria. I don't understand why it works on my server but not on my localhost

gdemaria

Yeah, good question. Perhaps a difference in database version?

SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial