$.getJSON calling CFC function which calls mySQL stored procedure using cfstoredproc
Posted on 2012-09-12
I've previously used the CFQUERY tag in my CFC function to call data from mySQL, passing variables to the query from $.getJSON using CFARGUMENT tags inside the CFFUNCTION.
I'm now changing to put my database query code into a mySQL stored procedure which I will call using CFSTOREDPROC instead of using CFQUERY. I still want to return JSON to manipulate it using jQuery, so I "think" that I still need to go through a CFFUNCTION tag to invoke the stored procedure and return the JSON to the webpage. Is this correct, or can I bypass the CFC function and use CFSTOREDPROC directly from the webpage (still getting JSON back for jQuery to consume)?
If I still need to go through the CFC function, it seems to me that I then need to use CFARGUMENT tags inside the function to get the arguments passed from the $.getJSON and also then need to use CFPROCPARAM tags for each passed in argument when I use the CFSTOREDPROC tag to call the mySQL stored procedure. This seems to me to be "double handling" -- ie passing each CFARGUMENT value on to each CFPROCPARAM. Am I correct in thinking that this is the way things must be done (or is there a more efficient/better way)?