I guess there isn't a way to include code without urlencoding it?
<cffunction name="getCourseList" access="remote" returntype="string" returnformat="json">
<cfargument name="loginname" type="string" required="yes">
<cfset var sendCourseList = "" >
<cfquery name="getCourseList" datasource="courses">
WHERE loginname = '#ARGUMENTS.loginname#%'
ORDER BY courseNum ASC
<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 --->
<cflog file="migrationList" application="no" text="User #ARGUMENTS.loginname# began the getCourseList cfloop.">
<cfquery name="getMigrationList" datasource="myListDb" result="tmpResult">
WHERE courseID = <cfqueryparam value="#getCourseList.courseNum#"> AND username = '#ARGUMENTS.loginname#%'
ORDER BY courseID ASC
<cfif tmpResult.recordcount IS 0 >
<cflog file="migrationList" application="no" text="User #ARGUMENTS.loginname# : course #getCourseList.COURSE_NAME# not found, adding via try ">
<cfquery name="addToMigrationList" datasource="mycourseList" result="courseAdded">
INSERT INTO migrationTrack (courseID, username, courseName, status) VALUES( '#getCourseList.courseNum#', '#getCourseList.loginname#', '#getCourseList.COURSE_NAME#' , '0' )
<cflog file="migrationList" application="no" text="User #ARGUMENTS.loginname# : course #getCourseList.COURSE_NAME# added ">
<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? ">
<!--- only the code below executes when put in a cfc / cffunction --->
<cfquery name="sendCourseList" datasource="mycourseList">
SELECT courseID, courseName, status, isMigrated, isQueued, copyOnly
WHERE username = <cfqueryparam value="#Arguments.loginname#" cfsqltype="cf_sql_clob" maxlength="255">
ORDER BY status, courseName ASC
1 person found this helpful
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?
* 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.
* 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.
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!
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!