Skip to main content
illovich
illovich
Inspiring
April 6, 2010
Question

Flow Differences between .cfm and cffunction in a .cfc?

  • April 6, 2010
  • 1 reply
  • 807 views

I need to manage a list of courses that exist in a database, but for various (good) reasons I can't store extra data in the primary database.  So I wrote up a relatively simple cfm page that had the following flow:

(first, make sure the lists are identical)

cfquery (*, mainCourseDb)

     cfloop

          for each courseID

                    cfquery (*, myListDb where courseID is mainCourseDb.courseID)

                    if recordcount IS 0

                    cfquery(insert mainCourseDb.* into myListDb.*)

      /cfloop

(then, show the user their list)

cfquery (*, where user is the current user)

When I do this in a cfm, everything works fine.  But when I converted this to a cfc inside of a cffunction, only the last part works. 

Can anyone explain why the first block of code doesn't execute at all when it's in a cffunction or cfc?

I'll add the actual code incase that helps (I changed some names to obscure actual datasources, table names, etc... also, I'm using cflog to trace what's executing), to reiterate this works if I pull the code in the cffunction out and just stick it in a cfm, but in a cfc (or in a cffunction) only the last query executes ... the cfloop stuff at the top doesn't happen:


<cfcomponent>
     
<cffunction name="getCourseList" access="remote" returntype="string" returnformat="json">
      <cfargument name="loginname" type="string" required="yes">
      <cfset var sendCourseList = ""  >

      <cfquery name="getCourseList" datasource="courses">
                SELECT *
                FROM dbo.mainCourseDb
                WHERE loginname = '#ARGUMENTS.loginname#%'
                ORDER BY courseNum ASC 
       </cfquery>
            
      <cflog file="migrationList" application="no" text="User #ARGUMENTS.loginname# requested a list.">  
            
      <!--- loop through user's  courses to compare course IDs (courseNum) to migration list --->
      <cfloop query="getCourseList">
           
        <cflog file="migrationList" application="no" text="User #ARGUMENTS.loginname# began the getCourseList cfloop.">  
      

        <cfquery name="getMigrationList" datasource="myListDb" result="tmpResult">
                            SELECT *
                            FROM migrationTrack
                            WHERE courseID = <cfqueryparam value="#getCourseList.courseNum#"> AND username = '#ARGUMENTS.loginname#%'
                            ORDER BY courseID ASC 
                    </cfquery>


  
  
        <cfif tmpResult.recordcount IS 0 >
        
             <cflog file="migrationList" application="no" text="User #ARGUMENTS.loginname# :  course #getCourseList.COURSE_NAME# not found, adding via try  ">  
        
          <cftry>
            <cfquery name="addToMigrationList" datasource="mycourseList" result="courseAdded">
                                    INSERT INTO migrationTrack (courseID, username, courseName, status) VALUES( '#getCourseList.courseNum#', '#getCourseList.loginname#', '#getCourseList.COURSE_NAME#'  , '0' )
                                </cfquery>
                                
                                <cflog file="migrationList" application="no" text="User #ARGUMENTS.loginname# :  course #getCourseList.COURSE_NAME# added  "> 
            <cfcatch type=”any”>
              <cflog file="migrationList" application="no" text="User #ARGUMENTS.loginname# : <strong></strong> course #getCourseList.COURSE_NAME# not added - An error of some kind occured; check logs? "> 
            </cfcatch>
          </cftry>
        </cfif>
      </cfloop>
      
      <!--- only the code below executes when put in a cfc / cffunction --->
      
                          <cfquery name="sendCourseList" datasource="mycourseList">
                                SELECT courseID, courseName, status, isMigrated, isQueued, copyOnly
                                FROM migrationTrack
                                WHERE username = <cfqueryparam value="#Arguments.loginname#" cfsqltype="cf_sql_clob" maxlength="255">
                                ORDER BY status, courseName ASC 
                          </cfquery>
                      
                      
                      <cfreturn serializeJSON(sendCourseList)>
                       
    </cffunction>



</cfcomponent>
This topic has been closed for replies.
1 reply
illovich
illovichAuthor
Inspiring
April 6, 2010
I guess there isn't a way to include code without urlencoding it?
<cfcomponent>
   
   <cffunction name="getCourseList" access="remote" returntype="string" returnformat="json">
   <cfargument name="loginname" type="string" required="yes">
   <cfset var sendCourseList = ""  >
<cfquery name="getCourseList" datasource="courses">
   SELECT *
   FROM dbo.mainCourseDb
   WHERE loginname = '#ARGUMENTS.loginname#%'
   ORDER BY courseNum ASC 
   </cfquery>
   
   <cflog file="migrationList" application="no" text="User   #ARGUMENTS.loginname# requested a list."> 
   
   <!--- loop through user's courses to compare course IDs (courseNum) to migration   list --->
   <cfloop query="getCourseList">
   
   <cflog file="migrationList" application="no" text="User   #ARGUMENTS.loginname# began the getCourseList cfloop.">
