Sanding our assholes with 150 grit. Slowly. Lovingly.

Is this normal?

I'm new to dev work against a database. All my stuff has been pretty much stuff that runs locally and self-contained.

So there are two things going on I am not sure if they are normal or not. One, access to the database is being constrained to just stored procedures. No inserts, no selects, it's all calling stored procedures. I can do this, it just seems...odd.

The other thing is that any time I suggest doing client side validation of data, it gets shot down. I'm told to let the database deal with it and just handle errors that come back. I don't have a problem with error handling - that's mandatory, but...shouldn't some effort be made to validate it before it ever gets that far?

And yes, I'm a regular poster, so I'd appreciate it if the mods didn't out me. TIA.
Permalink anonymous this time 
August 30th, 2005
There are different philosophies, some of them even valid. If you think of your database as the keeper of data integrity then it makes a certain amount of sense to let the db do the checking.

This clearly sucks for applications though. You want immediate feedback for errors. You don't want to wait.

Yet you don't want to duplicate all the rules at the client side, in the model, and in the database.

What would you do?
Permalink son of parnas 
August 30th, 2005
The SP v. in line SQL is a religious debate, sounds like someone one it at your company.

The second does seem odd, but if you have good DB Developers this should not be a problem.

I use mostly SP's and do data validation on both sides.
Permalink Christopher Hester 
August 30th, 2005
"The SP v. in line SQL is a religious debate"

Well, it does have the potential to give the app dev some sort of data abstraction. I tend to use it as much as I can, although I don't always do it properly, and so get occasionally bitten in the ass by changes to the data model.
Permalink Paulo Caetano 
August 30th, 2005
On stored procs...

This isn't unusual. It's a good way to ensure specific interaction between your app and your data. It also can provide a performance boost and protect your data from attack. (But poorly written stored procs can end up just as slow and vulnerable.)

On the validation thing...

That is weird. It may be a very good idea to have constraints at the database, but IMO data should be validated before it even goes there.

Some crappy programmers implement bad, messy validation code that is hacked into the user interface, doesn't really work anyway, and is impossible to change without redeployint the entire app. Your people may have had experience with such programmers. They may be concerned that a constraint may need to be changed at some point and don't want to have to deal with a new version of the app to handle it.

So if I were you, I would insist on having some validation at the application level, but I would implement it in a validation layer that is easy to maintain and deploy. (Maybe even an auto update by your app accessing your network.)
Permalink Jeff Barton 
August 30th, 2005
Constraining all interactions with the database to a select set of stored procedures can be an excellent design in some scenarios. I've found that the most battle worn groups tend towards this design, as it allows for abstracting the underlying schema from middleware and front-end logic, and is a great aspect-oriented intercept location. I'm a fan of that design methodology.

Is it normal? Nothing is normal in this industry, and everything depends upon specifics of the environment, the team, and the design philosophies. It sounds like your team is very database centric (which is valid from the perspective that the database is usually the longest living and most reusable element of projects).

Regarding client side validation, there is a cost of developing validation code in different technologies and locations, which is doubly so because you then need to synchronize the two validation code sets (which will likely diverge as time goes on).
Permalink Dennis Forbes 
August 30th, 2005
I see no problem with encapsulating all of your access in stored procedures. This is not uncommon.

Also, 99.9% of everyone who says they're a regular who doesn't want to get 'outed' by the mods is completely full of shit about being a regular.
Permalink muppet 
August 30th, 2005
On the validation point:

I've realized there are some exceptions that I take all the time. Sometimes validating your data IS best done at the database. Especially when the validation depends on other data already in the database (keys, related fields, dependancies). It can be cheaper perfomance-wise to hand it all over to a stored proc and see what you get back rather than have your app fetch the data, check it, and then commit.

So, it depends on the context I guess.
Permalink Jeff Barton 
August 30th, 2005
Personally, I'd go for client side validation simply because it'd easier to have business logic in code than it is in stored procedures.

Code was designed for this sort of thing; stored procedures were tacked on to databases for this sort of thing. Guess which is better?

But whatever, both are possible.
Permalink Colm O'Connor 
August 30th, 2005
It depends on the likelihood of errors as well - e.g. if it's the sort of entry that the user seldom gets wrong, then the centralization of the validation code (because you have to have it in your data layer, be that a component or a stored procedure, regardless) might be a worthwhile compromise.

