11 Replies Latest reply on Mar 4, 2008 12:45 PM by JayTee

    Query Of Queries runtime error

    JayTee
      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.
        • 1. Query Of Queries runtime error
          paross1 Level 2
          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
          • 2. Re: Query Of Queries runtime error
            JayTee Level 1
            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>
            • 3. Re: Query Of Queries runtime error
              CF_Oracle Level 1
              Please put a master query for review.
              • 4. Query Of Queries runtime error
                paross1 Level 2
                This doesn't work?

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

                Phil
                • 5. Re: Query Of Queries runtime error
                  JayTee Level 1
                  I wish that would work, but still the same error.
                  • 6. Re: Query Of Queries runtime error
                    Dan Bracuk Level 5
                    Cold Fusion converts null values to empty strings.
                    • 7. Re: Query Of Queries runtime error
                      JayTee Level 1
                      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?
                      • 8. Re: Query Of Queries runtime error
                        CF_Oracle Level 1
                        Probably. Could you use just strait queries?
                        • 9. Re: Query Of Queries runtime error
                          JayTee Level 1
                          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.
                          • 11. Re: Query Of Queries runtime error
                            JayTee Level 1
                            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,pare ntID,PID,queryString,results,title,URL","varchar,varchar,varchar,varchar,varchar,varchar,v archar,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.