Coldfusion - cfquery not returning correct data

Question

I am having a hard time figuring out why when the cfquery runs in my code it is only returning one row when it should be returning three rows.

In almost all cases this cfquery is returning the correct data. For one or two users the cfquery is returning only 1 row (out of 3). I take the output from the debug info that coldfusion spits out and run that query in toad and the correct results are shown.

What could cause the cfquery and toad results to be different? One of the columns I'm returning is user defined and maybe the column has some special characters in it that cause the result to not be returned? I am really stumped and noone around here knows any coldfusion to even help me think about this. So I turn to SO.

I'm searching on the web as we speak.

ADDED:

<cfquery name="getInfo" datasource="#DSN#">
    SELECT
        u.user_no, u.username, u.description
    FROM
        user_info u
    WHERE
        u.parent = #session.user_no#
    ORDER BY 
        u.username ASC
</cfquery>

Again, one a few users it returns one row out of three and on the rest it seems to return the all the correct resuts.

** UPDATE ** Not that anyone cares, but just Today this problem was resolved! If you want to know what it was I'll tell you.

Turns out the tester who was testing this piece of code (who claimed that she was clearing her cache!) didn't clear her cache! She had me running around trying to fix it up when she was feeding me some B.S. after I told her that was the likely problem. Oh well, I didn't spend too much time on it. Thanks all for the answers about the cfqueryparam, I'll be using those from now on in my coldfusion coding.

Cheers!

Answer 1

Always use cfqueryparam around variables in your queries!

Right, now that's out of the way, there is nothing in that code that will cause only one of three rows to be returned.

So, your problem is either data-related or elsewhere in the code.

If you immediately after that query do:

<cfdump var="#getInfo#"/><cfabort/>

Do you get one row or three rows?

• If it is one row, that suggests a data issue - I doubt non-ascii 'special' characters would cause this, but try returning just an integer to test that - or even SELECT 1 ... - if there are three rows you would get three 1s.

• If there are three rows there, it must be some later code which is causing the problem, so we would need to know what is happening next to be able to help.

Make sense?

Answer 2

Can you post the contents of the table?

Answer 3

I would use cfqueryparam to define the type of variable you are entering into your query.

It never hurts to be too specific with database queries and I have came across problems before with integers being evaluated as strings and therefore returning incorrect result sets.

(Added (main) bonus - cfqueryparam is always good for added protection against SQL injection attacks)

Answer 4

I suppose the error is in the data or your input (SESSION timed out/invalid?).

Actually, I've never witnessed a <cfquery> result differing from what the DB server would tell you on the console. It's just so improbable that I'd not consider it a possibility.

I suggest the following test scenario:

<cfquery name="AllUsers" datasource="#DSN#>
  SELECT 
    p.user_no,
    COUNT(u.user_no) ChildCount
  FROM
    user_info p
    LEFT JOIN user_info u ON p.user_no = u.parent
  GROUP BY
    p.user_no
  ORDER BY
    COUNT(u.user_no)
</cfquery>

<cfdump var="#AllUsers#">

There you will see exactly how many children each user has.

If it makes you feel safer, you can run a loop and check the results individually:

<cfloop query="AllUsers">
  <cfquery name="SingleUser" datasource="#DSN#">
    SELECT
      u.user_no, u.username, u.description
    FROM
      user_info u
    WHERE
      u.parent = #AllUsers.user_no#
  </cfquery>

  <cfif SingleUser.RecordCount neq AllUsers.ChildCount>
    <cfabort showerror="space-time rupture found for user #AllUsers.user_no#">
  </cfif>
</cfloop>

My bet would be that the <cfabort> is never being hit, which would prove that <cfquery> was functioning flawlessly and you'd have to look somewhere else.