August 3, 2009 at 9:25 pm
Comments posted to this topic are about the item Building an API
August 4, 2009 at 3:29 am
I would just like to say that this is probably the best reason I have heard of why I should use Stored Procedures altogether.
I know there are lots of arguments for SPs, security and keeping the data in the Database, but when looking at real-world scenarios, these are never that important.
When I build a small application or website that needs a database, although I use SPs, I almost never change the security for them, like to only allow particular users, etc. because a website for example, the only one accessing the data is the server (hopefully) anyway. And when you look at .NET LINQ to SQL and Entity Framework, these basically say, "Put your SQL in the code", even if you don't actually type any SQL at all.
By saying that providing Stored Procedures as an abstraction layer, to enable an API-like framework, this I think is the best explanation and reason to use them.
Thank you for a great post.
August 4, 2009 at 6:18 am
Are you sure you weren't listening in on our development meeting the other day? We talked about both using FogBugz and developing stored procedures to use as an API layer between our databases! Too ironic, I tell you!!
Obviously, I'm with you on the whole API approach. I've always felt that database development can be treated in an "object-oriented" way. To me, it's just good development practices.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
August 4, 2009 at 6:51 am
A good API is an excellent resource and often better than source code for customization.
However, the API has to really be properly thought out and well structured, not added as an afterghought. Some are incomplete, poorly documented, and buggy.
The best ones are actually used by the internal developers to do some of their own features.
...
-- FORTRAN manual for Xerox Computers --
August 4, 2009 at 7:02 am
Although you don't need to use stored procs to have an API - us developers, (yes I am one), also have utility classes that provide the same sort of abstraction; they're still part of the Data Access Layer but rather than sitting next to the data, they live on the server.
There's pros and cons to either. With the classes you can include enumerated types for those "option" arguments, abstract away the database connection information, and any parameters whose values come from fixed values can also be coded.
Regardless of the technology utilised, you still need a well written API and well documented.
I'm also, personally, an advocate for as many options and customisations as possible, because you never know when you might need them. Normally when I'm writing something I'll add extra choices for disabling functionality or additional flags for what's required. It's saved a lot of hassle later for only a small extra development time up front.
Paul
PS: Just tried to spell check my work. It managed to flag up words like "to", "that", "an", "you" and, (ironically?), "data" as being incorrect.
August 4, 2009 at 7:10 am
Paul (8/4/2009)
Although you don't need to use stored procs to have an API - us developers, (yes I am one), also have utility classes that provide the same sort of abstraction; they're still part of the Data Access Layer but rather than sitting next to the data, they live on the server.
Paul,
You're right when it comes to applications accessing the database APIs, however if another database needs that access, then a stored procedure is a much better way to go. In our environment, we're isolating one database from the others via the stored procedure APIs.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
August 4, 2009 at 8:19 am
Nothing to say on this one except, "I agree".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 4, 2009 at 8:41 am
GSquared is speechless? And so am I after that.
August 4, 2009 at 9:00 am
What you are advocating, Steve, is keeping code abstracted and divided into objects. This is the core to good software engineering. When you take delivered source code and modify/enhance it without keeping your changes separate from the original you are falling back into the old programming practices of the '60s and '70s, when code became more and more entangled until it became well and truly spaghetti code.
I have had to re-engineer some of that code, finding things such as subroutine stubs with nothing in them, but which are expected to return things by the calling code, and subroutines which use names which have since become keywords in the language. But of course, the EEs who meddled with the code (because, after all, how difficult could Fortran or C be?) had turned off the warnings which prevented their code from compiling. Then they had to hire people like me to come in and figure out why, one day, the code wouldn't compile and run any more.
Now, you can go too far the other way, too. I have seen some code where none of the routines did more than one or two things. That is fine for a few routines where you want to abstract something which is platform-dependent, but it makes the organization and debugging much more difficult if the number of modules increases into the thousands.
August 4, 2009 at 9:04 am
Steve Jones - Editor (8/4/2009)
GSquared is speechless? And so am I after that.
More like "writeless". 😀
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 4, 2009 at 9:13 am
in a previous life, I built object libraries in C++. The greatest (and most rewarding part) was conceptualizing the object trees, the relationship of objects, inheritance and methods into a comprehensible model for the user.
Challenging, but fun.
...
-- FORTRAN manual for Xerox Computers --
August 4, 2009 at 9:55 am
Excellent (again) Steve!
I agree, although I include Views along with Store Procedures in the definition of a database API. I suppose one could argue for Functions as well, although I don't use functions that often.
I'm interested in what others think...
:{> Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
August 4, 2009 at 10:04 am
Aaron: My point about the class alternative was that it's just that; an alternative. You could use either, actually I normally use both, (database objects and classes), as they both serve a purpose for my projects.
Andy: In fact, I'd extend your list to *any* object in the database. There's even arguments that a db table is an API, as you're not accessing the information held on the harddisk directly, you're accessing it via SQL server's own API.
Being a developer, I'm always using what could be termed an "API". Everything from the window's own APIs, all the way to the programming language I'm writing for - OK, maybe that's stretching the definition to almost breaking point, but if you think of an API as an interface to something else, then the term still applies.
How philosophical shall we go with this? Any arguments for our lives being APIs...
Paul
PS: Could someone, please, explain why the spell check option refuses to recognise almost any word I've typed in this reply. Pretty please.
August 4, 2009 at 10:16 am
Paul (8/4/2009)
Aaron: My point about the class alternative was that it's just that; an alternative. You could use either, actually I normally use both, (database objects and classes), as they both serve a purpose for my projects.
Point well taken!
PS: Could someone, please, explain why the spell check option refuses to recognise almost any word I've typed in this reply. Pretty please.
Perhaps your language setting is incorrect?
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
August 4, 2009 at 10:32 am
Agree with jpowers. Additionally, if the API is robust and provides function overloading the problem of drowning in discrete modules can be ameliorated.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply