May 10 2009

Database Abstraction - It's Stored Procedures

Posted by Mike Brunt at 6:31 AM
5 comments - Categories: .NET | DataBase | ColdFusion | JRun-J2EE

There are two situations that get discussed reasonably often relating to databases and applications.  The first is the possibility that a database engine (the RDBMs) might be changed at some point from let's say, SQL Server to Oracle.  In my career in the database-web application, which spans 16 years, I have literally never seen that happen with any database of any palpable size.  I have seen several occasions where Access was up sized to SQL Server but I see that as different.  The second situation, which I have seen several times, is where a new application is created to utilize an RDBMs that already existed or an existing application is extended to use an existing RDBMs.  This second situation, which is discussed far less often, as it pertains to database abstraction is actually a far more real and occurring situation. In either of these two situations, efficient abstraction of the RDBMs from the application code makes the operation easier to accomplish.

In my experience, there is one method of abstraction that surpasses all others in terms of continuity of standards and usability across all application code development ASP, ASPX, CFML, JSP .NET PHP etc etc.  That abstraction layer is Stored Procedures not some sort of code-level paradigm.  My advise is used Stored Procedures wherever possible, avoid in-line SQL and also any paradigm that is application code dependent when interacting with RDBM's.  Ongoing, this will make your development processes much smoother and predictable.

This blog piece of mine was inspired by one created by the author of the SQL Server 2005 Bible - Paul Nielsen

Just a reminder that I will be in Boston/Cambridge on May 11 and 12 at The Charles Hotel, in the bar from 7:00PM on if you want to come down to chat about all things Enterprise and HA.

 

Comments

tony petruzzi

tony petruzzi wrote on 05/10/09 8:46 AM

I'm going to have to completely disagree with you on this one as there are some reasons why stored procedures should never be used for database abstraction:

1) you need to be granted the proper permission in the database in order to create or modify stored procedures. I know in most cases this is a problem, but in some case it can be.

2) depending on your RDBM the stored procedure language can be limiting to use in terms of proper error handling (sql server 2000 doesn't have a try, catch) and functionality.

3) debugging where exactly the stored procedure is failing is very difficult without the proper tools. it's much easier doing this on the application end.

4) the day that your application needs to move from sql server to oracle or from oracle to postgresql you're going to be in a world of hurt. while some of the syntax used in your stored procedures could be the same on the migrating engine as your current one, chances you're going to be porting about 85% of them.

with the advert of ORMs you get the flexibility of have all your business logic at the application level while not having to do the mundane task of writing crud and sql statement.

i still use stored procedures when i need to import and export data from the database in a nightly job and don't need to have coldfusion involved in the mix. but as application development, everything is contained on the application's end
Mike Brunt

Mike Brunt wrote on 05/10/09 11:20 AM

@Tony thanks for taking the time to reply so comprehensively, I could be lazy and simply say, refer to Paul Nielsen's article. He looks at things, in his piece, largely from the standpoint of the DBA. In my case, I have been a CF developer since 1996 so I look at it from the application perspective.

1/ Your first point is spot on, we absolutely need to granted permission to touch anything to do with the database and in the era of PCI compliance and Sarbanes-Oxley it cannot and will not be any other way.

2/ I have been working extensively with SQL Server 2005 and 2008 recently and the error handling capabilities in each is more than adequate, in my experience; including the capability to send emails. Obviously this will not include data manipulation error reporting on the CF side but there is no reason not also to use try-catch in CF also.

3/ Working with the DBA, codes can be returned to CF from the Stored Procedure indicating what went wrong, I have not found any difficulty with that.

4/ In actual fact, in-line SQL is as bad if not worse and ORM's are no panacea. At the end of all of this we are enabled and or limited by the vagaries of SQL. Whatever mechanism is used we still have to deal with SQL differences across different RDBM's.

Many DBA's will not allow access to system tables which makes the use of an ORM very difficult.
clément

clément wrote on 05/11/09 8:52 AM

As a dba, I completely agree with Mike. Migration works could be far more significant when going from 2000 to 2005 and inline SQL is everywhere.
Cf inline SQL is not only dangerous, it is not optimal for the db engine to optimize the execution plan.
Finally I do not think "set-based" programming should be handled by the application. I have the same opinion on the other side, CLR programming should be mostly handled by application.

From iPhone
Mike Brunt

Mike Brunt wrote on 05/11/09 2:26 PM

@Clement thanks for the response and the perspective of a DBA. In my day to day working with different companies I see a definite trend toward Stored Procedures and away from in-line SQL. As I mentioned in my comments above PCI compliance along with Sarbanes Oxley challenges are inevitably going to make data more secure and less directly accessible, in my opinion.
Clement

Clement wrote on 05/12/09 2:38 AM

As you mentionned, Stored procedure is also an abstraction.
Views can be queried directly and also get specific permissions.
However, you never know if in the future you might need specific development due to the evolution of the business or industry your company is.
In that matter, stored procedures (even for just selects) is inevitable to be scalable and to handle migration and PCI/SOX compliancy.
Data service layer at the application/web service/ WCF level can be done too but I still believe that encapsulating SQL code makes data management much easier.

Write your comment



(it will not be displayed)



Leave this field empty:

Categories

Monthly Archives

Search Archives

Favorite Links