Highlighted

Query Of Queries runtime error

Community Beginner ,
Feb 27, 2008

Copy link to clipboard

Copied

Query Of Queries runtime error.
Unsupported Cast Excpetion: Casting to type "NULL" is unsupported.

I am getting this error when I try to query a query for a search tool that I developed. Problem is, it doesn't happen everytime. And, after dumping the main query, before I query it (when the error happens), there is nothing visible in the query that indicates a NULL casting. We have even filtered out special characters, thinking that might be the problem.

I was wondering, what really causes this error, and is it something I am doing, or maybe a bug in CF7. I did not get this error until our company recently upgraded to CF7.
Community Beginner
Correct answer by JayTee | Community Beginner
Just wanted to let everyone know the problem has been solved.

When I created the "Master Query", which is a Custom Query, I did not specify what type each field needed to be, so CF just assumes a type according to the value being placed into the first field. Let this be a lesson if you plan to merge Queries later, as I did.

CF assigned a type to a field, that had a number in it, as LONG. Well, later as I compiled queries, a particular row had a string value.
Everything was fine until I query the query, and try to select an integer value :

<cfquery name="qryAllCategoryList"
dbtype="query">
SELECT Category
FROM qryResultsAllCategoryList
WHERE (groupLevel = <cfqueryparam cfsqltype="cf_sql_integer" value="0"> or
groupLevel = <cfqueryparam cfsqltype="cf_sql_integer" value="1">)
GROUP BY Category
</cfquery>

The query of queries failed at : groupLevel = <cfqueryparam cfsqltype="cf_sql_integer" value="0

So the fix is, I have to assign a type to every field in my Custom Query.
When I build the "Master Query" it should look like this :

<cfset qryResults = QueryNew("anchor,category,description,expire_date,isgroup,groupLevel,ID,last_updated,parentID,PID,queryString,results,title,URL","varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar")>

Now each field has its own type, in this case they are all VARCHAR.

Thanks for everyone's help! I hope I explained the answer good enough.
TOPICS
Advanced techniques

Views

1.5K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Query Of Queries runtime error

Community Beginner ,
Feb 27, 2008

Copy link to clipboard

Copied

Query Of Queries runtime error.
Unsupported Cast Excpetion: Casting to type "NULL" is unsupported.

I am getting this error when I try to query a query for a search tool that I developed. Problem is, it doesn't happen everytime. And, after dumping the main query, before I query it (when the error happens), there is nothing visible in the query that indicates a NULL casting. We have even filtered out special characters, thinking that might be the problem.

I was wondering, what really causes this error, and is it something I am doing, or maybe a bug in CF7. I did not get this error until our company recently upgraded to CF7.
Community Beginner
Correct answer by JayTee | Community Beginner
Just wanted to let everyone know the problem has been solved.

When I created the "Master Query", which is a Custom Query, I did not specify what type each field needed to be, so CF just assumes a type according to the value being placed into the first field. Let this be a lesson if you plan to merge Queries later, as I did.

CF assigned a type to a field, that had a number in it, as LONG. Well, later as I compiled queries, a particular row had a string value.
Everything was fine until I query the query, and try to select an integer value :

<cfquery name="qryAllCategoryList"
dbtype="query">
SELECT Category
FROM qryResultsAllCategoryList
WHERE (groupLevel = <cfqueryparam cfsqltype="cf_sql_integer" value="0"> or
groupLevel = <cfqueryparam cfsqltype="cf_sql_integer" value="1">)
GROUP BY Category
</cfquery>

The query of queries failed at : groupLevel = <cfqueryparam cfsqltype="cf_sql_integer" value="0

So the fix is, I have to assign a type to every field in my Custom Query.
When I build the "Master Query" it should look like this :

<cfset qryResults = QueryNew("anchor,category,description,expire_date,isgroup,groupLevel,ID,last_updated,parentID,PID,queryString,results,title,URL","varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar")>

Now each field has its own type, in this case they are all VARCHAR.

Thanks for everyone's help! I hope I explained the answer good enough.
TOPICS
Advanced techniques

Views

1.5K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Feb 27, 2008 0
Mentor ,
Feb 27, 2008

Copy link to clipboard

Copied

You didn't post any code, so it is harder to guess what is going on. Perhaps your main query is returning NULLs in a column that you are using in the WHERE clause in your Q-of-Q. You might try adding an IS NOT NULL statement in your Q-of-Q for any columns that may be returned as NULL from your database query. Or, return a valid value of the correct type in your datbase query if the column is NULL using IsNull(), etc.

Phil

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 27, 2008 0
Community Beginner ,
Feb 27, 2008

Copy link to clipboard

Copied

Thank for the Reply,

I didn't post any code because it is real general. But I will if it helps. We have tried the IS NOT NULL statement as well, even stripped the WHERE clause completly out,

<cftry>

<!---Get Category List-------------------------------------------------->
<cfset qryResultsAllCategoryList = Duplicate(stSearchStructure.results)>

