Update/Edit a database through cfajaxproxy

[salimzayat]

Hey all.  I have been trying to get this to work for two days and am stuck.  I want to be able to edit a database through a cfajaxproxy function.  Here is what I have:

<cfajaxproxy cfc="#application.cfcRoot#schedulerFunctions" jsclassname="schedulerProxy">
        <script>
            var schedulerProxy = new schedulerProxy();

            schedulerProxy.setCallbackHandler( successHandler );
            schedulerProxy.setErrorHandler( failHandler );

            function updateGame( gameID, divID )
            {

                // grab these two values from two selection elements on the page
                var homeTeam = document.getElementById( "select1" );
                var visitorTeam = document.getElementById( "select2" );
                gameProxy.updateGameMini( gameID, homeTeam.value, visitorTeam.value );
            }
           
            function successHandler( result )
            {
                alert( result );
            }
           
            function failHandler( statusCode, statusMsg )
            {
                alert( "failure" );
                alert(statusCode+': '+statusMsg);
            }           
        </script>

And I have the aforementioned cfc with this.  In it, I want to update the games field with the two values.  So the meat of the function is:

    <cffunction name="updateGameMini" access="remote" returntype="query" output="false">
        <cfargument name="ID" type="string" requried="yes" hint="Game ID">
        <cfargument name="homeTeam" type="string" required="yes" hint="Home team unique ID">
        <cfargument name="visitorTeam" type="string" required="yes" hint="Visitor team unique ID">

          <cfquery name="myUpdateGame" datasource="#application.datasource#" username="#application.dbUser#" password="#application.dbPass#">
            UPDATE games
            SET home=<cfif arguments.homeTeam EQ "">NULL<Cfelse><cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.homeTeam#"></cfif>,
            visitor=<cfif arguments.visitorTeam EQ "">NULL<Cfelse><cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.visitorTeam#"></cfif>
            WHERE gameID=<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.ID#">
        </cfquery>

          <cfreturn myUpdateGame>

    </cffunction>

But I keep getting "500: Internal Server Error".  Why?  Is this possible to run an UPDATE database call through a cfajaxproxy cfc function?

[Dan_Bracuk]

Have you successfully tested this cfc by calling it without using ajax?  If not, do so.  If you get the same error, the problem will be with your cfc.

[salimzayat]

One of the tests I ran was to use a QUERY sql call instead of the listed UPDATE one, and it ran fine.  So I don't think the problem is with the cfc.  Truth be told, I lifted that code directly from another bit of code that has existed in the codebase for a while.  But that cfc function was a "public" one, not "remote", and I didn't want to change code that wasn't mine and risk breaking something else.  So I copied it over and just changed the access.

Does this mean there should not be any problem, hypothetically, with updating a database through such a mechanism?

[Dan_Bracuk]

I don't use ajax because I'm still on cf7 but I don't see why what you are attempting would be impossible.  Unfortunately the error message you are getting is not very helpful.

You have to find out what is causing the problem.   I suggest starting by copying the update query to a cfm file, putting hard values in place of the variables,  and running it.  If that works, move it to a local function and run it again, still with the hard values.  If that works, start sending it arguments, one at a time.  Eventually you will find the problem.

[BKBK]

The correct object is schedulerProxy, not gameProxy.

[salimzayat]

Found the problem.  You were right, there was a mistake in the cfc, but I wasn't able to see it until I remove it from AJAX.  Apparently, I copied the original code a little too closely, and was repeating function and query names.  Works well, thanks for the help.