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

Report · Apr 06, 2010

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.">  <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>  <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>

Report · Apr 06, 2010

I guess there isn't a way to include code without urlencoding it?

<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.">

   
   <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>

   

   <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>

Report · Apr 06, 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

Report · Apr 06, 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

Report · Apr 06, 2010

Also, I meant to mark your answer as "the answer" but I hit "helpful" by mistake… and I can't seem to take it back. Sorry!