Shane, Sounds like you have had bad experiences with DBA's. I am sorry about that, but, doing stored procedures is the better way to go. Yes, it is sometimes faster to circumvent going through the DBA. if there is one, for things. But, they are a specialist like you are for CF. Generally, they can write better SQL that you or another CF programmer. It is their job.
I am experimenting with LINQ now. I am rewriting a large application that was developed in Access with a SQL Server back end. I am trying 3 different data access methods to determine which is the better method. LINQ for SQL LINQ for Objects, and Codesmith tools with netTiers. The application will be rewritten into WPF and done as software as a service to the clients that use it. Since WPF can go either browser or native Windows, it seems the best choice for where I want to go with it. For those that have to wear all of the hats, I understand. The software I talked about above was something that I have done alone. And I do know all about having to wear every hat of the software development cycle. Data access is not the same as business logic. We have had that argument here also. We are trying very hard to keep all of the business logic out of the database and the stored procedures. It absolutely belongs in the layer above the db. While dynamic SQL is just as fast using parameterized queries, neither one is as fast as compiled stored procedures. As far as migration, if all access is through stored procedures, migrating between databases and middle layers are both easier. Version control can be done through Visual Source Safe or Subversion. We have tried and used both with out issues. So your version control argument is null. If you know what you are doing and follow a system, keeping SPs synchronized is not an issue. Triggers are adding business logic into the data layer and not something that should be done unless absolutely necessary. Robert At 02:38 AM 12/16/2008, you wrote: >Date: Mon, Dec 15 2008 9:13 am >From: Shane Heasley > > >I think Aaron makes a good point. I have always had to wear all the >hats - project manager, information architect, business analyst, >graphic artist etc - database developer and coder. I think that is >more often than not the case. So if I'm building the db and doing the >coding the logical and physical are the same to me. Or, to state >another way, I don't see an employee object, I see an employee object >related to an address object, also related to a department object etc. > >I may or may not build a component that gloms all of those into one >Employee object. I usually do not. > >But I can see the advantage of Robert's approach. Robert have you >considered moving from SPs to LINQ and feeding ColdFusion .net objects >(assuming you're on CF8)? I don't know how well it would work, or >even if it is possible since I haven't done it - just curious to find >out if it can be done and what your thoughts were. > >Cheers, > >Shane > > > >== 6 of 6 == >Date: Mon, Dec 15 2008 10:48 am >From: Shane Heasley > > >Additional thoughts regarding using Stored Procedures: > >SQL Injection: >Stored procedures can help stop injection attacks, but you can also do >it with parameterized queries. >I would rate this as a wash. > >Development Speed: >Stored procedures slow down development in large environments where >there is a separate DBA. I recently worked a contract at MD Anderson >and burned a good week trying to get the DBA to, correctly, modify >some SPs. Not to mention, if the same person is the DBA and the >programmer they now have to know two different syntaxes. > >MVC: >SPs mangle the three tier architecture designed for apps. Instead of >having a structure which separates logic from storage, you have >storage and logic on the same tier. This will almost certainly cause >problems at a future date - unless perhaps the DBA is very good and >not overworked - and doesn't leave the company. > >Business logic in a SP does not scale: >Business logic should be in code and not in the storage center. When >you have multiple database servers, it is more difficult to keep the >triggers and SP's synced. Also, there is no version control for SPs >and database screw ups can be very dangerous. > >Processing Speed: >I was wrong about the speed issue also - it now looks like that even >dynamic SQL is just as fast using parameterized queries. > >Migration: >I was recently given the argument that using SPs made sense if there >were plans to migrate to different middleware (in this case from CF to >asp.net). That made sense for a minute but after I thought about the >statement it seemed to me that it was just an easier method of getting >yourself into a bad long-term situation. Robert Stewart ProjecTools.com 713-371-9840 X1305 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the "Houston ColdFusion Users' Group" discussion list. To unsubscribe, send email to [email protected] For more options, visit http://groups.google.com/group/houcfug?hl=en -~----------~----~----~----~------~----~------~--~---
