It's always better to be cautious when an RDBMS is involved. Jumping in to "fix" some coder's problem without first doing a lot of sanity checking could cause immense chaos.

Who owns the next step is debatable. I'm guessing that other coders have coped with the existing database behavior. If true, push back on the coder to gain concensus with his peers. That puts the ball in his court, giving him the opportunity to shelve the issue.

To answer your question, you are a very correct DBA who is escalating correctly. You're also a very annoying lazy weasel, at least from the programmer's point of view. I'm of the opinion that programs should be liberal in the input they will handle and strict in their output. He's not being very liberal, is he? How hard is it to test that input and die gracefully if it's ' '? I know Java sucks, but it can't suck that bad, can it?

------
We are the carpenters and bricklayers of the Information Age.

The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

If I were to write code that extracts values from a database, I'd try it to make it as robust as possible. I would do input validation, and I would probably die (or otherwise warn) if the input is incorrect. And then I would complain by the person responsible for the database. Because while my program deals with the illegal value gracefully, another program may not.

But then again, '' might be an acceptable value; as I said, I don't know what the columns represent.

Abigail

If ' ' is not valid data for this database, then the DBA's job is to get rid of it and make sure it can't happen again. That's what Oracle has all of those constraint features for.

I agree with perrin regarding the Oracle constraint features, but I have been in environments where constraints were considered "dirty" because they slowed down the insertion of data into the database.

I know, it sounds odd, but thats the truth. I was on the other end trying to get usable reports and demographics out of this mess.

I think you handled the situation correctly. Its not up to you to harangue the developer's boss.

What might be likely in your case is that there is no written specification, in which case, your approach of escalating the decision is the correct thing to do. I also think that the coding of Sql*Loader scripts, along with triggers and stored procs is a developer role rather than a DBA role. Depending on your company's management structure, I would think that the decision needs to be referred to and owned by the development team and its manager.

In terms of whether you are being weasel or not, I don't think you are - you are just being professional. Welcome to the world of office politics.

This whole scenario you are facing sounds awfully familiar. A year ago, I was on an assignment where I was tasked with design and implementation of the "Interfaces" component of a bank transaction processing system.

Being a skilled scriptor with knowledge of Sql*Loader, I devised templates for the various files that had come from other systems. I was also wearing other hats, including business analyst and system tester.

The realisation that nothing was defined about handling blank values on incoming data fields prompted me to ask the project manager to call a high level meeting to discuss the processing of received input data. Present at this meeting were technical staff responsible for supplying the source data and non-technical users from the business side. I found myself explaining the problem in plain English, and getting agreement, firstly that there was an issue. Secondly, an action plan was formed to get every input field defined. This resulted in a appendix to the functional specification which went through a formal signoff process.

The interfaces component proved to be one of the least problematic parts of this project and application.

--
I'm Not Just Another Perl Hacker

Assuming that records with a "blank" value for this particular field are intended to indicate the abscense of information (ie: null) then you should do your part to try and fix the data, and ensure that the integrity of the system.

If you're designing a new schema, and you tell a developer that they should assume any "blank" string field should be treated the same as null, is like telling a developer that this varchar(2) column is a "boolean", and "if the value looks like the word 'no' (or 'N' or ' N' or 'n ', etc...) then it's 'false', otherwise consider it 'true'."

However...

It sounds like you are talking about an existing system, with a lot of existing data, and a lot of existing code that uses that data ... in which case, any new code should try to "play by the rules" -- whatever those rules are.

I don't really blame this guy for not being happy with your answer, and escalating the issue ... but I hope he did that in addition to making his code more robust, and liberal in what it accepts.

If he just complained to get out of changing his own code, then he sounds like a real Ass.

Question: is the "offending" value legal? Does the database design and business rules allow column values of "single blank" or are they *supposed* to be NULL?

yours,
Michael

// I assume test_val will never be ' '
ResultSet r = select.executeQuery ("SELECT test_val FROM test");
[download]

// My code turns ' ' to NULL now
ResultSet r = select.executeQuery ("SELECT rtrim(test_val) FROM test")
+;
[download]

I hate to say it, but you are most definately a Weasel.

In my time as a DBA, I learned that if I wanted to keep my databases clean and running well, I had to keep a good relationship with the development teams. Often that lead to a lot of dumb questions, but sometimes I got the opportunity to make the database better and teach the developers a few things about how you should be programming with a database.

Now, in this case, I would have asked a few more probing questions, like "Is a value of ' ' invalid everywhere?" or "Have you tried rtrim()?" I would have also checked the original specifications for the control files or the input data files.

What you did will keep the developers from coming to you when they have questions, and more importantly, design questions. Now, in six months when you have to look into why new tables in your system are blowing out extents or help developers with why queries are so slow in your production databases, you can look at this episode here for the reason why.