On the flip side I've seen a system where virtually everything happened in the database, so the user would fill out a giant form and then get some abstract stored procedure generated error message telling them that some piece of input was wrong. The client app just knew there was an error of some sort, so it couldn't highlight the errant field or anything like that, or make sure that they enter the phone number as (xxx)-xxx-xxxx before wasting time submitting.
Permalink Dennis Forbes 
August 30th, 2005
> stored procedures were tacked on to databases for this sort of thing

Please ignore this statement. It is dumb.

And regarding business logic. Constraints don't necessarily constitute business logic. They are a way of ensuring a schema for you data.
Permalink Jeff Barton 
August 30th, 2005
Debatable issue. Some folks think communication should only go over SPs, and they have their reasons (security, comm overhead, speed). I think their reasons are very inadequate, but that's their business, luckily I don't need to do this and the one project where I did, that project was cancelled.

About data checks being relegated totally to the database layer.. well that sucks. Sure it makes sense in some respect, namely you don't replicate the logic. But. It's not at all easy to "handle errors that come back". Say you are developing a multilingual system (or just non-English). What should you do with an error like "ORA-02291: integrity constraint (RHM.SYS_C00127894) violated -
parent key not found". You can't show this do the user, who might not even speak English, less understand what the heck is wrong. And you cannot determine what exactly went wrong yourself either, unless you hardcode ALL possible DB data check errors.
Permalink Mr. Wumpus 
August 30th, 2005
Also, validating inputs solely on the database side may be less secure. If everything is being run through stored procedures (and the interface to the stored procs is strictly parameterized, rather than set up as an 'exec ...' string that concatenates the parameters into the string), this may be mitigated to some extent. However, whenever I see unvalidated inputs being sent to SQL, as parameters or otherwise, I get kind of worried about SQL injection.

However, I can see the value of the "do everything on the server" side ... this way, you control your business logic in a central location, rather than scattered all over the many bits of the user interface. If the rules you use for validating input are continually changing due to weird business conditions, this may actually be a good idea. On the other (third) hand, relatively simple items like phone-number validation can be easily set up in a module or include file, since they tend not to change all that much.

Of course, really harcore SQL coders are invited to take issue with my point-of-view, since I'm not part of their elite fraternity. ;)
Permalink Snark 
August 30th, 2005
"Personally, I'd go for client side validation simply because it'd easier to have business logic in code than it is in stored procedures."

So what happens when the mainframe guys need to start interacting with your database and they start inserting bogus data into it? Ultimately, whatever the layer, there needs to be one universal (regardless of client) "public" entrypoint to the database, be it a stored procedure layer, or a set of web services, or some COM components, where the rules are without exception enforced. Most teams have found that stored procedures at the database level are the most universally usable across technologies and platforms. Database integrity (both through database natives like foreign keys and column constraints, as well as programmatic limits such as ensuring that a field follows a certain form) are critical for a database to not descend to garbage. I've seen too many projects where the data is just basically worthless trash because these sorts of constraints weren't enforced.

In any case, I don't think the OP is against these sort of programmatic constraints at the database level, but rather they like to couple that with client validation (which doesn't actually help data integrity at all, given that the same checks will happen at the database, but rather gives the user a more timely and intuitive experience.
Permalink Dennis Forbes 
August 30th, 2005
>>stored procedures were tacked on to databases for this
>>sort of thing
>Please ignore this statement. It is dumb.

It is based on:

