May 10 2009

Database Abstraction - It's Stored Procedures

Posted by Mike Brunt at 6:31 AM .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.
qiudong

qiudong wrote on 03/16/11 10:30 PM

before http://feiyue.eu.com the dinner, the host should http://feiyue.eu.com/feiyue-low-cut-cancas write the full name of each http://feiyue.eu.com/feiyue-hight-cut-leather guest in fancy print on little cards and place them at
qjkider

qjkider wrote on 11/24/11 4:14 PM

Daily meticulously shaping a hair, will make people look extra spirit [url=http://www.bestlisseurghdfr.info/][b]ghd lisseur[/b][/url] Today we introduce some friends of female beauty does not hurt the hair shaping straight hair, [url=http://www.bestlisseurghdfr.info/][b]styler ghd [/b][/url]the product uses a high temperature ceramic plates, [url=http://www.bestlisseurghdfr.info/][b]lisser ghd[/b][/url] hair can minimize the damage will be. Straight hair looks elegant design [url=http://www.bestlisseurghdfr.info/][b]ghd hair straighteners[/b][/url] Body with a high temperature ceramic plates smooth and delicate texture to prevent their hair, [url=http://www.bestlisseurghdfr.info/][b]ghd france[/b][/url] to protect the scalp healthy.
gaabg

gaabg wrote on 10/22/13 9:10 AM

The blog is good.i recommend a great deal. These proceed through intensive study as well as growth to improve overall performance http://www.ronaldocleats.com characteristics which are good to gamers. Adidas, Nike, The puma company along with Umbro are the hottest and a lot donned cristiano ronaldo mercurial boot makes. The best measurement on your foot provides you with the best outstanding overall performance within a game. For most ages now, Chaussure de foot Nike Hypervenom may be famous for producing many different sneaker items that offers provide many players worldwide in several sports http://www.lionelmessif50.com as well as games. In almost any sport, Nike firm constantly make an effort to generate a particular footwear product which can be fitting to a certain sports activity variety. Thus, because presented within the Lionel Messi ads as well as used by way of a web host http://www.chaussurefootronaldo.com involving renowned people just like Gareth Bale, Jesse Silva, Luis Suarez as well as Pavel Pogrebnyak.This is exactly what Nike has developed for Ronaldo brand new footwear. Nonetheless, seems to be and all sorts of set aside. These shoes are produced from excellent unprocessed trash and are engineering based.A player must consider special care when picking her or his boots since it is critical Chaussure de foot Nike Hypervenom that it suits them personal, that means body along with really feel from the start. These boots need to suit the players?ft . properly, using the shape of the particular base into mind.
chaussure foot mercurial

chaussure foot mercurial wrote on 10/28/13 4:17 AM

great,make people truly feel harmony and normally want checking your site.
crampon nike hypervenom

crampon nike hypervenom wrote on 10/28/13 4:21 AM

So good,It's my best novice as i visit here.
xuehua

xuehua wrote on 05/20/14 11:31 AM

They may be set up in boot styles to provide better hold http://www.chaussurefootballpaschere.com
throughout materials the place that the players enjoy. It's important http://www.nouveaufootball.com with regard to basketball shoes to have this specific component to be able to avoid these people through falling or perhaps getting back in mishaps.
http://www.radiologie-thiais.fr

http://www.radiologie-thiais.fr wrote on 11/05/14 7:38 PM

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
click

click wrote on 12/26/14 10:23 PM

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
clothing manufacturer

clothing manufacturer wrote on 07/04/15 4:44 AM

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
http://www.creezvotresoiree.fr

http://www.creezvotresoiree.fr wrote on 08/24/15 3:43 AM

There was a problem: Sorry, but your comment appears to be sp