A couple of things to note about using cfstoredproc with MX: First, passing parameters in the order in which they are specified is required in JDBC. In previous versions of CF that used ODBC, you were supposed to be able to use named notation - althought from what I understand, this never really worked on most platforms (such as SQL Server).
There are several advantages to using <cfstoredproc> over usin the CALL or EXEX syntax in a CFQUERY. The first, which was already mentioned, is multiple record sets. Another reason is that you can have your SP send back a return code, which can only be accessed if called via cfstoredproc. A third reason is the use of out and in/out parameters. These can only be done with cfstoredproc. So, if you have simple stored procs that don't return multiple record sets, don't make use of return codes, and don't have out or in/out parameters, you may want to look at calling them using the cfquery tag. One ADVANTAGE to using cfquery over cfstoredproc is that you can cache the query results with the cfquery tag, but not the cfstoredproc tag. One final thing. If you do use the cfquery method, you should use cfqueryparam (it wasn't a kludge just to solve long column data problems in older versions of CF) for each parameter you pass. This allows you bind the data to the specific SQL type expected by the DB (this can keep someone from passing an arbitrary SQL statemtnt or other non-expected data as a parameter). It also allows you to set max lengths for data as well as use NULLs. -Rob >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Date: Wed, 04 Sep 2002 10:27:36 -0400 From: Kreig Zimmerman <[EMAIL PROTECTED]> Subject: Re: <cfstoredproc> and CF MX Message-ID: <[EMAIL PROTECTED]> Well, it works for me. I'd just check your code out. That was production-level code I used as an example, so I'm 110% certain it works. As for your comments... yeah it's stupid. But without knowing exactly what CFSTOREDPROC does, what's to say? At any rate, I avoid that issue with abstraction--CFMX style. The StoredProc is wrapped in a CFFUNCTION. Any calls from my own code, it's a simple CFSCRIPT, e.g.: <cfscript> thoseQueries.GetStuff(id=12, bid=226) </cfscript> What's the point of complaining about most CF limitations... anyone who's worked on any major software knows all the compromises that must go into any project. Maybe the guy who originally coded that fucntionality left half-way through his job for a dotcom of the time, and they never finished implementing it. Maybe he got lost halfway through building it. Maybe the piece was EOL'd. As it is, I'm convinced CFQUERYPARAM was a kludge of its own, which "borrowed" CFPROCPARAM in order to solve the long column data insert issues in Oracle pre-4.5. The fully implemented version of CFQUERYPARAM since 5.0 has thus blurred the boundaries between CFQUERY and CFSTOREDPROC. Let's not forget that CFQUERY is probably doing a lot more than it was initially intended to do in CF's "design". ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
