Consider what goes on in most such setups.

Post #154,733 by mmoffitt 5/11/04 11:46:05 PM Reply	Consider what goes on in most such setups. You have several textboxes on a form, each bound to a column in a table. You allow the user to edit the values in those textboxes. If the updates are "immediate" what has to happen? A separate Update query is fired each time the data in the textboxes is changed. I admit, this is the situ when MSFT tools are used, but what do you guess is the most prominent "bound control" tool publisher? Surely you'd agree that base table access is taboo, no? bcnu, Mikem If you can read this, you are not the President.
Post #154,790 by ben_tilly 5/12/04 10:01:53 AM Reply	Don't be sure about the surely First of all I'm unclear whether the discussion is about a "live bound control" or merely a UI with direct mappings between form elements and the database. The former adds load, round trip latency, and similar issues. The UI is not an unreasonable idea if the database and UI that result fits your usage pattern. But on direct access to data, your surely is wrong. I am convinced that routing all table access through layers of stored procedures adds development complexity. I'm not nearly as convinced that it adds real value. If you think that I should be convinced, then you need to provide me with better reasons to be convinced than just shock and outrage that I would not be convinced of orthodox opinion. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #154,794 by admin 5/12/04 10:09:33 AM Reply	Re: Don't be sure about the surely As I mentioned elsewhere, we have a good million lines of stored procedures here. There is a mix of direct DML in the code, and DML wrapped with stored procedures. Without exception the wrapped DML is easier to maintain. I recently had to recode a large subsystem that was rife with direct DML calls that was quickly becoming a maintenance nightmare, especially since it was competing with other portions of the code using direct DML on the same tables. Any change to the tables required changes to several different pieces of code. Now, I'm not saying that things have to be wrapped in stored procedures, but they should be wrapped in some fashion. There should be only one access point to the underlying data, whether that's a stored procedure or some wrapper class in the external code. Using stored procedures, however, ensures that multiple external programs have a consistent interface. This is a big deal here, where we have PL/SQL, C++, Perl, and Java all accessing the same tables. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #154,799 by ben_tilly 5/12/04 10:29:19 AM Reply	That I agree with Yes, you need a wrapper. And if you have multiple languages, then the only clean place to wrap it is in the database. However here we only have one development language - Perl. It is unclear to me that there are significant differences for us between choosing to wrap in the database or the application. And there is no question that developers here would find it easier to have that wrapper be in our usual development language. We actually do wrap in the database. I just haven't seen any payoff from doing it there rather than in the application. But it does mean that adding a new field to a table involves editing a lot more files than it would otherwise... Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #154,801 by admin 5/12/04 10:32:32 AM Reply	Why more? How does putting the wrapper in the database cause more edits than a wrapper out of the database? Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #154,804 by ben_tilly 5/12/04 10:43:55 AM Reply	Duplication of information Suppose that I want to add a field. Someone else adds it to the table. I have to then add it to the view, insert procedure, update procedure, and possibly a null procedure. I further add it to the object-relational mapping layer, and then to the unit test. That's 5-6 files. If the object-relational mapping layer could also do the job that the stored procedure does. That would cut out 2-3 edits. Furthermore those are the biggest edits. If I was reworking the system, I think that you could still have stored procedures, and have them be autogenerated from code. However the system already exists, and at present it is easier not to have to rebuild it. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #154,810 by admin 5/12/04 11:06:48 AM Reply	Must depend on what you're doing. If you're always updating the entire row, that's different than DML that does specific things (which is what our system does, for the most part). For situations where we do make changes wholesale, we are moving to a PL/SQL object that handles the inserts/updates/etc., which is autogenerated from the table DDL. We also create a Java proxy for this object using the same script. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #154,798 by mmoffitt 5/12/04 10:27:12 AM Reply	I can't add much to what Scott said. It may be that I favor stored procedures because of familiarity, but I would hasten to add that if you don't use them as your single point of entry, then you may be susceptible to a developer/user using a non-standard tool to go around your single point of entry. bcnu, Mikem If you can read this, you are not the President.
Post #154,802 by ben_tilly 5/12/04 10:34:04 AM Reply	Then see my response to Scott However I find your argument for using stored procedures to be a non sequitor. If you're using stored procedures as your single point of entry, then you are still vulnerable to developers using a non-standard tool to get around them. If you want guaranteed safety that can't be worked around, then you need to use triggers. However once you get into the game of trying to protect yourself from your developers, you have a lot of other problems that follow directly. I'm not saying that in some environments you don't have to take that step. (In fact the larger the group, the more likely it is that it is a good choice to not trust freely.) But it adds a lot of immediate overhead and complexity when you do. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #154,803 by mmoffitt 5/12/04 10:38:38 AM Reply	I did. And how? If you're using stored procedures as your single point of entry, then you are still vulnerable to developers using a non-standard tool to get around them. Only if the crack the db admin's password. If no one has access to the base data and no one has anything but select permissions on views, excluding a crack in the database engine/password for the admin/etc. how am I vulnerable to a developer "getting around" the stored procedures to manipulate the data directly? In short, if you can only manipulate data via the stored procedures you have execute only permissions on, how are you going to get around that? bcnu, Mikem If you can read this, you are not the President.
Post #154,812 by ben_tilly 5/12/04 11:22:16 AM Reply	Ah, good point However how much that accomplishes depends on how much logic you have in the database layer, and who maintains it. At my job, developers are responsible both for application code and stored procedures. Furthermore the stored procedures are responsible for rather little. Keeping update_dm accurate. Making sure that basic required fields are filled. The business logic, authorization logic, and most data validation is kept in the application. Locking down the stored procedures buys you rather little. Reversing this would require having a lot of development work move into PL/SQL. It also requires having a lot of development work move from developers to DBAs. And it requires a lot of tight integration during the development cycle between different people. All moves that are going to significantly hinder productivity. And in the end what it means is that you still have to trust someone, you're just choosing to trust a different set of people. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #154,842 by drewk 5/12/04 1:10:54 PM Reply	Problem is rollback With source code you can easily roll back to a previous revision. Once you break data, you're hosed. It's changing constantly, so you don't want to go back to last night's version. That's why the goal (in this context) of stored precedures is explicitly to make it harder for anyone to change how the application interacts with the DB. Worst case, a delete statement without a where. So yes, you are just trusting a different group of developers. But hopefully the DBAs are more concerned with, and more trained in, maintaining the data than with adding features. === Implicitly condoning stupidity since 2001.
Post #154,848 by ben_tilly 5/12/04 1:44:31 PM Reply	That's only an argument for a wrapper As soon as you have a wrapper that all access goes through, which prevents you from accidentally dealing with all of the data at once, then using that wrapper solves the "delete world" issue. As for DBAs being better equipped to deal with data than development, I agree. Which is why I question making DBAs even more critical path on interactive development than they need to be. Pushing responsibility for more and more features and complex requirements makes them do a job they aren't supposed to be doing, and puts up roadblocks for developers. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]

Welcome to IWETHEY!