Strange problem - Element RECORDCOUNT is undefined in query

[Scott Thornton]

Hi,

 

Running across a odd problem in CF 9,0,0,251028. development server.

 

I was just running a process that usually works and has not been modified for quite a long time. part of that process runs a query and then loops over the result set. Simple enough… however the query does not seem to exist after it has run.

 

eg: The following code returns Element RECORDCOUNT is undefined in FIX_THIS.

 

 

<cfquery  datasource="#DSN#" name="fix_this">

                select sb_invoice.sb_invoice_id, sb_invoice.sb_reminder_date, max(sb_claim_date) as sb_claim_date

                from sb_invoice

                inner join sb_invoice_item

                on sb_invoice.sb_invoice_id = sb_invoice_item.sb_invoice_id

                inner join sb_item_claim

                on sb_invoice_item.sb_invoice_item_id = sb_item_claim.sb_invoice_item_id

                where sb_invoice.sb_bill_mech_code in ('M','F','AG','SC','MB','MO')

                and sb_invoice_item.sb_item_status_code in ('21','24','31')

                group by sb_invoice.sb_invoice_id, sb_invoice.sb_reminder_date

                having sb_invoice.sb_reminder_date < DateAdd(d,30,max(sb_claim_date))

</cfquery>

 

<cfif fix_this.recordcount gt 0>

 

                <cfoutput query="fix_this">

               

                                <cfquery name="upd_fund_reminder" datasource="#DSN#">

                                                update sb_invoice

                                                set sb_reminder_date = <cfqueryparam cfsqltype="cf_sql_varchar" value="#DateFormat(DateAdd("d",31,fix_this.sb_claim_date),"dd-mmm-yyyy")#">

                                                where sb_invoice_id = <cfqueryparam cfsqltype="cf_sql_numeric" value="#fix_this.sb_invoice_id#">

                                </cfquery>

                               

                </cfoutput>

 

</cfif>

 

I have changed the name of the query to a few different values, I have used cfloop instead of cfoutput, I added the cfif.recordcount to see if that helped, to no avail. Without the CFIF the error was :

Attribute validation error for tag cfoutput.
The value of the attribute query, which is currently fix_this, is invalid.

 

 

Debug output shows that the query executed in 922ms and returned no rows.

 

What am I missing?

 

[m...@ampersand.net.au]

What happens if you try CFDUMP instead right after the query?

--
You received this message because you are subscribed to the Google Groups "cfaussie" group.
To post to this group, send email to cfau...@googlegroups.com.
To unsubscribe from this group, send email to cfaussie+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.

[Andrew Scott]

Not sure why you are getting that error, but there are a few things I will ask why? Mainly for others who might be newer to ColdFusion and reading this.

 

This

 

<cfif fix_this.recordcount gt 0>

 

Can be written

 

<cfif fix_this.recordcount>

 

Any number above zero will always return true.

 

Also this

 

                <cfoutput query="fix_this">

 

You are not actually outputting anything, so why are you not using a cfloop?

 

And lastly, I think you might need to upgrade to CF9.01 with all the latest hotfixes just in case your issue was fixed in the 9.01 or the patch just after the 9.01 was released.

 

Regards,

Andrew Scott

http://www.andyscott.id.au/

 

--

[Cassie Woolley]

I had this exact same problem a few months ago.  Unfortunately I can’t remember exactly what the problem was but I do remember it was a smack myself in the head kind of answer.  I have a feeling that even though the query was appearing to run OK, there was actually an issue with it that meant it wasn’t available for use.  If you haven’t already, double check the query in SQLServer or try a much simpler query with the same name and then build it back up to its current state one piece at a time.  If I remember exactly what it was I’ll reply again.

 

From: cfau...@googlegroups.com [mailto:cfau...@googlegroups.com] On Behalf Of Scott Thornton

Sent: Friday, 1 October 2010 9:36 AM
To: cfau...@googlegroups.com

--

[Mark Ireland]

#DateAdd(d,30,max(sb_claim_date))#

---

From: cas...@bluerocksoftware.com
To: cfau...@googlegroups.com
Subject: RE: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in query
Date: Fri, 1 Oct 2010 09:53:07 +1000

[Cassie Woolley]

There is a SQLServer DateAdd function that I think it is using

http://www.w3schools.com/sql/func_dateadd.asp

 

[Brett Payne-Rhodes]

Maybe you also have "fix_this" defined in a different scope?

B)

> *Attribute validation error for tag cfoutput. *

>
>
>
> The value of the attribute query, which is currently fix_this, is invalid.
>
> Debug output shows that the query executed in 922ms and returned no rows.
>
> What am I missing?
>

