Coldfusion Oracle query results are inaccurate

[aofficer]

I have run the exact same query in toad and in oci8 php and get the same results. In coldfusion the results are different. I made sure the query is not being cached. What could be the reason for this?

[ilssac]

aofficer wrote:

What could be the reason for this?

1. Your not connecting to the database you think you are connecting to.
2. Your not passing in the same parameters you think you are passing in
3. Different database drivers
4. Others that are hard to think of or consider without some more information on your part.
   1. Database version
   2. ColdFusion version
   3. example of the code
   4. example of the results
   5. etc

[Adam Cameron.]

Added to that list: different DB user.  That's what - in my (admittedly not comprehensive ~) experience - usually causes these things with Oracle.

--

Adam

[aofficer]

DB version 11

CF version 9
CF driver oracle
CF query<cfquery name="getUser_ac" datasource="oracle">
        SELECT sibinst_pidm as pidm
        FROM sibinst a
        WHERE a.sibinst_fcst_code = 'AC'
</cfquery>
<cfoutput>
    <cfloop query = "getUser_ac">
        #getUser_ac.pidm#<br>
      </cfloop>
</cfoutput>TOAD querySELECT sibinst_pidm as pidm
FROM sibinst a
WHERE a.sibinst_fcst_code = 'AC'
CF results Records=5845
TOAD results Records=5912If you need more information let me know.

[JR__Bob__Dobbs]

CF results Records=5845

Is this number the value of getUser_ac.recordCount?

[ilssac]

When you say "CF Driver Oracle" are you refering to the Oracle Native JDBC 4 Driver that is only available in ColdFusion Enterprise and Developer?  Or are you using the Oracle Thin Client, or heaven's forbid, the Oracle ODBC driver?  Or possible some other Oracle driver?

[Dan_Bracuk]

In what way were they different?

Are you sure you were connected to the same database each time?

[aofficer]

I am absolutely positive that it is the same database and table. The TOAD query is 100% accurate but cfquery misses about a hundred records. I would show you the results but its very long.

[JR__Bob__Dobbs]

Do the table and/or login have any triggers associated with them?

[Dan_Bracuk]

If this were my problem, I'd identify the TOAD records that did not get returned by ColdFusion and try to figure out why.  One thing I'd be looking for would be leading or trailing spaces in sibint.sibinst_fcst_code.