- Trying to manipulate strings in stored procs (either very difficult or impossible depending upon what you're trying to do).
- Trying to debug a stored procedure.
- Trying to do simple stuff like a for loop, which requires you to hack one together out of a while loop.

Stored procedures as a language are about 5 years behind (maybe more) regular languages like C# or Java.
Permalink Colm O'Connor 
August 30th, 2005
Oracle does allow for Java stored procedures, while the upcoming SQL Server 2005 supports .NET natives (stored procedures, types, UDFs).

You are absolutely right on the money that PLSQL and T-SQL are just terribly underpowered, and there's a lot of TERRIBLE code out there to deal with this gap.
Permalink Dennis Forbes 
August 30th, 2005
"In any case, I don't think the OP is against these sort of programmatic constraints at the database level, but rather they like to couple that with client validation (which doesn't actually help data integrity at all, given that the same checks will happen at the database, but rather gives the user a more timely and intuitive experience."

Exactly. Combined with the problem of figuring out exactly which pieces of data were bogus based on the error thrown by the database, and it's just terribly inconvenient. I'm certainly not saying to have *no* data validation or constraints in the database - that would be retarded, and a great way to fill the database with junk when you start accessing it from other sources, as it inevitably will - but some of it can be client side.

Any mod here can tell you that I'm that one in a thousand, muppet. Would someone who isn't a regular know that the mods could tell you that?
Permalink anonymous this time 
August 30th, 2005
>So what happens when the mainframe guys need to start
>interacting with your database and they start inserting
>bogus data into it?

Mainframe guy? I don't think I've ever come across one of those species.
Permalink Colm O'Connor 
August 30th, 2005
Oh, and as far as problems caused by letting somebody else screw with the data, there is a simple solution - don't let them screw with the data.

If a lot of applications need to talk to the data then it may well be a smart idea to use web services instead.
Permalink Colm O'Connor 
August 30th, 2005
> Stored procedures as a language are about 5 years behind (maybe more) regular languages like C# or Java.

Colm, I don't even know if I should respond to this. Stored Procedures are simply *stored procedures* that get a little bit of special treatment by the engine (remembering execution plan, etc). They are not a "regular language". Basically they allow you to encapsulate the interface to the data. There are other ways to do this, as Dennis stated, but stored procs are a very good one.

I think I know where you are coming from, and it's very script kiddie. You just obviously don't know what you are talking about here, so do yourself a favor and be quiet.
Permalink Jeff Barton 
August 30th, 2005
re: only stored procedures. this works fine, but can get bogged down if your database people don't know what they're doing.

having validations all in one place makes sense if you're not worried about performance issues... another problem with having it all done in the db is that you might end up repeatedly writing a lot of code... it isn't weird to do it this way, it's just not the most efficient.
Permalink Kenny 
August 30th, 2005
>Colm, I don't even know if I should respond to this.
>Stored Procedures are simply *stored procedures*
>that get a little bit of special treatment by the engine

Precisely. This is what I meant by 'tacked on'.

>(remembering execution plan, etc). They are not a
>"regular language".

They're turing complete. Which is as good a definition of 'regular language' as any.

>Basically they allow you to encapsulate the interface to
>the data. There are other ways to do this, as Dennis
>stated, but stored procs are a very good one.

Basically they allow you to do all sorts of things. Many of them, including encapsulation of data, for example - they are very bad at.

I still use them but only in limited circumstances.

>I think I know where you are coming from, and it's very >script kiddie.

I would expect somebody who codes all day in stored procedures to be angry with life so I won't take that insult to heart :)
Permalink Colm O'Connor 
August 30th, 2005
Oh, and you might want to read this:

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
Permalink Colm O'Connor 
August 30th, 2005
> I would expect somebody who codes all day in stored procedures to be angry with life so I won't take that insult to heart :)

Actually I'm a C# guy, but that's irrelevant. More importantly, I know what I'm talking about. ;-)
Permalink Jeff Barton 
August 30th, 2005
Well, the idea that all applications that talk to a database will have identical validation constraints is dumb. By demanding that ALL validation happen in the database, the database must enforce every kind of validation that any kind of application might have for any of the data. And some of these constraints may even conflict between applications.

So, if the database is to be used for many applications, requiring all validation to happen in the database will really hamper reuse of that database in new applications.
Permalink Jim Rankin 
August 30th, 2005
Jim,

The constraints are based on the data that you're capturing. I'm not really seeing how disparate applications change the validation of specific points of data.
Permalink Dennis Forbes 
August 30th, 2005
I've met Rob Howard. He's a smart guy. He also knows what he's talking about.

First of all, a little qualification: If you are doing RAD on little apps that will be rolled out internal to your organization, or you make a little web site, using dynamically generated sql exclusively can be very liberating.

However, if you are building any kind of distrubeted system, dynamic sql can end up being a big, big headache. Period. If you don't get it, I don't care.
Permalink Jeff Barton 
August 30th, 2005
I'm confused - who is Rob Howard, and why does he matter to this discussion? I'm not being flippant, I'm just not sure if maybe that was intended for a different thread or something.
Permalink Dennis Forbes 
August 30th, 2005
Colm linked to an blog entry written by some guy as a counter to Rob Howard's advice on stored procedures.

Rob Howard used to be a Program Manager on the Asp.NET team. He was one of the principle authors of the Asp.NET Forums. He left Microsoft last year to found Telligent Systems, but I believe he still speaks for them at conferences and such.
Permalink Jeff Barton 
August 30th, 2005
Aha! Okay gotcha.
Permalink Dennis Forbes 
August 30th, 2005
Yes, not to be confused with Ron Howard, who, as we all know, advocates web services as a data interface. That looney should stick to movies.
Permalink Jeff Barton 
August 30th, 2005
Let's say the database represents color schemes for various rooms of your house.

One application just allows people to choose whatever godawful combinations they want. Another application has an AI based taste module that actively forbids you from choosing a color combination that clashes with your personality type, or the principles of feng shui.

Do you really want to build the feng shui AI into your stored procedures?
Permalink Jim Rankin 
August 30th, 2005
But there should be some sort of constraint to actual colors, or people will try to paint the house "ferret", "climb", "43tgadrq4311112", and "drop table".
Permalink anonymous this time 
August 30th, 2005
Aha, so now we're only arguing about the price?

As in -- once you've admitted you want SOME SP checking of constraints, you've opened the door to SP. Now we need to have a conversation about HOW MUCH SP, or how detailed the constraints need to be, our should be.
Permalink AllanL5 
August 30th, 2005
No Jim, and that isn't what we're advocating. The base set of constraints at the data layer is there to enforce a schema for that data. So you know if it went in, it's of proper form. It makes sure you don't try to put a 747 in your living room.

(Do you create all of the columns in your tables as large text fields, just in case? See where I'm going...)
Permalink Jeff Barton 
August 30th, 2005
Talking to me, or someone else? Sorry, confused.

I already said that I have no objection to having the database do some of this.
Permalink anonymous this time 
August 30th, 2005
> Now we need to have a conversation about HOW MUCH SP, or how detailed the constraints need to be, our should be.

This stuff really, really depends on the application and context. So, it's hard to debate without a well known use case.
Permalink Jeff Barton 
August 30th, 2005
"The base set of constraints at the data layer is there to enforce a schema for that data."

Key words there being "base set". More sophisticated validation may need to be done in the application (such as the always handy feng shui A.I.).

So, yes, I suppose now we're down to drawing the actual line where validation becomes too sophisticated to put into stored procedures. And also the impact on the user experience of having to take a trip to the database for every instance of validation.
Permalink Jim Rankin 
August 30th, 2005
I read that blog entry you linked, Colm, and I am left with the impression that the author basically put up a big show of smoke and mirrors to support their case. The author does, rightly, correct some falsehoods levied against dynamic SQL, but I see little to actually support their argument (negating opposition to a position doesn't in itself support an argument). There is a place for both, without an ounce of doubt, but just to reply to that blog posting a bit-

a) How is hundreds of dynamic SQL snippets hidden throughout a multitude of applications better than hundreds of stored procedures? Simply saying that dynamic SQL frees one from stored procedures is absurd, because you're basically just encapsulating the stored procedures in your code.

b) SQL is a set language. T-SQL is a bastardized and expanded procedural language built around SQL. There is quite a disconnect between the two.

c) Security in any real database system is a world more involved than CRUD rights in specific tables (although even then most people manage to screw up CRUD rights, which is why it's nice to simply forbid any direct table access). For instance, in the real world with real projects user 745 wants to update appointment record 948, a record owned by user 484. This is real world security, and in the real world the user has update rights on the table (they have to update their own records, right?), but programmatically you don't want update rights to be allowed on that specific record. Voila, you need centralized logic to enforce these constraints. Either you're using a "stored procedure somewhere else" in the form of a data-tier component, or you're using a stored procedure.

d) The "CRUD" model is broken anyways, and you don't need every user application to know exactly how you've normalized the database. That, again, is the beauty of hiding all access behind stored procedures.
Permalink Dennis Forbes 
August 30th, 2005
And anyways I think the term "dynamic SQL" is inane - people use it as if they're dynamically adapting to the database, creating SQL on the fly. In reality in most situations it's static SQL hardcoded into an application (which itself can be a major pain in the ass when you want to redesign the database - a dozen apps each directly tied to a specific schema) - it's a stored procedure in a less centralized place.
Permalink Dennis Forbes 
August 30th, 2005
Dennis I think they are coming from a different place. They don't see whey many distributed applications of different forms might need access to a database. Creating a class to handle the SQL generation is all the abstraction the think they need.

