• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Query Of Queries runtime error

Community Beginner ,
Feb 27, 2008 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.
TOPICS
Advanced techniques

Views

2.4K

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
community guidelines

correct answers 1 Correct answer

Community Beginner , Mar 04, 2008 Mar 04, 2008
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 ...

Votes

Translate

Translate
Mentor ,
Feb 27, 2008 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

Votes

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
community guidelines
Community Beginner ,
Feb 27, 2008 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>

Votes

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
community guidelines
Explorer ,
Feb 27, 2008 Feb 27, 2008

Copy link to clipboard

Copied

Please put a master query for review.

Votes

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
community guidelines
Mentor ,
Feb 27, 2008 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

Votes

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
community guidelines
Community Beginner ,
Feb 27, 2008 Feb 27, 2008

Copy link to clipboard

Copied

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

Votes

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
community guidelines
Community Beginner ,
Feb 27, 2008 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?

Votes

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
community guidelines
Explorer ,
Feb 27, 2008 Feb 27, 2008

Copy link to clipboard

Copied

Probably. Could you use just strait queries?

Votes

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
community guidelines
Community Beginner ,
Feb 27, 2008 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.

Votes

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
community guidelines
Community Beginner ,
Mar 04, 2008 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.

Votes

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
community guidelines
LEGEND ,
Feb 27, 2008 Feb 27, 2008

Copy link to clipboard

Copied

Cold Fusion converts null values to empty strings.

Votes

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
community guidelines
Guest
Feb 29, 2008 Feb 29, 2008

Copy link to clipboard

Copied

removed

Votes

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
community guidelines
Community Beginner ,
Jul 08, 2020 Jul 08, 2020

Copy link to clipboard

Copied

LATEST

Thanks! You just solved my problem.

Votes

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
community guidelines
Resources
Documentation