<cfquery name="qryAllCategoryList"
dbtype="query">

SELECT Category
FROM qryResultsAllCategoryList
WHERE (groupLevel = <cfqueryparam cfsqltype="cf_sql_numeric" value="0"> or
groupLevel = <cfqueryparam cfsqltype="cf_sql_numeric" value="1">)
GROUP BY Category
</cfquery>

<cfset intCategoryListCount = qryAllCategoryList.recordcount>
<!---------------------------------------------------------------------->

<cfcatch type="any">

<cfset bDisplayResults = 0>

<!--- Display Error ---------------------------------------------------------------------->
<cfset stErrorArg = structNew()> <!--- Structure of aurguments to pass into function --->
<cfset stErrorArg.strMSG = "Cannot Search at this time. Please try again later.">
<cfset stErrorArg.strErrorDescMSG = "site_search.cfc; fnBuildResultsPage Criteria :
#stSearchStructure.Criteria#, User was NOT notified, silent error">
<cfset stErrorArg.bUseJS = 0>
<cfset stErrorArg.bUseHTML = 1>
<cfset stErrorArg.bSendEmailToProgrammer = 1>
<cfset stErrorArg.stDump = cfcatch>
<cfset stErrorArg.bUseErrorLog = 1>

<!--- Call function 'fnDynamicCFCCall' to call any specified component dynamically --->
<cfset application.generalFunctions.fnDynamicCFCCall
("CFComponets.error","fnDisplayError",stErrorArg>
<!----------------------------------------------------------------------------------------->

<!--- <cfdump var="#cfcatch#" label="cfcatch"><br><br> --->
<!---<cfdump var="#qryResultsAllCategoryList#" label="qryResultsAllCategoryList"><br>--->

</cfcatch>

</cftry>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 27, 2008 0
Explorer ,
Feb 27, 2008

Copy link to clipboard

Copied

Please put a master query for review.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 27, 2008 0
Mentor ,
Feb 27, 2008

Copy link to clipboard

Copied

This doesn't work?

SELECT Category
FROM qryResultsAllCategoryList
WHERE groupLevel IN (0, 1)
AND Category IS NOT NULL
GROUP BY Category

Phil

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 27, 2008 0
Community Beginner ,
Feb 27, 2008

Copy link to clipboard

Copied

I wish that would work, but still the same error.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 27, 2008 0
Community Beginner ,
Feb 27, 2008

Copy link to clipboard

Copied

The master query is a custom query made from querys that actually touch the database. Its a search query, so I gather what I can from specific tables, and put them into my custom query with comman names, like title, description, ID, stuff like that. I try to keep the results (master query) as small as possible for performance.

Perhaps the custom query is the fault here?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 27, 2008 0
Explorer ,
Feb 27, 2008

Copy link to clipboard

Copied

Probably. Could you use just strait queries?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 27, 2008 0
Community Beginner ,
Feb 27, 2008

Copy link to clipboard

Copied

The way things are designed, at the moment using straight queries would not work. I would have to re-implement the entire CFC, but if I can't get this problem solved, I may have to do it.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 27, 2008 0
Community Beginner ,
Mar 04, 2008

Copy link to clipboard

Copied

Just wanted to let everyone know the problem has been solved.

When I created the "Master Query", which is a Custom Query, I did not specify what type each field needed to be, so CF just assumes a type according to the value being placed into the first field. Let this be a lesson if you plan to merge Queries later, as I did.

CF assigned a type to a field, that had a number in it, as LONG. Well, later as I compiled queries, a particular row had a string value.
Everything was fine until I query the query, and try to select an integer value :

<cfquery name="qryAllCategoryList"
dbtype="query">
SELECT Category
FROM qryResultsAllCategoryList
WHERE (groupLevel = <cfqueryparam cfsqltype="cf_sql_integer" value="0"> or
groupLevel = <cfqueryparam cfsqltype="cf_sql_integer" value="1">)
GROUP BY Category
</cfquery>

The query of queries failed at : groupLevel = <cfqueryparam cfsqltype="cf_sql_integer" value="0

So the fix is, I have to assign a type to every field in my Custom Query.
When I build the "Master Query" it should look like this :

<cfset qryResults = QueryNew("anchor,category,description,expire_date,isgroup,groupLevel,ID,last_updated,parentID,PID,queryString,results,title,URL","varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar")>

Now each field has its own type, in this case they are all VARCHAR.

Thanks for everyone's help! I hope I explained the answer good enough.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 04, 2008 0
LEGEND ,
Feb 27, 2008

Copy link to clipboard

Copied

Cold Fusion converts null values to empty strings.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 27, 2008 0
Explorer ,
Feb 29, 2008

Copy link to clipboard

Copied

removed

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 29, 2008 0
sheila2 LATEST
Community Beginner ,
Jul 08, 2020

Copy link to clipboard

Copied

Thanks! You just solved my problem.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 08, 2020 0