> --
> You received this message because you are subscribed to the Google Groups "cfaussie" group.
> To post to this group, send email to cfau...@googlegroups.com.
> To unsubscribe from this group, send email to cfaussie+u...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
>
>
>
>

> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.856 / Virus Database: 271.1.1/3167 - Release Date: 09/30/10 03:50:00
>

--
Brett Payne-Rhodes
YourSite Web Solutions
w: http://www.yoursite.net.au
e: br...@ehc.net.au
t: +61 (0)8 9371-0471
m: +61 (0)414 371 047

[Scott Thornton]

HI All,

 

1. What happens if you try CFDUMP instead right after the query?

 

using

<cfdump var="#fix_this#"><cfabort>

 

returns

 

Variable FIX_THIS is undefined.

 

2. re recordcount gt 0.

 

I didn't know that, thanks.

 

3.  Why the cfoutput?

 

The original code used cfloop, I tried using cfoutput as well. Typically cfloop would be used.

 

4. Update CF?

 

Will try that now.

 

5. Does the query run (as is)?

 

Yes it does, however it does not return any rows ( in SQl Query Analyzer 2000). The database is MS SQL 2005

 

5. Much simplier query?

 

<cfquery  datasource="#DSN#" name="fix_this">

                select top 1 * from sb_invoice

</cfquery>

 

<cfdump var="#fix_this#"><cfabort>

 

works fine.

 

Sooo, I will update my local server first, then put the orginal query back in and see how it goes.

[Steve Onnis]

If a simple query works i would be taking stuff out stripping stuff out of the query and seeing what is making it not return anything and i would be starting with the HAVING clause. It is possible that there is something in there that is making it not return anything.

 

Out of curiosity, what happens if you add the “result” attribute into the query and  dump the result value? What do you get?

=======
Email scanned by PC Tools - No viruses or spyware found.
(Email Guard: 7.0.0.21, Virus/Spyware Database: 6.15990)
http://www.pctools.com
=======

=======
Email scanned by PC Tools - No viruses or spyware found.
(Email Guard: 7.0.0.21, Virus/Spyware Database: 6.15990)
http://www.pctools.com
=======

[Scott Thornton]

Hello,

 

Thanks for all replies.

 

An update of my local dev server to 9,0,1,274733 appears to have solved the problem.

 

 

 

From: cfau...@googlegroups.com [mailto:cfau...@googlegroups.com] On Behalf Of Scott Thornton

Sent: Friday, 1 October 2010 10:29 AM
To: cfau...@googlegroups.com

[christophe albrech]

Hi Scott,

If I may, your code could be improved performance-wise. It is a classic example of Rbar programming (pronounced reebar = Row by agonizing row) as the process is not written in a set-based way, which is exactly what DB servers are really good (and really fast) at.

If you think about it, the code loops over each record in the first query (the cfif is useless by the way) , and then does an update for each one. if you have a thousand records, that's a thousand loops, a thousand queries sent through the wire, a thousand query the optimizer has to parse, and so on. What you really want to do is have a single update statement instead.

The problem in your case is that using aggregation forces you to use a group by statement. On top of that you need the aggregated data to perform the set operation which complicates the matter. While writing one single statement is possible it's probably easier to read if you use a temp table to store the maxed date and then write a single update. like so:

<cfquery>

SELECT  sb_invoice.sb_invoice_id,

        MAX(sb_claim_date) AS sb_claim_date

INTO    ##tempTable

FROM    sb_invoice

        INNER JOIN sb_invoice_item ON sb_invoice.sb_invoice_id = sb_invoice_item.sb_invoice_id

        INNER JOIN sb_item_claim ON sb_invoice_item.sb_invoice_item_id = sb_item_claim.sb_invoice_item_id

WHERE   sb_invoice.sb_bill_mech_code IN ( 'M', 'F', 'AG', 'SC', 'MB', 'MO' )

        AND sb_invoice_item.sb_item_status_code IN ( '21', '24', '31' )

GROUP BY sb_invoice.sb_invoice_id,

        sb_invoice.sb_reminder_date

HAVING  sb_invoice.sb_reminder_date < DATEADD(d, 30, MAX(sb_claim_date))

UPDATE  i

SET     sb_reminder_date = DATEADD(d, 31, t.sb_claim_date)

FROM    sb_invoice i

        INNER JOIN #tempTable t ON i.sb_invoice_id = t.sb_invoice_id

</cfquery>

The more records the initial select returns, the more resources you save. I just tried a simple example on my local machine where I tried to update 10k rows with both methods. the rbar one took about 10 seconds while the set-based one took less than half a second. trying with 100k rows, I got 70 seconds against 2.5.

