Query was empty error while selecting and updating the record?

Question

I am wondering why I am getting this error "Query is Empty" as I checked the queries which are working fine in Sqlyog. Can anyone please help me out by explaning what's wrong in this query ? Any help would be appreciated.

This is my code. I am using Railo 4.2.0 on Ubuntu.

<cfquery name="q" datasource="#getDSN()#">
    SELECT is_nav_item, nav_order, MAX(nav_order) AS maxNavOrder 
    FROM content
    WHERE id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.id#">
    OR site__id = <cfqueryparam value="#arguments.site__id#" />
</cfquery>

<cfquery datasource="#getDSN()#">
    <!--- 
       check to see if existing record has 0 and in arguments it is 
       passed as 1 then we are going to update the nav order by selecting 
       max nav order in the table then incrementing the maxNavOrder by 1
    --->
    <cfif q.is_nav_item EQ 0 AND arguments.is_nav_item EQ 1>
        UPDATE content
        SET nav_order = #q.maxNavOrder+1#
        WHERE id = <cfqueryparam value="#arguments.id#" cfsqltype="cf_sql_integer" />
        AND site__id = <cfqueryparam value="#arguments.site__id#" />
    </cfif>

    <!--- 
        check to see if the existing record have is_nav_item eq 1 and in 
        the arguments it is 0 if it is then its going to update the nav_order 
        to 0... so that we can prevent it being updating the nav order in case 
        someone not updating nav_item but something else
    --->
    <cfif q.is_nav_item EQ 1 AND arguments.is_nav_item EQ 0>
        UPDATE content
        SET nav_order = 0
        WHERE id = <cfqueryparam value="#arguments.id#" cfsqltype="cf_sql_integer" />
        AND site__id = <cfqueryparam value="#arguments.site__id#" />
        ;
    </cfif>
 </cfquery>

Gavin Pickin Gavin Pickin · Accepted Answer · 2014-01-17T19:53:43

You have if statements inside your query... and if those both do not evaluate as true, your query could be an empty query.

Why not do an IF and put the whole query inside the IF statement... that way there is no way of getting an CFQUERY without any text in it.

<!--- 
  check to see if existing record has 0 and in arguments it is passed as 1 
  then we are going to update the nav order by selecting max nav order in 
  the table then incrementing the maxNavOrder by 1
--->
<cfif q.is_nav_item EQ 0 AND arguments.is_nav_item EQ 1>
     <cfquery datasource="#getDSN()#">
        UPDATE content
        SET    nav_order = #q.maxNavOrder+1#
        WHERE  id = <cfqueryparam value="#arguments.id#" cfsqltype="cf_sql_integer" />
        AND site__id = <cfqueryparam value="#arguments.site__id#" />
     </cfquery>
</cfif>

<!--- 
    check to see if the existing record have is_nav_item eq 1 and in the 
    arguments  it is 0.  if it is then its going to update the nav_order 
    to 0... so that we can prevent it being updating the nav order in case 
    someone not updating nav_item but something else
--->
<cfif q.is_nav_item EQ 1 AND arguments.is_nav_item EQ 0>
    <cfquery datasource="#getDSN()#">
       UPDATE content
       SET    nav_order = 0
       WHERE  id = <cfqueryparam value="#arguments.id#" cfsqltype="cf_sql_integer" />
       AND    site__id = <cfqueryparam value="#arguments.site__id#" />
    </cfquery>
</cfif>

Query was empty error while selecting and updating the record?

1 Answers