Stored Procedures
-
I was wondering what the best practises are for stored procedures. Basically, I'm still struggling with a kind of code explosion that occurs when writing code that's related to the database. In the end, what I need to do is move data into and out of the database while sometimes performing transformations and doing error checking along the way. I've found over and over again that it takes a lot of code to do this. I'm curious if stored procedures are a good way to abstract out of code such things as queries, inserts, updates, etc., and move them into the database itself. In a way, this relates to a thread I created here a few months ago about database code. I'm trying to get a handle on how to deal with complexity. I'm finding that database programming is largely procedureal in nature, so I'm not finding my experience with Object Oriented programming and patterns to be of much use.
-
I was wondering what the best practises are for stored procedures. Basically, I'm still struggling with a kind of code explosion that occurs when writing code that's related to the database. In the end, what I need to do is move data into and out of the database while sometimes performing transformations and doing error checking along the way. I've found over and over again that it takes a lot of code to do this. I'm curious if stored procedures are a good way to abstract out of code such things as queries, inserts, updates, etc., and move them into the database itself. In a way, this relates to a thread I created here a few months ago about database code. I'm trying to get a handle on how to deal with complexity. I'm finding that database programming is largely procedureal in nature, so I'm not finding my experience with Object Oriented programming and patterns to be of much use.
I believe that the database and the stored procedures, or functions should only be used to guard data constraints. So they can be useful when you insert data into one row that always needs to be verified against another table, when a foreign key alone does not describe the full nature of the relation. For example one row depends on a range of rows existings in a foreign key. Another good reason to use them is to guarentee sequential execution of data insertion or deletion across multiple instances of the application. From what I understand from your question you might be better of abstracting inside the code first by generating generic classes responsible specific parst of the applictions communicating with the database.
-
I was wondering what the best practises are for stored procedures. Basically, I'm still struggling with a kind of code explosion that occurs when writing code that's related to the database. In the end, what I need to do is move data into and out of the database while sometimes performing transformations and doing error checking along the way. I've found over and over again that it takes a lot of code to do this. I'm curious if stored procedures are a good way to abstract out of code such things as queries, inserts, updates, etc., and move them into the database itself. In a way, this relates to a thread I created here a few months ago about database code. I'm trying to get a handle on how to deal with complexity. I'm finding that database programming is largely procedureal in nature, so I'm not finding my experience with Object Oriented programming and patterns to be of much use.
Leslie Sanford wrote:
I was wondering what the best practises are for stored procedures. Basically, I'm still struggling with a kind of code explosion that occurs when writing code that's related to the database. In the end, what I need to do is move data into and out of the database while sometimes performing transformations and doing error checking along the way. I've found over and over again that it takes a lot of code to do this.
Learn to use existing frameworks. Dynamic ones or generated static ones. Database code is often something that is very amenable to those types of implementations. Myself I have been write database layer code generators for years. Additionally other common functionality is easily assigned into the same layer generation types. For example one can generate field generation code that validates such things a existence, length and even pattern for use in things such as GUI code. Some of this is something that you are more likely to gain an advantage by learning to write your own code generators.
Leslie Sanford wrote:
I'm curious if stored procedures are a good way to abstract out of code such things as queries, inserts, updates, etc., and move them into the database itself.
Yes. For the same reason that one create any layer code - to abstract the user from the implementation.
Leslie Sanford wrote:
I'm finding that database programming is largely procedureal in nature, so I'm not finding my experience with Object Oriented programming and patterns to be of much use.
Relational databases have a very long and successful history. For a reason. So avoid the inclination to even attempt to make it OO. There are in fact ways to do that and it is unlikely that it will lead to positive versus negative results. You start with a data model. Then you use stored procs and the database layer to translate the relational model into an OO model. Just as you do with any other translational layer. As a final note don't get to wrapped up in where the "best" place is for business logic. As an example don't avoid uniqueness keys in the database simply because that is expressed as a business rule. The database can implement that just as well, and probably better for standard business cases, versus attempting it in the database layer.