Skip to main content
T
tderouen
Participant
September 12, 2012
Question

Query returns zero records in coldfusion context, but works fine in Navicat

  • September 12, 2012
  • 2 replies
  • 1841 views

I've got a query that's returning zero records when I load a page.  If I copy and paste that same query (from the debug output) into navicat, I get rows returned (as I expect).  Has anyone seen this?  It happens locally (CF9) AND remotely on our staging server (CF10).  Even weirder, it's a query that was previously working fine - I simply added an if statement to the where clause, and all of a sudden... 

Here's the query:

        <CFQUERY name="LOCAL.getEncounterServices" datasource="#REQUEST.dsn#">

        SELECT

            a.EncounterProductID,

            a.DateTime AS ServiceDate,

            aa.CartItemID,

            aaa.CartID,

            aaaaa.CartStatus,

            b.ProductID,

            b.ProductName,

            b.CPTCode,

            b.Price,

            c.EncounterID,

            c.DateTimeClosed AS EncounterClosedDate,

            d.FirstName,

            d.LastName

        FROM

            EncounterProducts a

                LEFT JOIN CartItemProduct aa ON (a.EncounterProductID = aa.EncounterProductID AND aa.Active = 1)

                LEFT JOIN CartItem aaa ON (aa.CartItemID = aaa.CartItemID)

                LEFT JOIN Cart aaaa ON (aaa.CartID = aaaa.CartID)

                LEFT JOIN CartStatus aaaaa ON (aaaa.CartStatusID = aaaaa.CartStatusID),

            Product b,

            Encounters c,

            Contacts d,

            EncounterStatuses e

        WHERE

            1 = 1

            AND (aa.CartItemID IS NULL OR aaaaa.CartStatus = 'Deleted')

            AND a.Active = 1

            AND a.ProductID = b.ProductID

            AND a.EncounterID = c.EncounterID

            AND c.PatientID = d.ContactID

            AND c.EncounterStatusID = e.EncounterStatusID

            AND e.EncounterStatus = 'Closed'

          <CFIF IsDefined("ARGUMENTS.encounter") AND IsObject(ARGUMENTS.encounter)>

                 AND c.EncounterID = <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#ARGUMENTS.encounter.getID()#">

         <CFELSE>

                AND c.DateTimeClosed >= <CFQUERYPARAM cfsqltype="cf_sql_date" value="#ARGUMENTS.startDate#">

                AND c.DateTimeClosed < <CFQUERYPARAM cfsqltype="cf_sql_date" value="#DateFormat(DateAdd('d', 1, ARGUMENTS.endDate), 'yyyy-mm-dd')# 00:00:00">

           </CFIF>

            AND c.LocationID = <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#ARGUMENTS.locationID#">

            AND c.CustomerID = <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#ARGUMENTS.customerID#">

        </CFQUERY>

All of this worked just fine before I added the lines:

         <CFIF IsDefined("ARGUMENTS.encounter") AND IsObject(ARGUMENTS.encounter)>

                 AND c.EncounterID = <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#ARGUMENTS.encounter.getID()#">

         <CFELSE>

                AND c.DateTimeClosed >= <CFQUERYPARAM cfsqltype="cf_sql_date" value="#ARGUMENTS.startDate#">

                AND c.DateTimeClosed < <CFQUERYPARAM cfsqltype="cf_sql_date" value="#DateFormat(DateAdd('d', 1, ARGUMENTS.endDate), 'yyyy-mm-dd')# 00:00:00">

          </CFIF>

Previously, it had just been:

                AND c.DateTimeClosed >= <CFQUERYPARAM cfsqltype="cf_sql_date" value="#ARGUMENTS.startDate#">

                AND c.DateTimeClosed < <CFQUERYPARAM cfsqltype="cf_sql_date" value="#DateFormat(DateAdd('d', 1, ARGUMENTS.endDate), 'yyyy-mm-dd')# 00:00:00">

With no IF/ELSE statement.

Anyone seen anything like this before?  Any ideas? 

