 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / General DB Topics / General DB Topics / January 2005When to use procedures?
Does anyone knows when should a procedure be used rather than code the statement into the application itself? Like for instance an application needs to know whether a user exists so the code will be SELECT * FROM table WHERE user.id = id; And if i were to create a procedure it will be CREATE PROCEDURE is_user_exists . > Does anyone knows when should a procedure be used rather than code the > statement into the application itself? > > Like for instance an application needs to know whether a user exists so > the code will be SELECT * FROM table WHERE user.id = id; And if i were > to create a procedure it will be CREATE PROCEDURE is_user_exists . When it enhances scalability, performance, and data integrity. Most of the time code belongs in the back end, written by people that know how to write code for the back end. Because it is nearly impossible, in the real world, to debug and tune code written elsewhere. For example I think the SQL statement you have above is abyssmal if the point is just to determine whether a user exists in the system. You are likely returning hundreds of bytes when a single byte would suffice.  SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond) >> Does anyone knows when should a procedure be used rather than code the >> statement into the application itself? [quoted text clipped - 11 lines] > point is just to determine whether a user exists in the system. You are > likely returning hundreds of bytes when a single byte would suffice. Correct, for example a scalar query would suffice nicely. How many different scalar queries do you need in a 500 table system, Daniel? A lot, I can tell you. Would you write these all out in procs? Or would you use something like: bool doesExist = ( adapter.GetScalar( EntityFieldFactory.Create(UserFieldIndex.UserId), null, AggregateFunction.None, PredicateFactory.CompareValue(UserFieldIndex.UserId, ComparisonOperator.Equal, _userID)) != System.DBNull.Value); This simply generates a scalar query with parameters in the DAL's sql engine. The fun part is, I can do that on every field in my 500 table database. And use filters, group by clauses, having clauses, expressions, aggregates and what have you. Too slow? Show me. I think you'll have a hard time trying to proof this is really slower. That's ok, speed isn't everything. I'm pretty sure you're not writing your programs in Assembler anymore either. Oh, and data-integrity and scalability? Do your procs have fail-over support? Can you re-use your procs on oracle and sqlserver? I can re-use my C# code which targets the O/R mapper on sqlserver if I wrote it first to target oracle. You see, nothing is as black/white as you think it does. Procs have severe disadvantages and you make these look like they're non-existend. I think that's the wrong way of approaching the subject. Frans. > How many different scalar queries do you need in a 500 table system, > Daniel? A lot, I can tell you. The number of procedures is not interesting, I think. If something has to be done, it has to be specified, somehow. And if it gets too much work, it can allways be automated one way or another. > Or would you use something like: > [quoted text clipped - 5 lines] > ComparisonOperator.Equal, _userID)) > != System.DBNull.Value); To someone unfamiliar with your programming language and framework, that code is as clear as Volapuk. Putting things in the middleware doesn't automatically mean that they become easier to maintain. > Oh, and data-integrity and scalability? Do your procs have fail-over > support? Can you re-use your procs on oracle and sqlserver? I can re-use > my C# code which targets the O/R mapper on sqlserver if I wrote it first > to target oracle. The SQL standard is - unfortunately - not respected enough to make stored procedures portable. So there you have a (very important) point. But: First: Your C# code is - currently - rather Windows-bound; so there is another portability problem, just elsewhere in the "solution-stack". Second: The lifetime of frameworks and hip languages seem to be lower than data(bases). So if constraint handling and access control is handled in the database where possible, the overall system becomes less vulnerable to changes in programming framework policies. SignatureGreetings from Troels Arvin, Copenhagen, Denmark >>How many different scalar queries do you need in a 500 table system, >>Daniel? A lot, I can tell you. > > The number of procedures is not interesting, I think. If something has to > be done, it has to be specified, somehow. And if it gets too much work, it > can allways be automated one way or another. No, that's the problem, it can't. The number of procedures IS important, as with every proc, you decrease the maintainability of the system. True, if you keep a good record of what does what and how, and why, it can be 'maintainable' over the years, but it will be hard to keep up. Especially with the larger databases where you have a single API of procs and multiple applications targeting the procs, it's impossible to alter a proc because some filter has to be adjusted for application X, because it will change the API interface (which is not recommended) and will 'probably' break app Y and Z or perhaps parts of X too. So you add a new one with the new parameter. Over the years, this gets horrible, as which proc is used by which app and which proc isn't used at all. >>Or would you use something like: >> [quoted text clipped - 9 lines] > code is as clear as Volapuk. Putting things in the middleware doesn't > automatically mean that they become easier to maintain. It was an illustration of the options available to developers. Often teams face problems where they have to alter existing procs to make a change but this is hard to do because it requires changes in a lot of layers, apps and might break something else, like another app. If you formulate the query at the spot where you need it, it doesn't matter if some other part of the app needs the same filter but with an additional predicate. That's the easier maintenance you have here. It also becomes the responsibility of teh developer to make his code work, which is the only place where that responsibility has to be. >> Oh, and data-integrity and scalability? Do your procs have fail-over >>support? Can you re-use your procs on oracle and sqlserver? I can re-use [quoted text clipped - 6 lines] > First: Your C# code is - currently - rather Windows-bound; so there is > another portability problem, just elsewhere in the "solution-stack". I can target whatever database I like from my windows box, be it DB2 on AS/400 to access locally to firebird embedded. But I hear you, the portability of C# code can be a problem if your app has to run on apache/linux. The thing is: what I said also goes for Java and for example JBoss + Hibernate. Now, that's a portable solution, so the point is the same. > Second: The lifetime of frameworks and hip languages seem to be lower than > data(bases). So if constraint handling and access control is handled in > the database where possible, the overall system becomes less vulnerable > to changes in programming framework policies. But code is not part of the relational model. Data inside a database will be kept 'consistent' because of constraints, as constraints ARE part of hte relational model. An application in C# or ruby or python or what's hip today can be old tomorrow, of course that's true, but see the application as the USER of the data, the program which makes information from the data inside the database. In fact, one of the core powers of the relational model is that you can create your own entities (Chen) using queries ran on the implementation of the relational model (the database), which can mean new things. Code therefore shouldn't be connected WITH data, it's USING data, it interprets data. HOW that's done, is not that important. Constraint handling is thus part of the database, as it is part of the relational model. Access control is not, it's part of the code, as one table can be accessable for app A and forbidden territory for app B. The table will not change because of this, just the code accessing it. This means that the code accessing it is part of the application calling that access code. You can decide to place that inside the database, but that's not necessarily the right spot. I've tried to argument that. This debate is rather old really, as O/R mappers on Java have had to deal with this for years now. I have to stress that I'm not 100% against procs. Some situations require procs, as I described in another posting, for example long running data-mining routines, which consume a lot of data. It's them simply more efficient to do that inside the database, as then the data doesn't have to be transported outside the RDBMS just to get processed. This debate however often ends in a "Does Not!" "Does too!" style mud-fight. Which can be entertaining but it also is time consuming. So I'll always say: if you want procs, go ahead and use procs. I'm not the one who will be suffering the maintenance nightmare nor will I be the one losing a contract because the price is too high because I have to spend too much time developing procs. FB > True, if you keep a good record of what does what and how, and > why, it can be 'maintainable' over the years, but it will be hard to > keep up. So can code. I must admit, though, that tools for tracking software (such as Subversion) are very nice, and I don't know of any good software configuration management system for databases. That could just be due to my ignorance, though. But then again: I believe that declarative "code" like constraint specifications (and GRANTs) are much easier to grasp than loads of imperative code and "framework of the week". So the lack of good SCM for database schemas etc is not as serious as it might seem at first. > Especially with the larger databases where you have a single API of > procs and multiple applications targeting the procs, it's impossible to > alter a proc because some filter has to be adjusted for application X, > because it will change the API interface (which is not recommended) and > will 'probably' break app Y and Z or perhaps parts of X too. So you add > a new one with the new parameter. Exactly the same problem for APIs in other parts of the "solution stack". Except that the APIs defined in the database can be general to more than one middleware/frontend framework. > It also becomes the responsibility of teh developer to make his code > work, which is the only place where that responsibility has to be. You talk of "the developer". First, I'm sure you agree that it's often more relevant to talk about developer_s_, in plural. Next, developers are not the only ones responsible for making a _solution_ work: Solutions often have to interact with other solutions, made by completely unrelated teams. >> First: Your C# code is - currently - rather Windows-bound; so there is >> another portability problem, just elsewhere in the "solution-stack". [...] > But I hear you, the portability of C# code can be a problem if your app > has to run on apache/linux. The thing is: what I said also goes for Java > and for example JBoss + Hibernate. Now, that's a portable solution, so > the point is the same. Just because a problem also exists for Java doesn't make the problem less important. >> Second: The lifetime of frameworks and hip languages seem to be lower >> than data(bases). So if constraint handling and access control is [quoted text clipped - 4 lines] > will be kept 'consistent' because of constraints, as constraints ARE > part of hte relational model. We might be talking in different directions. I'm advocating putting logic in the DBMS _where possible_. If it's impossible to validate some constraint in the DBMS, it has to be validated elsewhere, of course. (And: I consider permission handling as a kind of constraint, so the same rules apply there, in my belief). (At this point, I wonder what we actually disagree about.) > Constraint > handling is thus part of the database, as it is part of the relational > model. Access control is not, it's part of the code, as one table can be > accessable for app A and forbidden territory for app B. That's simply not true. Example: The other day, I set up a mail system from certain specifications. The SMTP server needed information about a subset of the information in the DBMS, so I only granted certain rights to that. The autoresponse system needed access to other parts, so it only got selected rights. The superuser administration frontend needed other rights, and so did the normaluser frontend. Etc. I used no stored procedures (though I may in the future, if I'm to improve the system in certain ways), but certainly, I used the permission system, considering it an important part of the general solution. > I'll always say: if you want procs, go ahead and use procs. I'm not the > one who will be suffering the maintenance nightmare nor will I be the > one losing a contract because the price is too high because I have to > spend too much time developing procs. I don't know your work situation. But when I've taken part in decision making processes, the decision making group have never seen "the application" in isolation. SignatureGreetings from Troels Arvin, Copenhagen, Denmark >> Especially with the larger databases where you have a single API of >>procs and multiple applications targeting the procs, it's impossible to [quoted text clipped - 6 lines] > Except that the APIs defined in the database can be general to more than > one middleware/frontend framework. true, an interface is always immutable and that's a problem everywhere an interface is used. The funny thing is though that in an OO language I can use several interfaces on the same object, refactor behind the scenes and call overloads from methods which used to do the work in older versions. In proc-land this is hard to do: there is one interface. Mind you: most of the problems I bring to the table are for CRUD procs, I already made a special case about long running procs which have to consume a lot of data. >> It also becomes the responsibility of teh developer to make his code >>work, which is the only place where that responsibility has to be. [quoted text clipped - 4 lines] > often have to interact with other solutions, made by completely unrelated > teams. True, but if you place the logic to get for example a given set of customers with a given filtered set of orders inside a proc, that proc is part of the DB API and usable for all applications, or at least there to see for all applications targeting the db. If you place that logic in the BL tier, it isn't. In that case, the development team of app A decides what is filtered, where and how. >>>First: Your C# code is - currently - rather Windows-bound; so there is >>>another portability problem, just elsewhere in the "solution-stack". [quoted text clipped - 3 lines] >>and for example JBoss + Hibernate. Now, that's a portable solution, so >>the point is the same. > Just because a problem also exists for Java doesn't make the problem less > important. huh? You said that my C# example isn't taht portable. I argued that the real point of my example was that logic is in a 3GL outside the DB, which can be Java for example. Java IS portable, making your argument non-existend. I don't really understand your remark about making the problem less important... >>>Second: The lifetime of frameworks and hip languages seem to be lower >>>than data(bases). So if constraint handling and access control is [quoted text clipped - 9 lines] > I consider permission handling as a kind of constraint, so the same rules > apply there, in my belief). There is always a way, so 'where possible' means likely 'almost always'. It's a choice you've to make. We can argue till doomsday about this but I think we'll never get to an agreement :). In the past I've written large scale apps with BL inside T-SQL procs, and in some way it made sense, but in another way it really didn't. Permission handling is IMHO not part of the constraintset of a relational model. > (At this point, I wonder what we actually disagree about.) the point that moving BL code into the DB is good/bad. My POV is that the relational model contains constraints, which you implement in the DB as you implement the relational model in the DB. All other stuff is program logic which you implement outside the DB. You can decide to move SOME code into the DB, but where should you stop? It's a slipperly slope, because there is no general rule what to place inside the db and what to place outside the db. >>Constraint >>handling is thus part of the database, as it is part of the relational [quoted text clipped - 10 lines] > certain ways), but certainly, I used the permission system, considering it > an important part of the general solution. You used the logic provided by the RDBMS outside the DB, i.e. consuming permission DATA outside the DB. Given a set of tables in a catalog. 2 apps target that catalog. One uses table A for user access control, the other uses table B. Doing this in procs, means that there is ONE api, using both systems. Yes, this is bad design, but it's meant to illustrate a point. Also, if you move your app from sqlserver to oracle or vice versa and you implement the access logic in the app itself, you can move the schema, set up roles, db users and you're practically set. This means that when you implement access rights INSIDE procs, which is the alternative suggested here, you have to port these too. However BOTH situations offer the same functionality. If that's the case, there can't be a situation where access logic is part of the db as in the situation where the app uses different users to do things which are placed in different roles inside the db accomplishes the same goals. >>I'll always say: if you want procs, go ahead and use procs. I'm not the >>one who will be suffering the maintenance nightmare nor will I be the [quoted text clipped - 4 lines] > making processes, the decision making group have never seen "the > application" in isolation. I was talking about development times of a large scale application + maintenance costs for such an application. But as I said: if you feel more comfortable with placing as much code inside procs as possible, go ahead. :) Why should someone arguing you can do a lot with dynamically generated SQL as well stop you :) FB >> Exactly the same problem for APIs in other parts of the "solution stack". >> Except that the APIs defined in the database can be general to more than [quoted text clipped - 5 lines] > scenes and call overloads from methods which used to do the work in > older versions. In proc-land this is hard to do: there is one interface. Overloading is not an OO-only phenomenon. You have it in functional languages, for example (though the terminlogy may be different). And you have it in the DBMS which I know best, PostgreSQL. I'm not sure what the standard says about this, though. >>> But I hear you, the portability of C# code can be a problem if your app >>>has to run on apache/linux. The thing is: what I said also goes for Java [quoted text clipped - 9 lines] > non-existend. I don't really understand your remark about making the > problem less important... First, I don't consider Java portable. But that's a long, off-topic issue. (See the "I hate Java" group on Orkut for my most condensed opinion on it.) Second: My point is that middleware doesn't automatically mean portability. - So I can't accept it as an argument for extensive middleware. > Permission handling is IMHO not part of the constraintset of a > relational model. No, certainly not; I agree on that. But it can be a very important constraintset, maintaining "rights-invariants" in a _solution_. > You can decide to move > SOME code into the DB, but where should you stop? It's a slipperly > slope, because there is no general rule what to place inside the db and > what to place outside the db. True. If there were no slippery slopes and gray zones, there might not be a Usenet (or a weblog-community). > Also, if you move your app from sqlserver to oracle or vice versa and > you implement the access logic in the app itself, you can move the > schema, set up roles, db users and you're practically set. This means > that when you implement access rights INSIDE procs, which is the > alternative suggested here, you have to port these too. Oh, now I think I see what you mean. I have yet to see a solution where end-user rights were part of the DBMS' grant-system[*]. The reason is probably that end-user credentials are most often delivered by the application, so one might as well trust the application on this. Where I find great use of the GRANT-system is to separate different applications, or parts of applications. I think of it as equivalent to public/private modifiers in imperative languages. Note *: Although it could probably make sense for frameworks like Oracle Forms. SignatureGreetings from Troels Arvin, Copenhagen, Denmark >>>Exactly the same problem for APIs in other parts of the "solution stack". >>>Except that the APIs defined in the database can be general to more than [quoted text clipped - 10 lines] > have it in the DBMS which I know best, PostgreSQL. I'm not sure what the > standard says about this, though. Even if one were to view overloading as an OO-only phenomenon it still exists in object-relational databases such as Oracle where it is extremely common to overload procedures and functions in packages. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond) > >>How many different scalar queries do you need in a 500 table system, > >>Daniel? A lot, I can tell you. [quoted text clipped - 8 lines] > why, it can be 'maintainable' over the years, but it will be hard to > keep up. So applications shouldn't have a lot of functions or procedures by the same logic. If the application has too many functions or procedures then it is unmaintainable. How is it any different if it is in the backend or the front end (or middle end)? (by QA measures MS's classes that they encouraged people to use in their development are totally unmaintainable) Good documentation, etc. are paramount for maintaining a system regardless of where the code is located. If apps can only acess the DB via procs then you have a nice portable interface that is highly maintainable. No matter what GUI and no matter what language the data integrity is maintained. The data will far outlast the application. Jim > Especially with the larger databases where you have a single API of > procs and multiple applications targeting the procs, it's impossible to [quoted text clipped - 92 lines] > > FB >>>The number of procedures is not interesting, I think. If something has to >>>be done, it has to be specified, somehow. And if it gets too much work, it [quoted text clipped - 14 lines] > documentation, etc. are paramount for maintaining a system regardless of > where the code is located. Ok, but an SP API is for example used by 20% by app A and for 90% by app B. If you write a BL tier in .NET for example, the app A embedding that BL tier uses 100% of that BL tier, i.e.: it is part of A. The maintenance burden is then lower, as the design of A embeds the design of the BL tier in A. With a proc API this isn't the case, as procs used by A can also be used by B or a wide range of other apps. Furthermore, by having functionality in procs in the DB, it fragments functionality accross tier boundaries. This degrades maintenability as the projection of functionality from the functional design onto code is not 1:1 but requires a translation, which means that the connection between functional design and the actual implementation is weaker with the fragmented functionality. > If apps can only acess the DB via procs then you have a nice portable > interface that is highly maintainable. No matter what GUI and no matter > what language the data integrity is maintained. The data will far outlast > the application. You mix things. procs on a DB form an interface, correct. But that interface is for the functionality embedded in the procs, which can be used by a variety of apps. This will cause problems if these apps change so they need a change in the procs -> as changes in the proc api will cause problems, changes are often added as additions, not changes to existing procs. The point you avoid is that what's implemented in the PROC is driven by the requirements of the using application(s), not by what people think is necessary to have when looking at the data. Also, what 'data integrity' really means is a semantical interpretation of that data. This can differ from app to app. data is data, and it will only become information if you interpret it, if you give it semantic value. HOW that's done is not that important: in code outside the db or in code inside the db. WHat's important is that the interpretation of data results in the information you need. If I port data inside an Oracle database with procs to an sql server database with an O/R mapper, I still have the same data, and I can implement the same rules. As a matter of fact: if I have a rules engine in my application, what's the difference between that rules engine in my middle tier vs a rules engine in procs? Nothing. Oh, there is though: the rules engine in my BL tier is part of my application. The rules consume the data, precisely according to Codd's model, they interpret the data the way they have to for my app. Another app, using the same data but with different rules, can come to different conclusions using different rules, as data is just data. (one app can use a less strict rule-set so it won't kick out certain customers for example, other apps will as they're used by different departments for example). You will, with the 2-app scenario, have 2 sets of procs inside the db. Which proc will be used by which app? Confusing, I have to consult docs for that, which make it error prone. Also, data-integrity is a term I see mentioned here a lot. No-one really addresses this correctly IMHO: you have relational model-based integrity, as defined in the ORM/NIAM or if you will, E/R model, and you have business logic rules. Business logic rules are not 1 category, there are 3: 1) attribute-focussed rules (ID > 0) 2) entity-focussed rules (shipdate >= orderdate) (Chen entity) 3) multi-entity spanning rules (order is only valid if inventory > 0) If you think you can enforce rules in category 1) ONLY in the db , you're mistaken: your data will never reach the db, as the data is coming from an outside source: probably a gui, or a conversion file, a webservice... rules in category 2) are often also handled on the client, as you can validate these on teh client so you can avoid calling DAL logic, as an entity isn't valid for example if these rules fail, as all data required is available. In theory, if you ignore real-life scenario's, it might look great, to have all code inside procs. In practise this is undoable, as you will always have rules outside the DB. (if you don't believe me, do you push textbox values from a webgui directly to procs, for example a date in a textbox ? I don't think so. Also, which fields are mandatory and which fields aren't... checks done on the client, but part of teh ruleset of the complete app. Done in the DB? I hope not!) FB > Another app, using the same > data but with different rules, can come to different conclusions using > different rules, as data is just data. (one app can use a less strict > rule-set so it won't kick out certain customers for example, other apps > will as they're used by different departments for example). You will, > with the 2-app scenario, have 2 sets of procs inside the db. Which proc > will be used by which app? Confusing, I have to consult docs for that, > which make it error prone. Those are 2 different apps with two distinct databases in effect: or are you saying Department B is allowed to trash Department A's data because Department B applies its own rules? > Also, data-integrity is a term I see mentioned here a lot. No-one > really addresses this correctly IMHO: you have relational model-based > integrity, as defined in the ORM/NIAM or if you will, E/R model, and you > have business logic rules. Business logic rules are not 1 category, [quoted text clipped - 10 lines] > logic, as an entity isn't valid for example if these rules fail, as all > data required is available. Not sure what your point is here. Looks like you are saying the DBMS should just trust the client and apply no integrity checking of its own. This takes us back to the "bad old days", where each separate client application (GUI screen, bulk data load, web service, etc.) is responsible for integrity, and the database is usually as corrupt as hell. > Also, which fields are mandatory and which > fields aren't... checks done on the client, but part of teh ruleset of > the complete app. Done in the DB? I hope not!) Good grief! You mean every column should allow NULLs? You aren't serious are you? >>Another app, using the same >>data but with different rules, can come to different conclusions using [quoted text clipped - 8 lines] > are you saying Department B is allowed to trash Department A's data > because Department B applies its own rules? 'is allowed' based on what? I supply an example, where you have a large database and various apps targeting that app, and not all apps use 100% of the database. If that sounds like SF to you, you're lucky. >> Also, data-integrity is a term I see mentioned here a lot. No-one >>really addresses this correctly IMHO: you have relational model-based [quoted text clipped - 19 lines] > responsible for integrity, and the database is usually as corrupt as > hell. since when are constraints not able to provide data integrity checking? I tried to prove that business rules are part of an application, and you can't possibly store ALL business rules in procs, as some have to be in a client/gui part. if you think I talk bullshit, try to find ONE webapplication which pushes all data right down to the db's procs. Almost all provide some sort of checks on the data they push down, which is logical, as that's part of the GUI tier's functional description, however at the same time this embeds some of the rules. >>Also, which fields are mandatory and which >>fields aren't... checks done on the client, but part of teh ruleset of >>the complete app. Done in the DB? I hope not!) > > Good grief! You mean every column should allow NULLs? You aren't > serious are you? This gets tiresome... do you people think the DB is the app and for the rest everyone can go to hell? How would you develop a webapplication which has forms with say 10 fields and 8 of them are mandatory? You simply read what the user fills in (for example just 1 field) and push that down to the procs and something comes back eventually? And a 'date' field (where the user filled in 'Foo!') is pushed HOW down to the procs? Remember, clientside fields don't have to be typed (textboxes on webforms for example). Where did I say every column is nullable? Nowhere, on the contrary. However on the CLIENT (which can be, what, 5 tiers away from the db layer) I have to have information which fields are mandatory and which aren't. Mandatory fields don't necessarily have to be non-nullable fields mind you, it can also be info for BL processes. Frans. > > Those are 2 different apps with two distinct databases in effect: or > > are you saying Department B is allowed to trash Department A's data [quoted text clipped - 3 lines] > database and various apps targeting that app, and not all apps use 100% > of the database. If that sounds like SF to you, you're lucky. What's SF? So you are saying the data is segregated by app? If so, fine: the database will only have to enforce the integrity rules that they have in common. > since when are constraints not able to provide data integrity checking? Who said they were not? > I tried to prove that business rules are part of an application, and you > can't possibly store ALL business rules in procs, as some have to be in [quoted text clipped - 3 lines] > is logical, as that's part of the GUI tier's functional description, > however at the same time this embeds some of the rules. Of course they do! But that doesn't mean the DBMS should be as dumb as you like and not perform any integrity checking itself. The GUI is all about providing a decent user interface, and a decent user interface helps users not to make stupid mistakes. > > Good grief! You mean every column should allow NULLs? You aren't > > serious are you? [quoted text clipped - 7 lines] > Remember, clientside fields don't have to be typed (textboxes on > webforms for example). Of course, I would develop a web application to be user-friendly so I would: - enforce input on the 8 mandatory fields - ensure that the user only types dates in date fields - provide a drop-down list for status codes As a database designer, however, I would also define constraints on the database to: - enforce non-null values for the 8 mandatory columns via NOT NULL consraints - enforce DATE values in the date column (i.e. I would use data type DATE not VARCHAR!) - enforce correct status codes via a CHECK constraint or a FOREIGN KEY constraint as appropriate > Where did I say every column is nullable? Nowhere, on the contrary. > However on the CLIENT (which can be, what, 5 tiers away from the db > layer) I have to have information which fields are mandatory and which > aren't. Mandatory fields don't necessarily have to be non-nullable > fields mind you, it can also be info for BL processes. OK, we seem to be in agreement here: mandatory fields in the GUI *and* NOT NULL constraints on the column (where appropriate). >>>Those are 2 different apps with two distinct databases in effect: or >>>are you saying Department B is allowed to trash Department A's data [quoted text clipped - 5 lines] > > What's SF? Science Fiction ;) > So you are saying the data is segregated by app? If so, > fine: the database will only have to enforce the integrity rules that > they have in common. I don't say the data is segregated by app, as that doesn't have to be the case. More than one app can use data from one table for example. This can be the case for example in reporting apps using data which is updated in a management application, or 1 app doing 20% of the work of a big app which is used by other people than the users of teh 20% app >>since when are constraints not able to provide data integrity checking? > > Who said they were not? as not having procs in the DB apparently makes the DB unable to provide data-integrity, according to you (or I misinterpreted your words). Constraints enforce that for you. Of course, there are often more rules thinkable, like business rules, but as I said, this can be different per app consuming the data, which means these rules are related to the application consuming the data, not to the relational model nor to the data inside the model, as the raw data is used by different apps, and used with different rulesets. >>I tried to prove that business rules are part of an application, and > you can't possibly store ALL business rules in procs, as some have to be [quoted text clipped - 6 lines] > Of course they do! But that doesn't mean the DBMS should be as dumb as > you like and not perform any integrity checking itself. I have stated a couple of times that I firmly believe in the relational model WITH constraints as these are part of that relational model and thus have to be implemented WITH that relational model where that's implemented, i.e.: the database. So I don't see where I said data integrity checks are not in order in the db. The problem I think here is what the definition of data-integrity really is. To some it's the set of constraints to keep data consisted in a relational model, to others its far more than that, also business logic rules should be added to the list of data-integrity. But as I said before: if you have a customer db, you can have 2 apps which consume the same data but come to different conclusions based on the data + ruleset as one app can only work with 'gold customers' for example. Integrity of the data is then for THAT app only valid in the scope of that app, as the data is used with different rules in another app. "Use 2 databases for that" is an often heared 'solution' to this, but this is creating redundant databases, not what you want. >> How would you develop a webapplication >> which has forms with say 10 fields and 8 of them are mandatory? You [quoted text clipped - 3 lines] >>Remember, clientside fields don't have to be typed (textboxes on >>webforms for example). > Of course, I would develop a web application to be user-friendly so I > would: > - enforce input on the 8 mandatory fields > - ensure that the user only types dates in date fields > - provide a drop-down list for status codes good :) > As a database designer, however, I would also define constraints on the > database to: [quoted text clipped - 4 lines] > - enforce correct status codes via a CHECK constraint or a FOREIGN KEY > constraint as appropriate good too! then we're on the same ground. :) Frans. > as not having procs in the DB apparently makes the DB unable to provide > data-integrity, according to you (or I misinterpreted your words). ... > I have stated a couple of times that I firmly believe in the relational > model WITH constraints as these are part of that relational model and > thus have to be implemented WITH that relational model where that's > implemented, i.e.: the database. So I don't see where I said data > integrity checks are not in order in the db. The problem I think here is > what the definition of data-integrity really is. ... > good too! then we're on the same ground. :) Yes, we seem to agree at lot more than I thought! My fault - I jumped into this discussion half-way through and misunderstood what you were saying. I didn't mean to imply that stored procs are necessary for data integrity: I agree that constraints should be able take care of that. I mistakenly thought you were saying that constraints weren't necessary either. :) >> Good grief! You mean every column should allow NULLs? You aren't >> serious are you? > > > This gets tiresome... do you people think the DB is the app and for > the rest everyone can go to hell? Of course not. But you seem to think that the database is a large bucket used to hold data. Where business rules should be written is a complex decision that must be made based on many factors including security, stability, scalability, performance, and maintainability. You seem to have taken a hard-line "us vs them" attitude that has been proven in case study after case study to lead to data corruption. The problem here is that you really have, at best a limited understanding of what a real commercial RDBMS is how to best leverage its abilities. And this is something you have demonstrated on your web site and here in this usenet group. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond) >>> Good grief! You mean every column should allow NULLs? You aren't >>> serious are you? [quoted text clipped - 5 lines] > decision that must be made based on many factors including security, > stability, scalability, performance, and maintainability. I don't see a database as a bucket which holds data, I see teh database as the place where a relational model is implemented. WITH that model you implement its constraints for integrity of the data so it is correct according to the relational model. Your second sentence is IMHO not matchable with what you try to flame me about, as the only right way to write business rules according to you is in procedures, how else could you be so insulting towards me? > You seem to have taken a hard-line "us vs them" attitude that has been > proven in case study after case study to lead to data corruption. us vs them? Where did I do that? I just enlisted the disadvantages of crud procs, and apparently that's a very bad thing to do because it fuels all kinds of hate-filled postings like you've placed here in this thread. The topic of if CRUD procs are essential or not is a taboo, as it is hardly discussable, this thread has proven that. If I re-read some postings here, the only safe way of writing software is by writing a mainframe program and every user should have a terminal to that mainframe so there are no real client programs and everything can be controlled by one big proc layer so it apparently is 'safe'. data-corruption can be prevented in a number of ways. One being check logic in the core code which controls access to the database. Your solution is a proc layer, which is your choice, but there are other options, which apparently cause some flames from you. Which is odd, as I'm not alone in this, but these people must be really stupid of course, because 'case study after case study' proves them wrong! (which case studies, Daniel?. Microsoft has a lot of 'case studies' on their website as well, do you believe them too?) > The problem here is that you really have, at best a limited understanding > of what a real commercial RDBMS is how to best leverage its abilities. hehe sure :) Have you ever read what I've written about relational models, stateless programming, n-tier development, what an entity is, where it lives and all that? Sure you did. I really fail to see where I failed to leverage the commercial RDBMS's abilities. That must be of course because I lack severe knowledge, the knowledge you of course have. ;) > And this is something you have demonstrated on your web site and here > in this usenet group. Did I? On my website? Hmm. So you actually went out and read the stuff I wrote? And you were personally offended by it of course, insulted till the bone, because your reactions here were of that kind. But I've to dissapoint you, I think I'll call it a day now, so no more entertainment for you I'm afraid. :) But don't forget to pickup the books by Fowler and Evans. FB > I don't see a database as a bucket which holds data, I see teh > database as the place where a relational model is implemented. WITH that > model you implement its constraints for integrity of the data so it is > correct according to the relational model. Perhaps you haven't read your own statements. We are not reacting to something dredged up off a stack of Taro cards. We are reacting to the only things we know of you: What you yourself have written. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond) >>Another app, using the same >>data but with different rules, can come to different conclusions [quoted text clipped - 69 lines] > Good grief! You mean every column should allow NULLs? You aren't > serious are you? And some people wondered why I reacted as I did to Bouma's web site. The only good thing I can say about his ideas is that he provides a continuing source of income for those that come along in his wake, probably one to two years later, and clean up his messes. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond) >> The only good thing I can say about his ideas is that he provides a continuing source of income for those that come along in his wake, probably one to two years later, and clean up his messes. << Based on my consulting work (and 30+ years in the trade) doing just that kind of thing, the "magic moment" is one (fiscal) year. That is the time when you have to produce enterprise level summary reports, take inventory, etc. or go to jail. This is when you see all of the differences in the data that each front end program caused. Another "Magic Moment" is when you are starting to think about a data warehouse. Much the same reason; the data is looked at at the enterprise level for the first time with a global data and metadata model. The third revelation comes with new software that gives users a second interface to the system and skips over the existing business rules in the old interface. A really cute version of this disaster was a new report writer that did not know about "special codes" and which computed the MOD() function for negative numbers a little differently. Nobody thought that a simple "break & totals" utility program would blow up. >>>The only good thing I can say about his ideas is that he provides a > [quoted text clipped - 19 lines] > Nobody thought that a simple "break & totals" utility program would > blow up. Thanks to Sarbanes-Oxley we will likely be even more popular. ;-) SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond) >>>The only good thing I can say about his ideas is that he provides a > [quoted text clipped - 6 lines] > take inventory, etc. or go to jail. This is when you see all of the > differences in the data that each front end program caused. Explain to me something: an application has a design, which defines which functionality has to be implemented and what it has to do. Now, let's just step away from the below-the-belt insults some people apparently have to place here because they are offended in a horrible way, and look at what we have here. With that design, I can test if an implementation does what it should do, correct? Now, it doesn't matter how I implement that design, the application has to work correctly, be it using procs, be it using a central DAL, be it using a central service... How can I have differences in my application, if I test that application against the design, which states that functionality ABC has to produce XYZ when this and that happens. Be it a proc producing that data or some BL code doesn't matter, the code has to be implemented and has to be correct. > Another "Magic Moment" is when you are starting to think about a data > warehouse. Much the same reason; the data is looked at at the [quoted text clipped - 8 lines] > Nobody thought that a simple "break & totals" utility program would > blow up. Still, if rules are defined in the application design, and later on implemented (which IMHO is the proper way to do it, you don't cook up rules when typing code, these are defined first), you can test if the rules are implemented correctly. If someone fails to do that, testing if the rules are applied correctly, then yes, software might result in bad information. Your argument thus is a lack of testing to the defined rules. How can that be an argument to avoid any rule in the application itself? (besides that, how would you avoid ANY rule being implemented in a client if that client works, say, disconnected from the db? FB >>>> The only good thing I can say about his ideas is that he provides a >> [quoted text clipped - 13 lines] > way, and look at what we have here. With that design, I can test if an > implementation does what it should do, correct? OK, so you are saying the design defines what the application has to do. That is wrong. The specification defines what the application has to do. With a *specification*, you can test if an implementation meets it (to a certain extent in finite time). > Now, it doesn't matter how I implement that design, the application > has to work correctly, be it using procs, be it using a central DAL, be > it using a central service... I agree that the application should meet the spec. You are free to provide any solution that meets the specification. Note: If the specification says (eg) that you must be able to guarantee that the database meets certain integrity constraints regardless of DML fired at it, some of your example design approaches fail instantly. > How can I have differences in my application, if I test that > application against the design, which states that functionality ABC has > to produce XYZ when this and that happens. Be it a proc producing that > data or some BL code doesn't matter, the code has to be implemented and > has to be correct. Of course, you need to test against the spec as I agreed above. For a sufficiently bad specification, one could provide something that was hacked up with no regard for structure or maintainability, and could still meet the spec successfully. In this case, I would seek to shoot the specifier. >> Another "Magic Moment" is when you are starting to think about a data >> warehouse. Much the same reason; the data is looked at at the [quoted text clipped - 13 lines] > rules when typing code, these are defined first), you can test if the > rules are implemented correctly. I agree that you don't cook-up rules while typing code. Specification (inc rules) -> Design -> Implementation. > If someone fails to do that, testing if the rules are applied > correctly, then yes, software might result in bad information. Your > argument thus is a lack of testing to the defined rules. How can that be > an argument to avoid any rule in the application itself? (besides that, > how would you avoid ANY rule being implemented in a client if that > client works, say, disconnected from the db? Speaking from a database perspective (as we are in comp.databases), if the client is disconnected from the db, we don't care about it. As a database designer, one's job is to ensure that the database stays in good condition. By using (eg) integrity constraints and sprocs, this is made much easier, since the application programmer can only interact with the database using fixed procedures that the database designer can check. That is good multi-tiered design. Of course, by infinite testing, one could provide an unstructured application with no bugs. In the real world, infinite testing -> infinite time which can be quite expensive, so we tend to rely on structured design to reduce the testing burden. John >>>>> The only good thing I can say about his ideas is that he provides a >>> continuing source of income for those that come along in his wake, [quoted text clipped - 15 lines] > OK, so you are saying the design defines what the application has to do. > That is wrong. The specification defines what the application has to do. design ... specification, functional/technical research document, whatever you call it, I meant the theoretical document which describes the functionality the application has to provide. > With a *specification*, you can test if an implementation meets it (to a > certain extent in finite time). yes, that's what I said, 'design' could be interpreted wrong, which is unfortunate, I should have used another term. >> If someone fails to do that, testing if the rules are applied >> correctly, then yes, software might result in bad information. Your [quoted text clipped - 10 lines] > with the database using fixed procedures that the database designer can > check. That is good multi-tiered design. multi-tiered design doesn't have rules written in stone, so claiming something is 'good' or 'bad' is hard to do in this case, no matter what that is that is good or bad. Your example of procs has one flaw: the calls of the procs and the input for the procs is coming from another tier. What if I call the wrong proc? What if I supply bad input? It still can corrupt your data, as a business process for example calls 10 procs in sequence and doesn't do that correctly. Obviously one can argue to move the sequence control code in a proc as well. But this would collide the BL tier with the DAL tier, semantically speaking, which could be a point to avoid if someone opts for a tier-ed design based on functional separation. I don't see a difference executing DELETE FROM Table WHERE ... from a DAL tier or calling a proc which solely does DELETE FROM Table WHERE... (the exact same DML). The point is: the code CALLING the proc decides what to delete as that depends on the input of the proc (e.g. PK values passed in to the proc). The code calling the SQL generator with input is semantically the same: it provides the input for the SQL generator (indirectly) and provides teh PK value for the entity to delete. > Of course, by infinite testing, one could provide an unstructured > application with no bugs. In the real world, infinite testing -> > infinite time which can be quite expensive, so we tend to rely on > structured design to reduce the testing burden. true, but that doesn't mean that using procs is thus lowering the amount of testing hours and increasing the chance of success, as the same can be accomplished with a DAL outside the db. The only difference is that the DBA is not that involved anymore with developing the DAL as he/she is with developing the procs, causing an important degration of the responsibility a DBA has in a project, at least that's how some DBA's see it. I don't think that's correct though, as tomorrow's database usage will move towards more datamining usage which requires more code inside the RDBMS as pumping data towards code outside the DB just for sime silly processing is often not that efficient, plus using data for datamining purposes is relying heavily on Codd's model, which is not that matchable with using data in an OO fashion, like most O/R mapper layers provide. FB <snip> > Your example of procs has one flaw: the calls of the procs and the > input for the procs is coming from another tier. What if I call the [quoted text clipped - 4 lines] > tier, semantically speaking, which could be a point to avoid if someone > opts for a tier-ed design based on functional separation. I think we've got to the crux of the matter here. You think that the middle tier can corrupt the data, which is a misconception. The whole point is that the middle tier is unable to corrupt the database. With a properly designed database, the middle tier should be able to spit randomly generated data at the database through the stored procedures for all time without corrupting the database at all. Lets look at your sequence control example. I have just finished doing the database work for a sales order processing system, which consists of 9 states and 10 stored procedures to move from state to state. There is no "sequence control code" per se, simply a constraint that each sproc has a set of preconditions under which it will operate. If the middle tier got out of sequence, the console would just show some complaints from the database and the data would have kept its integrity. The consequence of this is some redundancy (since the presentation, middle and persistence tiers should all enforce integrity). This is a good thing though, and acts as a check for the times when we get things wrong. John > <snip> > [quoted text clipped - 14 lines] > randomly generated data at the database through the stored procedures > for all time without corrupting the database at all. This can only be the case if all data in the system is always coming from the db itself. If you allow input coming from the outside, you can have a proc which accepts input which will be not correct semantically, unless ALL BL is in procs. I already explained that that would mean the BL and DAL tier be the same, effectively throwing away n-tier development. > Lets look at your sequence control example. I have just finished doing > the database work for a sales order processing system, which consists of [quoted text clipped - 3 lines] > tier got out of sequence, the console would just show some complaints > from the database and the data would have kept its integrity. no offence but isn't that sequence control IN the proc layer? > The consequence of this is some redundancy (since the presentation, > middle and persistence tiers should all enforce integrity). This is a > good thing though, and acts as a check for the times when we get things > wrong. as soon as you work with tiers, you have stale data. When you have stale data, you have problems keeping in sync, as that's impossible, unless you lock rows explicitly, which kills performance / scalability. So if the BL tier runs 10 updates, in a transaction, and checks if everything goes all right (i.e. an update succeeds), it can commit the transaction after the 10th update and it should be correct, according to the BL's point of view on the system. If you need real-time data management, that's another story, but let's say a webapplication is powered by your database, that's an n-tier app with stale data in the gui stack. When the BL code succeeds, why would you check twice in the procs as well? Why even bother checking in the BL tier? Say you have a new customer, new orders for that customer, that's 3 different record types minimum, probably more. That data has to be written in sequence into the db, first customer, then order, then order detail. Where's this controlled? IMHO in the BL tier. If you want to do this in the proc-tier, you have to push all data to one proc which controls that. So say, I use procs for new customer, new order and new order detail. What if I call new customer twice from the BL? Then I have a dangling customer with no orders. However you can't test in your customer insert proc if it has orders, as these have to be written next, due to an FK in order. I do think we agree on the fact that working with data requires rules enforced in the system doing the data manipulation. It's up for debate where these rules are implemented, IMHO. FB I participated on both kinds of project developments. In the one we used stored procedures, it was a pain every time a new column was added or removed - all our insert/update SP's had to have their parameter signature changed, with corresponding changes in the calling programs. And talk about sending up to 60 parameters to an SP. In other system, we used a DAL - granted, it is pretty much a CRUD system. I didn't write a line of SQL and I didn't miss it a bit. The system has good performance and the DAL is even used to handle batch entries in the system. I would certainly use SP's if the security requirements warrant it or if I have some intrincate SQL for reporting. But I would never think on developing business logic into them. That's why you have frameworks like BizTalk or similar ones - they don't use SP's AFAIK. I try to choose the best tool for the job. And DALs have served me well so far. Otavio > I participated on both kinds of project developments. In the one we used > stored procedures, it was a pain every time a new column was added or [quoted text clipped - 15 lines] > > Otavio Your statements about having to modify your insert/update SP's is not indicative of stored procedures ... it is indicative of bad design and bad change management policies. Put the blame where it belongs. Otherwise your experience is not all that different from that of many in similar situations. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond) > Your statements about having to modify your insert/update SP's is not > indicative of stored procedures ... it is indicative of bad design and > bad change management policies. Put the blame where it belongs. Ok, please help me here. You have to insert data to a table, with let's say 40 columns. How do you do that without passing all of them in separate parameters? And when you add/remove a column, how can you design it so that it won't break the contract between the old and new SP signature. I'm being sincerely curious here, I tried to use a structured type but couldn't do it in Oracle 8.7. > Otherwise your experience is not all that different from that of many > in similar situations. [quoted text clipped - 3 lines] > damorgan@x.washington.edu > (replace 'x' with 'u' to respond) >>Your statements about having to modify your insert/update SP's is not >>indicative of stored procedures ... it is indicative of bad design and [quoted text clipped - 7 lines] > sincerely curious here, I tried to use a structured type but couldn't do it > in Oracle 8.7. I assume you mean Oracle 8.1.7 and you could have done it by passing a loosely typed ref cursor. Examples of this are posted on my site at www.psoug.org and by Tom Kyte at asktom.oracle.com. In Oracle always define variables as table_name.column_name%TYPE and rows as table_name%ROWTYPE or cursor_name%ROWTYPE and the issue goes away. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond) > I assume you mean Oracle 8.1.7 and you could have done it by passing a > loosely typed ref cursor. Examples of this are posted on my site at > www.psoug.org and by Tom Kyte at asktom.oracle.com. That's correct, 8.1.7 it is, thank you. It is interesting to pass a ref cursor *to* a SP - I always thought you would get that *from* the SP. I'll check the references above, but can you tell me how to create a loosely typed ref cursor in Java, populate it with the values and send it to the SP? That's precisely the type of thing I was trying to do, unsuccessfully. > In Oracle always define variables as table_name.column_name%TYPE and > rows as table_name%ROWTYPE or cursor_name%ROWTYPE and the issue goes > away. I did that, but still I had to declare a new variable for the newly added column on inserts and updates. Regards, Otavio > -- > Daniel A. Morgan > University of Washington > damorgan@x.washington.edu > (replace 'x' with 'u' to respond) >>I assume you mean Oracle 8.1.7 and you could have done it by passing a >>loosely typed ref cursor. Examples of this are posted on my site at [quoted text clipped - 5 lines] > typed ref cursor in Java, populate it with the values and send it to the SP? > That's precisely the type of thing I was trying to do, unsuccessfully. Some product, Crystal Reports for example, only interact with Oracle with IN OUT ref cursors. Pass in nothing get back a result set. >>In Oracle always define variables as table_name.column_name%TYPE and >>rows as table_name%ROWTYPE or cursor_name%ROWTYPE and the issue goes [quoted text clipped - 6 lines] > > Otavio Use the loose typing and the problem goes away. There is a demo of passing ref cursors at: http://www.psoug.org click on Morgan's Library click on REF CURSOR scroll to the bottom The cursors here are defined using SYSREFCURSOR which is proper for 9i or 10g so you will still need to use the older syntax for 8i. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond) > > That's correct, 8.1.7 it is, thank you. It is interesting to pass a ref > > cursor *to* a SP - I always thought you would get that *from* the SP. I'll [quoted text clipped - 4 lines] > Some product, Crystal Reports for example, only interact with Oracle > with IN OUT ref cursors. Pass in nothing get back a result set. I understand getting ref cursor coming from a SP. What I still don't understand is how to use the same concept (REF CURSORs) to pass information from an entry screen, for example, to the stored procedure that is going to insert them into the database, without assigning nme_first, dte_birth, etc, one by one, to the SP's INPUT parameters. Thank you again, Otavio > -- > Daniel A. Morgan > University of Washington > damorgan@x.washington.edu > (replace 'x' with 'u' to respond) >>>That's correct, 8.1.7 it is, thank you. It is interesting to pass a ref >>>cursor *to* a SP - I always thought you would get that *from* the SP. [quoted text clipped - 20 lines] > > Otavio That is something that will change from product to product so I probably can't help you with it. Check http://asktom.oracle.com as the answer is likely already there and possibly submit it as a new question if you can't find the answer. Also consider asking in the usenet group: comp.databases.oracle.server. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond) > > That's correct, 8.1.7 it is, thank you. It is interesting to pass a ref > > cursor *to* a SP - I always thought you would get that *from* the SP. I'll [quoted text clipped - 4 lines] > Some product, Crystal Reports for example, only interact with Oracle > with IN OUT ref cursors. Pass in nothing get back a result set. I understand getting ref cursor coming from a SP. What I still don't understand is how to use the same concept (REF CURSORs) to pass information from an entry screen, for example, to the stored procedure that is going to insert them into the database, without assigning nme_first, dte_birth, etc, one by one, to the SP's INPUT parameters. Thank you again, Otavio > -- > Daniel A. Morgan > University of Washington > damorgan@x.washington.edu > (replace 'x' with 'u' to respond) Oops! "Otavio C. Decio" <REMOVEMEodecio@earthlink.net> was seen spray-painting on a wall: >> Your statements about having to modify your insert/update SP's is >> not indicative of stored procedures ... it is indicative of bad [quoted text clipped - 7 lines] > and new SP signature. I'm being sincerely curious here, I tried to > use a structured type but couldn't do it in Oracle 8.7. Sounds like the .sig... Any occasion in which I have created SPs with large numbers of parameters, things have turned out badly in almost exactly that way. Either: a) I had a whole bunch of badly specified parameters, or b) There were irritating missing ones. By and large, the principle seems to fit not too terribly badly with the number of columns in a table, too. Having more than 10 makes it likely that there will be a few that are ill-defined... :-( Signaturelet name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];; http://cbbrowne.com/info/emacs.html If you have a procedure with ten parameters, you probably missed some. -- Alan J. Perlis > Oops! "Otavio C. Decio" <REMOVEMEodecio@earthlink.net> was seen spray-painting on a wall: > [quoted text clipped - 22 lines] > the number of columns in a table, too. Having more than 10 makes it > likely that there will be a few that are ill-defined... :-( In Oracle one can do 40 columns with a single parameter ... either pass a ref cursor or an array. The OP's issue was he doesn't know how from his front-end tool. The back-end code is only about 3 lines. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond) > > Sounds like the .sig... > > [quoted text clipped - 4 lines] > > a) I had a whole bunch of badly specified parameters, or > > b) There were irritating missing ones. I was handed the database to program against it, and indeed it had tables with more than 40 columns, and that was it. > > By and large, the principle seems to fit not too terribly badly with > > the number of columns in a table, too. Having more than 10 makes it [quoted text clipped - 3 lines] > pass a ref cursor or an array. The OP's issue was he doesn't know how > from his front-end tool. The back-end code is only about 3 lines. This is true, and I'm still looking for answers in Java on how to do it. > -- > Daniel A. Morgan > University of Washington > damorgan@x.washington.edu > (replace 'x' with 'u' to respond) >>In Oracle one can do 40 columns with a single parameter ... either >>pass a ref cursor or an array. The OP's issue was he doesn't know how [quoted text clipped - 6 lines] >>damorgan@x.washington.edu >>(replace 'x' with 'u' to respond) This generic usenet group is not it. Use c.d.o.server and one of the Java groups instead. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond) > This generic usenet group is not it. Use c.d.o.server and one of the > Java groups instead. ... where they'll probably recommend using EJB's and forget about this SQL nonsense. > -- > Daniel A. Morgan > University of Washington > damorgan@x.washington.edu > (replace 'x' with 'u' to respond) > I participated on both kinds of project developments. In the one we used > stored procedures, it was a pain every time a new column was added or [quoted text clipped - 5 lines] > didn't write a line of SQL and I didn't miss it a bit. The system has good > performance and the DAL is even used to handle batch entries in the system. I can't see any value of DAL _at all_ as far as abstraction is concerned; it's nothing more than a pale PHP imitation of Perl DBI. It doesn't look like it has a way of coping with cursors, so you haven't got anything in between: a) Grabbing a whole result set as an array/hash set, or b) Requesting results individual row by individual row. > I would certainly use SP's if the security requirements warrant it or if I > have some intrincate SQL for reporting. But I would never think on [quoted text clipped - 3 lines] > I try to choose the best tool for the job. And DALs have served me well so > far. Near as I can tell DAL's just a PHP "abstraction layer" to make access to some LCD form of MySQL and PostgreSQL look the same. Maybe there's more to it in CVS HEAD, but that seems unlikely... Signaturelet name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];; http://cbbrowne.com/info/rdbms.html If olive oil comes from olives, where does baby oil come from? >> I agree that you don't cook-up rules while typing code. Specification (inc rules) -> Design -> Implementation. << But they do cook-up rules! :) That was a reason for databases -- a single trusted source with constraints instead of a zillion files each will a slight "data drift" over time. Without a central set of rules **enforced by a machine**, setting up testing is nearly impossible. >> How can I have differences in my application, if I test that application against the design, which states that functionality ABC has to produce XYZ when this and that happens. Be it a proc producing that data or some BL code doesn't matter, the code has to be implemented and has to be correct. << Let's say we have a small database with 1000 data elements and 50 application programs to against it. That means at least one simple constraint per data element (range check, grep(), check digit, whatever) and other constraints among the data elements (DRI, triggers, assertions, etc.)-- say a mere 1500 rules to enforce. This is a bitch to write correctly once. I would recommend a decision table or other logic tool for some of this work and an ORM tool for the rest of it. What are the odds of 50 programs enforcing all those rules correctly? Assuming that you have a perfect test harness, what is the cost of program validation? What is the size of the programs, if they have to look at 1000+ rules? What is the speed of the programs, when they have to re-do the same calculations over and over, instead of sharing materialized views? When a rule changes how do I cascade the change in the VB, C++, COBOL et al programs? If I had business rules engine that supported code generation, this might be possible in theory. We tried this in the 1980's in the US Army and other places -- check Mack Alford's work with specification languages. Unfortunately, the spec languages were more complex than the problems and faded away. If the rules are encapsulated in the RDBMS constraints and procedures, I have no redundancy and one trusted source. I can share materialized views. I can refactor common routines in my procs, but try to share code between COBOL (78% of the world's programs are in COBOL) and any OO language. >> how would you avoid ANY rule being implemented in a client if that client works, say, disconnected from the db? << I'd like some rules implemented in the client. The most obvious one is check digit validation and other immediate local things, like an entry clerk id. Things that can give immediate feedback to the user. But the client side should not be the final, trusted source for the validation. The redundant calculations on the database side are FAR cheaper than a corrupted database. > And some people wondered why I reacted as I did to Bouma's web site. > The only good thing I can say about his ideas is that he provides a > continuing source of income for those that come along in his wake, > probably one to two years later, and clean up his messes. Ok, apparently you just want to play the 14-year old child, throwing mud instead of discussing things. Like someone said: as long as they talk about you, you or what you have to say are apparently important. Have a good day, mr Morgan. Oh, and when you visit the bookstore, look up some books about mr. Fowler or mr. Evans. FB > > Another app, using the same > > data but with different rules, can come to different conclusions [quoted text clipped - 51 lines] > Good grief! You mean every column should allow NULLs? You aren't > serious are you? Andrew, The problem here is that the guy doesn't know what he is talking about and has almost no actual experience. He doesn't have the slightest idea of what data integrity is and is taking a relativistic approach. (data integrity is what the app says it is not what it should be. The road to Hell is that way. ) If you can't agree what the data means between apps (and they share data) then you have real big problems. <sarcasm on> Yeah, put the data integrity rules in all those apps, that makes lots of sense. That is highly maintainable. <sarcasm off> In good OOP one tries to centralize data access in one place not in every thing that uses the object! That's one of the main points of OOP. So by the same token put the data integrity in the place that has the data (database) not outside the database and rely on everyone using the same application/language/OS platform. At least in the database if the rules are forgotten the database could tell you what they are and prevent you from breaking your data integrity rules. If the rules have to be replicated across multiple software projects (all without sufficient time) you descend into Hell. Jim > Andrew, > The problem here is that the guy doesn't know what he is talking about and > has almost no actual experience. He doesn't have the slightest idea of what > data integrity is and is taking a relativistic approach. ah another person I apparently have met and who can see what I know and think. > (data integrity is what the app says it is not what it should be. The road to Hell is that > way. ) If you can't agree what the data means between apps (and they share > data) then you have real big problems. Ah finally someone provides a definition. You fail to understand that what "what the data means between apps" means is that the app does the interpretation of raw data. Data doesn't mean anything, an application gives it meaning, by interpreting it. This is what I was trying to illustrate. However some people here have the urge to avoid discussion and go into the 'let's flame his a.s off' mode to make their 'point'. Which is what I don't understand really. Does what I say make your job go away in the next 5 years? It seems so, otherwise why are you so hostile? Oh and before you launch another backstab at me: I'm not the inventor of what I said. See for example the books mr. Fowler, Evans and others. You apply 'procedures' as the solution for something that is far more theoretical, i.e.: the implementation is not important, it's how the theory behind it is structured that makes the problem gets solved. > <sarcasm on> Yeah, put the data > integrity rules in all those apps, that makes lots of sense. That is highly > maintainable. <sarcasm off> In good OOP one tries to centralize data access > in one place not in every thing that uses the object! Says who? OOP is applying behavior to data. But where did I state that integrity rules are placed where the object is used? You haven't addressed what integrity rules really are. I've mentioned 3 categories. Have you solved that problem yet? Or do you leave taht to the people who have to apply your 'ideas' to real-world software? > That's one of the main points of OOP. So by the same token put the data integrity in the > place that has the data (database) not outside the database and rely on > everyone using the same application/language/OS platform. At least in the > database if the rules are forgotten the database could tell you what they > are and prevent you from breaking your data integrity rules. And since when are rules invented in code? Isn't code the implementation of rules defined in the design? How on earth can 'rules be forgotten' if you have the design right there in your hands? > If the rules have to be replicated across multiple software projects (all without > sufficient time) you descend into Hell. If rules have to be shared among projects you have a couple of options to share these rules among those projects. Needless to say, it totally depends on what these projects are: a handheld client, a service, a website? perhaps another database with a proc layer? Who knows... it depends on what the clients are how you will be able to share the rules. If the only thing in common is a proc layer, that might be the right choice, but it doesn't have to be the right choice as that choice also gives a lot of disadvantages, which are ignored by pro-proc people. FB > Ah finally someone provides a definition. You fail to understand that > what "what the data means between apps" means is that the app does the > interpretation of raw data. Data doesn't mean anything, an application > gives it meaning, by interpreting it. This is what I was trying to > illustrate. But that is completely wrong! The data in a DATABASE *does* have meaning of itself, it does not need an external application to give it meaning. A row in the EMPLOYEE table with EMPNO=123 and NAME='Smith' asserts that there is an employee identified by empno 123 whose name is 'Smith'. That meaning is inherent in the database design. Of course, you *could* create a table called WHATEVER, with columns SOME_NUMBER and SOME_TEXT, and your application could give rows in that table the meaning that SOME_NUMBER is an employee number and SOME_TEXT is an employee name. And then you could write another application that stored more data in that same table but gave it a different meaning, e.g. SOME_NUMBER is an ISBN and SOME_TEXT is the name of the book. But I hope you would agree that that would be silly!? >> Ah finally someone provides a definition. You fail to understand > that what "what the data means between apps" means is that the app does [quoted text clipped - 4 lines] > > But that is completely wrong! No it's not. > The data in a DATABASE *does* have > meaning of itself, it does not need an external application to give it > meaning. A row in the EMPLOYEE table with EMPNO=123 and NAME='Smith' > asserts that there is an employee identified by empno 123 whose name is > 'Smith'. That meaning is inherent in the database design. No, it means there are bits which you interpret as an employee table. |
Enable EMail Alerts
Start New Thread
Reply to this Message