And the guys arguments about security being a dead horse, and simply using views to manage security is a complete joke. Views cannot replace SPs in terms of security. And besides they would have to manage their security-views just like one would have to manage SPs. And then they would have to manage their generators on top of that.
Permalink Jeff Barton 
August 30th, 2005
One question -
When there is a validation error (string too long, value out of range, bad content, etc), how good is the feedback from the SP? Is it something that can be shown to the user (or possibly translated into something that can be shown)? Or is it a message along the lines of "Your data is fubared. Fix it and resubmit" that is of no use?
Permalink example 
August 30th, 2005
Throwing an error from SQL Server involves throwing a text street, and an error code. Most teams have their own list of error codes and what they mean in their apps.
Permalink Dennis Forbes 
August 30th, 2005
ermmm, street=string. I think I have some corrupted pointers in my brain.
Permalink Dennis Forbes 
August 30th, 2005
IMHO, certain validations belong on the user interface and others within the stored procedures. For example, if a user is expected to enter their birthdate and enters the current year instead of their birth year, the interface should be capable of warning them. No need for a round trip to the database for that.

Or, if there's a date range, the interface should ensure that the beginning date is prior to the end date. This is all stuff that can be encapsulated in some kind of error handling layer right below the IU.

The stored procedures, table constraints and so on should be used for application-specific validation.
Permalink Dana 
August 30th, 2005
I would prefer to have the validation in all tiers -- it makes for a better user experience to get the error earlier than later.

