cfstoredproc scope issue?

[Guest]

When i try to display

<tr><td id="smNormalText">Country: <strong><cfoutput>#list_all_country_sp_rst.county_name#</cfoutput></strong></td></tr>

i get empty value....it is not displaying Country value coming from procedure

Here is code

<cfstoredproc procedure="list_all_country_sp" datasource="#dsn#" username="#user#" password="#pass#" returncode="yes" debug="yes">

        <cfprocresult name="list_all_country_sp_rst">    

</cfstoredproc>

    <cfloop query="list_all_country_sp_rst">      

    <cfstoredproc procedure="list_all_state_sp" datasource="#dsn#" username="#user#" password="#pass#" returncode="yes" debug="yes">

            <cfprocresult name="list_all_state_sp_rst">

            <cfprocparam type="in" cfsqltype="CF_SQL_VARCHAR" variable="county_name_in" value="#list_all_country_sp_rst.county_name#"  null="no">

        </cfstoredproc>   

        <cfloop query="list_all_state_sp_rst">   

        <cfstoredproc procedure="name_count_sp" datasource="#dsn#" username="#user#" password="#pass#" returncode="yes" debug="yes">

            <cfprocparam type="in" cfsqltype="CF_SQL_VARCHAR" variable="state_name_in" value="#list_all_state_sp_rst.state_name#"  null="no">

            <cfprocresult name="name_count_sp_rst">

        </cfstoredproc>

        <cfif name_count_sp_rst.name_count neq '0'> 

        <tr><td id="smNormalText">Country: <strong><cfoutput>#list_all_country_sp_rst.county_name#</cfoutput></strong></td></tr>

<tr><td id="smNormalText">State: <strong><cfoutput>#list_all_state_sp_rst.state_name#</cfoutput></strong></td></tr>

</cfloop>

</cfloop>

[mack_]

Try cfdump to see what values are returned from the stored procedure:

<cfdump var="#list_all_country_sp_rst#">

Mack

[Guest]

Tried with CFDump..

outer loop, cfdump is displaying value.

cfdump not displaying anything in inner loop...

Here is a Bug in CF. Please notfy to Adobe.

Can i get my money for finding bug?

[mack_]

I think this is the nested CFLOOP problem (in your inner CFLOOP you

access the outer loop variables). This is a known issue in ColdFusion,

please read the following blog entries:

- for CF7: http://www.bennadel.com/blog/322-Nesting-CFLoop-Tags-Of-The-SAME-Query-Hmmmm.htm

- for CF8 update:

http://www.bennadel.com/blog/322-Nesting-CFLoop-Tags-Of-The-SAME-Query-Hmmmm.htm

Mack

[Guest]

that is fine.

i am looking for a solution to this problem...

a workaround or something

please let me know.

[ilssac]

Well first you have to prove it is a bug.  What are you trying to do?  What do you expect this to do?  Why does this not do what you expect it to do?  How would you make it do what you expect it to do?  Does this work differently then similar constucts in other languages?

As you can see, there is quite a bsit more to declaring a bug then just "I don't get the results I want!"

I do not immeditaly get what your logic is trying to do.  It looks like you want loop over your record set and then loop over the entire record set each itteration?

Possible solutions:

1) Try using two references to your record set:

<cfquery ... name="refOne">
...
</cfquery>
<cfset refTwo = refOne>

2) Try using two copies of your record set:

<cfquery ... name="copyOne">
...
</cfquery>
<cfset copyTwo = duplicate(copyOne)>

3) Don't rely on the shortcut "query" parameter to loop over your record set.  Build your own record set looping logic:

<cfoutput>
     <cfloop from="1" to="#myQuery.recordCount#" index="outerLoop">
          #myQuery["aColumn"][outerLoop]#
          <cfloop from="1" to="#myQuery.recordCount#" index="innerLoop">
               #myQuery["aColumn"][innerLoop]
          </cfloop>
     </cfloop>
</cfoutput>

[Guest]

Thanks Ian,

i don't see anything wrong in my logic.

i call a stored procedure.

loop throw resultset using cfquery

call another stored procedure

loop throw resultset using cfquery

call another stored procedure

display first stored procedure variable

display second stored procedure variable

display third stored procedure variable

what is wrong here?

maybe cfquery loop got a bug.

please tell me how you will implement this logic?

Whatever you explain is not matching with this situation.

i am looking a way to correct this.

i need to use stored procedure...cuz query runs slow...

even by using stored procedure report is taking 10 mins to build.

[Guest]

my outer loop varible is not displaying all the time...some time it is displaying...sometime it looses output.

my inner loop varible is displaying always.

how will i fix this?

i used cfdump...

it is getting result in some case...some time it loosing result....stange behaviour.

maybe cf query loop do have a problem like this?

do i need startrow and endrow defined in cfquery loop?

[ilssac]

What do you mean "my outer loop looses output"?

Output is not easy to loose!  It sounds like you are sometimes getting results and sometimes not.  That could be the result of many things from SQL filters returning nothing to database connectivity issues.

I would start by simpliying your logic.  Start with the one stored procedure and the one loop.  Can you consistintly run that and get the expected results without fail?

If so, then add the next layer, if not start figuring out why the code is not producing consistent results.

P.S.

Whenever I see logic like this where one result is being looped over, and another query is being run to get related results.  95% of the time this can be done more effeciently with a propererly joined query that returns all the data once and then use nested output loops to display the results as desired.