Thanks.

    This topic has been closed for replies.

    2 replies

    R
    Reed_Powell-ttnmOb
    Inspiring
    September 12, 2012

    I'd start by including a RESULT="X" option in the CFQUERY and then putting a CFDUMP VAR="#x#" right after the query, to be absolutely sure that you know what the SQL is that's being executed.

    For GandG, verify that the datasource you're using is pointing to the same database you're doing the manual query from.  Just to be sure.  When crazy stuff like this happens, you gotta check the basics.

    Any chance some db software was upgraded (is this your box or a shared box?) at about the same time you make your changes?

    good luck,

    -reed

    T
    tderouenAuthor
    Participant
    September 12, 2012

    Hi Reed.  I had done this as well, and yes, the SQL that's being executed looks exactly the same.  In fact, copying the SQL from the CFDUMP output and pasting it into my NaviCat is how first assessed the situation.  Double-checking the datasource and database looks correct as well.

    This is occuring on two separate platforms.  My local machine (which I'm 100% sure there has been no software change recently) and a remote testing server (which I'm close to 100% sure that nothing has changed recently).

    I also tried clearing my browser's cache on the longshot that some lingering nonsense was causing a problem.

    Thank you guys for the help!

    D
    Dan_Bracuk
    Inspiring
    September 12, 2012

    When you look at the sql string in the debugging section, what is being passed to the db?  This:

    AND c.EncounterID = <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#ARGUMENTS.encounter.getID()#"> 

    or this

    AND c.DateTimeClosed >= <CFQUERYPARAM cfsqltype="cf_sql_date" value="#ARGUMENTS.startDate#">

    AND c.DateTimeClosed < <CFQUERYPARAM cfsqltype="cf_sql_date" value="#DateFormat(DateAdd('d', 1, ARGUMENTS.endDate), 'yyyy-mm-dd')# 00:00:00">

    By the way, using dateformat might work but it's improper and inefficient.  The function returns a string and you want to send a date as your parameter.

    T
    tderouenAuthor
    Participant
    September 12, 2012

    Right, I'll start disabusing myself of the DateFormat!

    I'm sorry, I should've posted the actual query too.  It's inserting the first part - "AND c.EncounterID = ....."

    Here's the full query:

    LOCAL.getEncounterServices (Datasource=xmddevdb, Time=9ms, Records=0) in /Applications/ColdFusion9/wwwroot/XMD_NEW/xmd_dev/cfc/ShoppingGateway.cfc @ 16:56:28.028

    SELECT

                a.EncounterProductID,

                a.DateTime AS ServiceDate,

                aa.CartItemID,

                aaa.CartID,

                aaaaa.CartStatus,

                b.ProductID,

                b.ProductName,

                b.CPTCode,

                b.Price,

                c.EncounterID,

                c.DateTimeClosed AS EncounterClosedDate,

                d.FirstName,

                d.LastName

            FROM

                EncounterProducts a

                    LEFT JOIN CartItemProduct aa ON (a.EncounterProductID = aa.EncounterProductID AND aa.Active = 1)

                    LEFT JOIN CartItem aaa ON (aa.CartItemID = aaa.CartItemID)

                    LEFT JOIN Cart aaaa ON (aaa.CartID = aaaa.CartID)

                    LEFT JOIN CartStatus aaaaa ON (aaaa.CartStatusID = aaaaa.CartStatusID),

                Product b,

                Encounters c,

                Contacts d,

                EncounterStatuses e

            WHERE

                1 = 1

                AND (aa.CartItemID IS NULL OR aaaaa.CartStatus = 'Deleted')

                AND a.Active = 1

                AND a.ProductID = b.ProductID

                AND a.EncounterID = c.EncounterID

                AND c.PatientID = d.ContactID

                AND c.EncounterStatusID = e.EncounterStatusID

                AND e.EncounterStatus = 'Closed'

     

                     AND c.EncounterID = ?

           

                AND c.LocationID = ?

                AND c.CustomerID = ?

    Query Parameter Value(s) -

    Parameter #1(cf_sql_integer) = 28
    Parameter #2(cf_sql_integer) = 16
    Parameter #3(cf_sql_integer) = 6

    Thansk again for the help!

    D
    Dan_Bracuk
    Inspiring
    September 12, 2012

    If this were my problem, I'd start troubleshooting by running the following in coldfusion.

    <cfquery name="x">

    SELECT 1 record

    FROM

    EncounterProducts a

    --LEFT JOIN CartItemProduct aa ON (a.EncounterProductID = aa.EncounterProductID AND aa.Active = 1)

    --LEFT JOIN CartItem aaa ON (aa.CartItemID = aaa.CartItemID)

    --LEFT JOIN Cart aaaa ON (aaa.CartID = aaaa.CartID)

    --LEFT JOIN CartStatus aaaaa ON (aaaa.CartStatusID = aaaaa.CartStatusID),

    --Product b,

    --Encounters c,

    --Contacts d,

    --EncounterStatuses e

    WHERE

    1 = 1

    --AND (aa.CartItemID IS NULL OR aaaaa.CartStatus = 'Deleted')

    --AND a.Active = 1

    --AND a.ProductID = b.ProductID

    --AND a.EncounterID = c.EncounterID

    --AND c.PatientID = d.ContactID

    --AND c.EncounterStatusID = e.EncounterStatusID

    --AND e.EncounterStatus = 'Closed'

    --AND c.EncounterID = 28

    --AND c.LocationID = ?16

    --AND c.CustomerID = 6

    </cfquery>

    <cfdump var="#x.recordcount#">

    Then I would start uncommenting lines until the recordcount = 0.

    I did notice something though.  You are referring to left join tables in your where clause.  That makes it an inner join.  The solution is to move those conditions to the from clause.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices