[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1763
  • Last Modified:

$.getJSON calling CFC function which calls mySQL stored procedure using cfstoredproc

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)?
0
ShanghaiD
Asked:
ShanghaiD
  • 2
1 Solution
 
_agx_Commented:
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)?

Technically speaking you could put the cfstoredproc on plain .cfm page and call that page from getJSON, instead of your cfc. But there's no benefit. It may even cause problems because .cfm scripts don't handle whitespace and json automatically like cffunctions do.


...it seems to me that I then need to use CFARGUMENT tags inside the function to get the arguments passed from the $.getJSON

Yep


..and also then need to use CFPROCPARAM tags for each passed in argument ..  

Correct


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

Yep, it is required.  If you think about it, that's what you'd do with a cfquery too, except using cfqueryparam instead of cfprocparam. I see your point, but it's not double handling because they serve different purposes.

cfargument validates that the function received the number and type of variables it needs to work. cfprocparam also performs validation, but it's much more detailed . cfprocparam's primary purpose is to parse and prepare the values properly for the database driver. It converts value strings into lower level data types like integer, date, double, etc... CF then constructs a sql statement & inserts the parameter values via bind variables. Finally sending it off to the db for execution.  So it's very different than what cfargument does.
0
 
ShanghaiDAuthor Commented:
Thanks once again for sharing your knowledge.  Your explanation is very clear and makes perfect sense to me. Now I know I am going down the right track!
0
 
_agx_Commented:
You're welcome. When venturing into unfamiliar territory I always appreciate a good sanity check too :) So I can back up if I'm going down the wrong path.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now