Let's keep in mind that lots of updates wouldn't need the temp table in the first place. It's only there because because you were updating with an aggregated value. Normally you'd just do:

UPDATE t

set blah = 6

FROM myTable t

 JOIN otherTable o on o.id = t.fk

 ...

 ...

Anyway, speaking of agonizing stuff, I'm off to see the dentist.

Cheers,

Tof

[Gavin Beau Baumanis]

You learn something new every day - awesome, Thanks Chris!

As always, please contact me if I can be of any futher assistance.

Gavin "Beau" Baumanis

Senior Application Developer

PalCare Pty. Ltd.

P: +61 -3 9380 3513
M: +61 -438 545 586
E: be...@palcare.com.au
W: http://palcare.com.au

[Hanswurst Globobrüll]

I had exactly the same problem in CF8, CF9 und also CF11.

Out team was analyzing and examining for days (!) - because the problem was not repleatable on purpose. It just occured sometimes and we found evidence in log files.

and finally we found out that the problem is the database. we use mysql, and when mysql (under huge load) creates deadlocks and a single request just times out, the <cfquery variable has no content, ergo recordcount does not exist because the query result could not be created.

observe your database as a first step!

best regards

raffael

Onlineumfragen.com

[Mike K]

Have you run the query in your database to be certain it actually should return some rows?       And that there's no syntax errors or anything like that in the query itself?      It's possible that your CFQuery is correctly returning no rows. 

Cheers,

Mike Kear

Windsor, NSW, Australia 

AFP Webworks.

--

You received this message because you are subscribed to the Google Groups "cfaussie" group.

To unsubscribe from this group and stop receiving emails from it, send an email to cfaussie+unsubscribe@googlegroups.com.

To post to this group, send email to cfau...@googlegroups.com.

Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

--

Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month

[Scott Thornton]

HI Guys,

 

That post was from 2010!

--

To unsubscribe from this group and stop receiving emails from it, send an email to cfaussie+u...@googlegroups.com.

To post to this group, send email to cfau...@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

 

--

Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month

--

You received this message because you are subscribed to the Google Groups "cfaussie" group.

To unsubscribe from this group and stop receiving emails from it, send an email to cfaussie+u...@googlegroups.com.

To post to this group, send email to cfau...@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

Unless explicitly attributed, the opinions expressed in this email are those of the author only and do not represent the official view of Hunter New England Local Health District nor the New South Wales Government.

 

[Peter Tilbrook]

​​

Wouldn't it be better to use StructKeyExists(QueryName, RecordCount)?

==

Peter Tilbrook
Professional Adobe ColdFusion 2016

and Microsoft SQL Server Application Development
Managing Director, ColdGen Internet Solutions

Web Administrator, The Club Group Pty Ltd
PO Box 2247
Queanbeyan, NSW, 2620
AUSTRALIA

Tel: +61-2-5100-8725
Mob: +61-0478-623-579

Email Address: peter.t...@coldgen.com
WWW: http://www.coldgen.com/

--

To unsubscribe from this group and stop receiving emails from it, send an email to cfaussie+unsubscribe@googlegroups.com.

To post to this group, send email to cfau...@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

 

--

Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month

--
You received this message because you are subscribed to the Google Groups "cfaussie" group.

To unsubscribe from this group and stop receiving emails from it, send an email to cfaussie+unsubscribe@googlegroups.com.

To post to this group, send email to cfau...@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

Unless explicitly attributed, the opinions expressed in this email are those of the author only and do not represent the official view of Hunter New England Local Health District nor the New South Wales Government.

 

--

You received this message because you are subscribed to the Google Groups "cfaussie" group.

To unsubscribe from this group and stop receiving emails from it, send an email to cfaussie+unsubscribe@googlegroups.com.

[Mike K]

I wonder why it suddenly appeared in the mailbox then?     I admit i didnt even look at the date.    

Cheers

Mike Kear

--

To unsubscribe from this group and stop receiving emails from it, send an email to cfaussie+unsubscribe@googlegroups.com.

To post to this group, send email to cfau...@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

 

--

Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month

--
You received this message because you are subscribed to the Google Groups "cfaussie" group.

To unsubscribe from this group and stop receiving emails from it, send an email to cfaussie+unsubscribe@googlegroups.com.

To post to this group, send email to cfau...@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

Unless explicitly attributed, the opinions expressed in this email are those of the author only and do not represent the official view of Hunter New England Local Health District nor the New South Wales Government.

 

--

You received this message because you are subscribed to the Google Groups "cfaussie" group.

To unsubscribe from this group and stop receiving emails from it, send an email to cfaussie+unsubscribe@googlegroups.com.

To post to this group, send email to cfau...@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.