Making the databaase continually roll back transactions because of validation errors may cause extra load on the database server. Can anyone of you DBA types confirm/deny this?
Permalink sam 
August 30th, 2005
I may have missed it earlier, but did it really take 40+ posts for anyone to suggest putting validation in BOTH the database AND the UI?

Damn.

I thought that was patently obvious - you put validation that protects the data (like data formats, fundamental business rules, foreign keys, unique constraints, etc) in the database, you put your more specific business rules (ship date must be at least two days after order date, people from this department can only see that data, etc) in the business layer, and you put validation for user convenience (like popups for formatting errors, spell checking, lookups for fixed lists, etc) in the UI.

Does that not logically follow?

Philo
Permalink Philo 
August 30th, 2005
No Philo, it didn't. And next time, why don't you read through the thread a little. You might not come off so pompous. ;-)
Permalink Jeff Barton 
August 30th, 2005
I have a reputation to uphold. *And* I'm lazy.

Philo
Permalink Philo 
August 30th, 2005
Isn't this thread off topic for "Off Topic"?
Permalink  
August 31st, 2005
>How is hundreds of dynamic SQL snippets hidden throughout a
>multitude of applications better than hundreds of stored
>procedures?

It was my impression that dynamic SQL wasn't SQL that was scattered around an application in bits and pieces, but rather SQL generated with a tool such as LLBLGen.
Permalink Colm O'Connor 
August 31st, 2005
That's the case for some people, Colm, but in the debate in general people endlessly use the phrase Dynamic SQL to refer to a non-stored procedure approach.
Permalink Dennis Forbes 
August 31st, 2005
>> Throwing an error from SQL Server involves throwing a text string, and an error code. Most teams have their own list of error codes and what they mean in their apps. <<

Absolutely. But what I was wondering was how good their implementation was. A good implementation will tell you "Customer Account must be 12 digits". A bad implementation will return to you "Data incorrect".

The latter, while technically correct, is of no use to anyone.
Permalink example 
August 31st, 2005

This topic was orginally posted to the off-topic forum of the
Joel on Software discussion board.

Other topics: August, 2005 Other topics: August, 2005 Recent topics Recent topics