Things to consider with stored procs: Plus: More logic is in the DB than in CF Con: More logic is in the DB than in CF
If you want to switch Data providers, you have a major task- CF and the DB are "hard-linked" sorta, if that makes sense. And I think that there is a beliefe that stored procs can't be poorly written, which is incorrect. But they do make diagnosing from within the database easier, which is where Teddy seems to get the most reward. The all around anser of "you use what's best for the task" is just too easy though... but it's what you have to do, so... eh... All I have to say is that the questions that the Pragmatic Programmer asks, are difficult ones, and involve being able to swap parts out indescriminately, which is REALLY hard to code for, as easy as the concept seems. 'Cuz it takes it out of the domain of what the language is you're using to develope, even. E.g.: switching from CF to PHP, or ASP, or Java or C##... Man, how could you do that? Eh. Dang. Meeting. Well, I'll comment on hsqldb later, but I think it's pretty interesting, and I wonder why no CFers seem to be utilizing it as well. Derby? Anyone using Derby? :den On 8/28/06, Teddy Payne <[EMAIL PROTECTED]> wrote: > > Well, there has been a strong aversion to my original thread and some > agreement with various aspects. > > Let me add that I thank everyone for their comments. My usage of stored > procedures is my personal style of coding. I consider stored procedures a > good way to abstract my data code and enforce code reuseability. I find > it > easier to share a stored procedure with another developer than a > ColdFusion > template. The other developer just needs to do a <cfstoredproc> with the > appropriate parameters and does not have to get lost in understanding the > query originally set up. <cfprocparam> is just as effect as > <cfqueryparam> > and offers an excellent way to have multiple returned results with > <cfprocresult>, where I can assign resultset numbers to names of a query. > > Now, I am aware that you could have three separate queries to achieve the > same thing, but from within CF, I do not have a way to debug the SQL as > quickly than I would have a tool like Query Analyzer. Now, I also know > that > you can copy and paste from your CF templates in Query analyzer, test the > query and paste the code back to my template. I do not consider myself a > purist, but I would choose to have all of SQL being managed by my database > abstractly and only have my CF code display results. > > Another consideration, I have stored procedures that can be executed from > CF > and now can be shared with more advanced database operations. I use SQl > Server a great deal and with the SQL execute feature of DTS, I can have > the > same stored procedures that I use for my CF being used by the RDBMS. For > me, > this is logical to have the queries available for my database and not just > for the CF. CF doesn't have a way to share SQL code easily with databases > that I have seen. > > I enjoy topics about ORM models as well. I am subscribed to the Reactor > for > ColdFusion mailing list as I like the approach of abstraction my queries > even more into an object definition. My custom gateway methods use stored > procedures to keep my personal style consistent. > > If you would like to share SQL concepts with me that I can apply with > stored > procedures, please let me know. I enjoy finding ways to structure my > queries more effectively. > > This is a good thread. I enjoy the contrarian comments. I see most of > the > comments as people justifying their own methods that work for them. I > suggest keep doing what works for you as I am sure that you are getting > the > results that you are looking for. > > I find that this is a style thread and that no one is arguing that stored > procedures cannot achieve great results. > > Teddy > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251299 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