<cfquery name="getMigrationList" datasource="myListDb" result="tmpResult">
   SELECT *
   FROM migrationTrack
   WHERE courseID = <cfqueryparam value="#getCourseList.courseNum#"> AND   username = '#ARGUMENTS.loginname#%'
   ORDER BY courseID ASC 
   </cfquery>

   
   <cfif tmpResult.recordcount IS 0 >
   
   <cflog file="migrationList" application="no" text="User   #ARGUMENTS.loginname# : course #getCourseList.COURSE_NAME# not found, adding   via try  "> 
   
   <cftry>
   <cfquery name="addToMigrationList" datasource="mycourseList" result="courseAdded">
   INSERT INTO migrationTrack (courseID, username, courseName, status) VALUES(   '#getCourseList.courseNum#', '#getCourseList.loginname#', '#getCourseList.COURSE_NAME#'   , '0' )
   </cfquery>
   
   <cflog file="migrationList" application="no" text="User   #ARGUMENTS.loginname# : course #getCourseList.COURSE_NAME# added  "> 
   <cfcatch type=”any”>
   <cflog file="migrationList" application="no" text="User   #ARGUMENTS.loginname# : <strong></strong> course #getCourseList.COURSE_NAME#   not added - An error of some kind occured; check logs? "> 
   </cfcatch>
   </cftry>
   </cfif>
   </cfloop>
   
   <!--- only the code below executes when put in a cfc / cffunction --->
   
   <cfquery name="sendCourseList" datasource="mycourseList">
   SELECT courseID, courseName, status, isMigrated, isQueued, copyOnly
   FROM migrationTrack
   WHERE username = <cfqueryparam value="#Arguments.loginname#" cfsqltype="cf_sql_clob" maxlength="255">
   ORDER BY status, courseName ASC 
   </cfquery>
   
   
   <cfreturn serializeJSON(sendCourseList)>
   
   </cffunction>
</cfcomponent>
A
Adam Cameron.
Inspiring
April 6, 2010
When you say only the bottom bit works, what do you mean?  What does the top bit do instead of working?  How does the "not working" manifest itself?  Does it error?  Do the log entries not appear?  Do the queries not show up as executing in the debug?
Questions:
* does your getCourseList query return any records?  If not, your loop won't loop over anything.
* your WHERE filter on that query looks odd.  What's with the %?  I'm aware % is a zero-or-more wildcard for a LIKE expression, but does it do anything meaningful in a simple = expression?  Or do your login IDs simply all end in % signs?  Weird.  However further down you do a similar filter on a different table without the %.  Also this points to your data not being normalised.
* what's the getMigrationList query for?  If you're really only wanting to check how many records there are matching those conditions, don't do SELECT * as you're returning a bunch of stuff you don't need (which adds to processing overhead on both the DB and CF.  And in transit between the two.  Just do a COUNT(id) instead.
* does getMigrationList return zero records?  If it doesn't... a bunch of your logic gets skipped.
Obviously none of this stuff should vary between a CFM-based and a CFC-based implementation of the "same" code, but it's all stuff you should be checking as part of troubleshooting.  Don't assume things are the same just because you think they ought to be.
Tips:
* VAR all your variables within a function;
* use <cfqueryparam> on all dynamic values within a query, not just some of them.
These two have nothing to do with your problem - well... I can't see how they would be, but we've not really defined what your problem is yet - but it's good form.
--
Adam
illovich
illovichAuthor
Inspiring
April 6, 2010
Hey Adam,
Thanks for your advice and your tips. 
I went through and started implementing your advice, and it's working now even though I haven't finished.  I had only removed the 'select *' stuff and that weird % (to tell you the truth, I don't know where that % came from, but I feel like I took it out once and it broke the query, but I can't tell you for sure), but I'm not sure which fixed it - probably removing the %?
To answer your other question, what I meant was that even though I wasn't having a problem with an of those queries returning 0 records when using the cfm page, the cfc page only seemed to execute the last query on the page - the other log statements weren't executed, nothing. But also no errors in the logs, so it was weird. 
But now it's working - I'm planning to take a second pass to fix it up so it's better/more elegant/more efficient, but that's something for tomorrow.  
Thanks again for your help!
Peter

        
                                                

        

    


            

                                        

            
                        

                                                                                                            
            







        

                    

    
    





    


                                        

                                                        

            
        
    






                                    
Terms & ConditionsAccessibility statement

            
        



        
            
    
        
    
        



        
        
        


        


        
        
        
        
                                                        

                            
    

                    

  

    

      
        
      
    

    

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