April 16, 2009 at 1:19 pm
Christian Buettner (4/16/2009)
Jack Corbett (4/16/2009)
Just a side note. If case is that important to the application then, in my opinion, the application should be upper-casing the string in the first place. Yeah you can do it in the SQL if you need to, but that should be part of the validation.Never thought I would ever have a different opinion than you, but never say never.
I strongly advise to move this task to the DB side. Thats why we have constraints within the DB as well. We don't trust the application by definition 😀
(It could also be that I misunderstood you and you meant to only validate the UPPER case in the DB, but UPPER the data itself in the application)
Bob Hovious (4/16/2009)
Somehow users managed to input some of the customer order numbers as lower case
Looks like they tried and failed, Jack. The counter-argument would be that if that column used an uppercase-only collation (or added UPPER to all procs that do inserts), then it wouldn't matter if the application code was broken. I know scrubbing is more of a load on the SQL server, but enforcing data integrity is a primary role of a database.
I figured I'd hit both of these in one post.
Christian,
I'm not sure we disagree. The point I was trying to make, and obviously not clearly, is that a business rule should be enforced in the application so that you do not "waste" a round-trip to the DB with invalid data. Would I want to put some kind of validation in the database, too? Yes. I'd probably use UPPER in my insert/update procedures. If you aren't using stored procedures, but an ORM tool or ad-hoc SQL, you'd have to use an INSTEAD OF trigger in the database to make sure the data was inserted properly and apply the UPPER, otherwise it still has to be done in the application. Ideally you would use a case-sensitive collation for the column and a check constraint.
Bob,
I missed the part of the post you quoted, which causes us to assume that the application IS supposed to be validating the data. As I said above, that doesn't mean I wouldn't validate in the database as well, mainly to protect the database from some ad hoc update by me or another DBA. Is there an upper case only collation? I know you can do case-sensitive, but didn't know you could require one case.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 16, 2009 at 2:04 pm
Hey Jack:
I think we're on the same page. I would never advocate that front-end editing be abandoned in favor of ONLY bouncing errors back from the server, but I would rather have redundant checks, and an occasional bounce, than allow data in that breaks a business rule. I've seen what happens with DBs created by developers who don't like constraints, or even foreign keys, because they "interfere with flexibility." (No, I don't think you would ever do this.)
Let me clarify the "upper case collation". Bad choice of words on my part. Every time I get in a hurry I lose precision. I assumed (possibly in error) that the order numbers coming in are validated against an orders table. Using a case-sensitive collation would make the lower-case 'cqs' strings fail. (Of course, it might also cause unexpected duplicates, depending on how the applications are written.)
The point was and is, if keeping those lower case values out is critical, steps can and should be taken to prevent it at the db level. Even if it's only changing the insert procedure to store UPPER(custOrderNo) instead of custOrderNo. In this case, it sounds as if it were more of an annoyance than a show-stopper.
Regards,
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 16, 2009 at 2:13 pm
I didn't really think we disagreed, I understood what you meant, and I wanted to make sure that you, and everyone else, understood what I meant. I also was trying to be quick and did not fully explain, or probably think through thoroughly, what I meant.
You and Christian are among the folks I respect on SSC (fortunately a growing group) so I wanted to make sure I clarified. I didn't want you to think I was a fool.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 16, 2009 at 2:34 pm
I'm not one to throw stones... especially where the word "fool" is involved 😉
By the way, the respect is returned.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 17, 2009 at 12:28 am
Jack Corbett (4/16/2009)
You and Christian are among the folks I respect on SSC (fortunately a growing group) so I wanted to make sure I clarified.
Now that makes me feel honored:Wow:
I didn't want you to think I was a fool.
Never would. And just to repeat - this "would have been" the first time I disagreed.
So if you were a fool, then I would be one as well (because I mostly (99,9%) agree with what you say). But now don't get the feeling that you are on the safe side, since I actually may be a fool:-D (and sometimes I definitively am - just cannot get this human behaviour out of myself)
Best Regards,
Chris Büttner
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply