The Gap

Less techie-stuff today.

Here's a story. Many years ago a very smart young man went to work in IT. Naturally, he developed software in the language of the future (PL/1, with a bit of assembler thrown in). He worked with the computing gods, and aspired to become one.

But along the way, something happened. Maybe he just didn't have divine attributes, maybe he got bored or outsourced or decided he wanted a life. So he changed his aspirations. Rather than being one of the gods of computing Olympus, he would manage them.

He was good at management. He was hard-working and politically savvy. Over time, he worked his way up from Junior Assistant Lackey Manager to Senior Assistant Lackey. Pretty soon, he found himself in a Vice President's office.

But he had one problem. He had no clue what his employee's did. He didn't remember much about computers, but he knew terms like "libraries" and "frames" and maybe even remembered a bit of JCL. They were talking about EJB's and Ajax (wasn't that a cleaning product?) and SSIS and PCI and data warehouses.

Conceptually, he kind of got it, being a smart fellow. But the details were all a mixture of magic words and flowcharts. And he couldn't understand why IT had gotten so expensive or why it took so long to get things done. Why not just toss together some JCL and run the silly thing? Why did everything take 4 months?

Enter the vendors. They walk into his office with really spiffy demos. It's not a flow chart, it's something he can see. And it's pretty. The reports are all graphical and the demo shows how easy they are to use. And time to market? Oh, the vendor insists that they have a crack consulting team that can deliver on the fly. Why, as part of the demo, the vendor even builds a really cool report in minutes.

Our hero is hooked. He signs on and spends a zillion dollars.

Then he gets the product to his IT staff and they tell him that they can do the same work already with other tools. Moreover, they insist that the complexity isn't producing the pretty report, but aggregating the data from the 16 different heterogeneous data stores they have, manipulating it to make it actually match, then (this is the really hard part), making it mean something. It's easy to sum a table column. It's a lot harder to know what the data in the column means or what the rules are around summing it.

The problem is that there is a gap between the people who work in IT and the folks who manage it. The vendors and consultants make a ton of money trying to fill that gap. Each year, there's a bevy of products aimed at it. They each hold the promise of being able to close the gap -- and turn the IT systems from a collection of disparate pieces into a contiguous whole. Only, in reality, the vendors don't really care much if they work. Oh, I'm not saying they are sloppy or bad people - although some are, I'm sure. But I'm just saying that in the end, the vendors have very little incentive to really close the gap, since that's basically how they make their living. Without the gap, consulting services have to shift their work to being little more than resource augmentation providers. And they have no real competitive edge. Today, if a consultant can get certified in the latest hot product, they can bill themselves as experts. If the gap closes, that will hold much less value. The vendors are in the same position. If the gap closes, then the only way for them to make a living would be to be truly creative with their products or to build tools that actually provide value for the developers. Don't get me wrong, some companies do that. Microsoft has a bunch of them. Oracle does too, although they have a lot of "gap-closers" too. Sun has a few, although they've been hamstrung by financial issues. But other companies would have to pony up and compete with the IBMs of the world.

And it's so much easier to simply pull together a good demo.

CORRECTION

My mistake.

Some days I just struggle to tie my own shoes.

In my last blog post, I talked about how to create an audit trigger. And I made a mistake that I found badly when I tried to run an update.

Here's the issue.

SET @OldValue = (SELECT B FROM deleted);

SET @NewValue = (SELECT B FROM inserted);

this is great if there's exactly one row in the tables. This means that if you're updating only one row at a time, everything's dandy. But if you do an update that will impact more than one row, this will fail and crash hard.

How do you get around it?

Basically, you need to open a cursor for each row in the update tables, then step through the cursor one row at a time.

So in this case, first you would

build out a cursor

DECLARE cur_inserted CURSOR FOR

SELECT B, A FROM inserted

OPEN cur_inserted

FETCH NEXT FROM cur_inserted INTO @B, @A

WHILE @@FETCH_STATUS = 0

BEGIN

SET @B= (SELECT B FROM deleted WHERE A = @A);

INSERT INTO MyAudit (Value, AuditType)

Select B, 'OLD' from deleted WHERE A=@A; (assuming A is a unique key).

Basically, it does the same thing, but uses a cursor to walk through the modified rows, then pulls the corresponding values from deleted that correspond to the cursor row. In this case, it will pull the "old" values into the log table while walking through the "new" ones. Of course, you can still add conditional statements on this as before.

but this will actually work for mass updates.

Sorry for the confusion.

audit tracking on Sql Server

Here's the challenge. You've got a database. In that database, you've got a table (say T). In that table, there are some columns -- say A, B, & C. If column B changes, you want to log that change somewhere.

You've got a couple issues. First, you don't care about columns A or C. If they change, you don't want to log the change. So how do you know when it's column B that changes?

Next, you only want to log the change if it actually *changes*. So if the value of B is x and someone does "UPDATE T SET B='x'", you don't want to log that, since it's not really changing the value ,but just setting it to what it already was.

In Sql Server, you can do this easily with a table trigger, but how you do it is a bit whacked.

First you need to create the trigger, of course. Then we'll need 2 variables -- one for the old value and one for the new:

CREATE TRIGGER somename

ON T

AFTER UPDATE

AS

BEGIN

DECLARE @OldValue integer

DECLARE @NewValue integer

Note that the trigger actually fires after the update. I did this because, in this case, the trigger need not be transactional -- I don't want to rollback the change just because I can't audit it. There may be cases where this isn't true, of course, but it will due for what I need.

Next, you'll need to use this: COLUMNS_UPDATED()

COLUMNS_UPDATED() will show you the columns that got updated by the update statement that fired the trigger, but the weird part is that it returns it as a bit mask. If you don't know what a bit mask is and don't want to learn, you can use a built in function to help. The column I'm looking for (B) has a corresponding bit in the bitmask. You can look to see if it gets set, but the way bit masks work, it's not quite that simple. Changing columns A or C at the same time as B, will create a different bit mask value. So it's not as simple as " if COLUMNS_UPDATED() == x20000" or something.

Because it's been like a hundred years since I've had to bit shift, I' m going to suggest using Microsoft's built in function to look for this value. It is:

sys.fn_IsBitSetInBitmask()

So, the syntax:

IF (sys.fn_IsBitSetInBitmask(COLUMNS_UPDATED(), 2) >0 )

will return true if the 2nd column (in this case "B") has been set, regardless of whether A or C have.

But what it doesn't tell is if the value was actually *changed*. This if statement will return true anytime the column is "Set" in an update statement.

But it's pretty easy to check, although it takes a few extra lines:

SET @OldValue = (SELECT B FROM deleted);

SET @NewValue = (SELECT B FROM inserted);

"deleted" and "inserted" are in-memory tables that hold the values of the update before and after the update itself. "Inserted" holds the new values, "deleted" holds the old ones. Since it's in memory, it's pretty quick. In my case, updates to the table will only happen one row at a time. If you're talking about mass-updates, you need to be careful that performance won't suffer. On the other hand, it is an in-memory query, so the numbers have to get pretty big before that's an issue. If you're doing such bulk transactions, you probably have other issues anyway.

With this, the old and new values can just be compared:

IF (@OldDisp <> @NewDisp)

To insert them into the audit table, just pull the values from either "inserted", "deleted" or both, depending on what it is you're looking to audit.

INSERT INTO MyAudit (Value, AuditType)

Select B, 'OLD' from deleted;

or

INSERT INTO yAudit (Value, AuditType)

Select B, 'NEW' from deleted;

for the old and new values.

That's pretty much it.

You'll want to add some error handling. And be careful about this. If the error gets thrown, it probably means that you can't insert into a database table. If that's the case, you may not be able to insert into any (out of space or whatever). So you may want to log the issue to system logs

(EXEC XP_LOGEVENT @ErrorNumber, @ErrorMessage, @ErrorSeverity )

or RAISE the error.

What's cool about this, is that it's database centric. If anyone modifies anything, you have it -- whether it's a service that does it, a scheduled task, or even an individual user with database privs. You can pull off the user id that initiated the update, if that's helpful, but keep in mind that the scheduled tasks and services probably all have a shared account.

--kevin

the excel syndrome

What is the excel syndrome?

It's an executive manager who uses Excel and is convinced he "knows" computers. You see it a lot now. And it's one of the big things that's changed in the last few years. Ten years ago, if you said "e-commerce" everyone's eyes glazed over and they said "just make something work".

Today, all the execs use Excel and buy things from Amazon. So when an IT professional says : "setting up this database correctly and adding accounts with the right security and tables with the right schemas will take... er... 2 weeks", the exec says "WHAT!?? that's insane! Why... I can do it in Excel in 10 minutes". Then he (or sometimes she, but that's pretty rare), slashes the project estimate.

Oh, I know I'm exaggerating for emphasis. But the syndrome exists. It really does. My CEO just said today that we weren't delivering fast enough. Fast enough compared to ... what? How does he have a feel for how long it *should* take?

I started noticing this a few years ago. Non-IT people started to think they understood IT well enough to second guess their IT staff. I see it among IT people too -- especially those who have worked in small environments and with older, more limited systems. Sure, it may be possible to export a file from DBase from a live system with a simple real-time query. But.. um... DBase isn't relational? Oh, and there's that little thing called "security"? And the DBase database that only held 2MB of data is a slightly different creature than a SqlServer database that holds 200GB.

It is part of a trend in the industry. IT has become big business. Consulting companies and vendors are big business. As the money that goes into IT grows, more and more people are trying to get their little part of it. Naturally, that includes the execs and the bean counters.

I'm not sure where IT will go in the future, but it's days of free-wheeling are done, I think. It will become more and more driven by people who have an opinion on how the money is being spent, even if they don't know what their opinion means. It's kind of like those Windows7 commercials where the people are saying the invented Windows7, because they sent an email to Microsoft telling them to make computers better.

There are a lot of those people.....

data access

there's lots of ways to do data access, but here's something I've been doing that seems kinda cool.

First, create a namespace (DataObjects, in my case) .

Next, a BaseDataObject this is the parent that all the data accessors will inherit from.

In the BaseDataObject go the common things. For example, I have a public property called "DatabaseName". Using this, the code will query for the correct connection string parameters when needed.

The BaseDataObject also owns the actual connection. This is interesting. The Connection goes into a thread static collection of connections (which is hard to say quickly :D ). When the connection is needed, the collection is searched. If the connection isn't there, it gets created and inserted for the life of the thread. Took a while to nail this, but this means that the connections are correctly cached by database name for the life of the thread and no longer.

In order for this to work, the connection is actually of type IDbConnection. Anything else will bind the data object to one database type.

All of the methods of the BaseDataObject follow that pattern -- each uses IDbCommand, IDataReader and the like.

The simplest subclasses only override the connection itself. For example, I've got a MySqlDataObject that allows connection to a MySql database. It overrides the "connectToDatabase" method to secretly append a mySql designation to the DatabaseName. This means the connection string resolution returns a MySql compatible connection string, not a SqlServer one. In addition, when it creates the database connection, it creates a "MySqlConnection", which gets implicitly cast upward to a IDBConnection on the return.

The more complex subclasses (the SqlServer one, for example) have some additional functionality. For example, I've added an ExecuteNonQueryWithParameters method to the BaseDataObject, which accepts a Dictionary of key-value parameters and then uses this to populate the Sql Statement. The BaseDataObject resolves all this by looping through the dictionary and ingloriously building the sql string. The SqlDataObject overrides this to use the Parameters.AddWithValue method on the SqlCommand, since this is available.

In addition, I've occasionally further subclassed the child data objects to allow versions of the objects that toss exceptions back to the caller and versions that don't, but log the exceptions and return a null result.

To wrap this all up, I added an object factory to the DataObjects namespace. Pass in the DatabaseName, and you get an appropriate subclass of BaseDataObject, based on whatever business rules are applied to decide this. The returning object is basically a live connection ready to be used.

So far, this has worked pretty well. My big frustration is the lookup on the database name -> connection string. I have been using a Microsoft "Settings" object for that, but, frankly, it sucks and doesn't provide any real benefit. The good news is that, since this is hidden by the objects in the namespace, I can change it anytime without issue. Moreover, the structure allows me to reference databases as objects. Not only do I like that, but the polymorphism and use of object factories allows that I can move data from one database to another with no changes to code.

Just thought it was cool enough to post.

today

just something to share that doesn't mean anything.

I spent about 3 hours today debugging a workflow. I was furious at Microsoft.

The issue was that the AutoResetEvent wasn't getting set, but timing out. So, after the flow was complete, the "oncomplete" delegate would fire, but then sit there and wait for the AutoResetEvent to timeout.

I checked the code. I checked the flow. I debugged. I searched the web. I was typing up a post on MSDN, when I thought I'd better double check my logic.

My flow was being called from a subclass of a util class that I had written. After some additional hair pulling, I realized that the subclass was hiding the AutoResetEvent with a variable named the same. What was really stupid was that in C#, you have to create the variable in the subclass with the "new" key word:

private new AutoResetEvent waitHandle = new AutoResetEvent(false);

Geeze. SO I *intentionally* was hiding the variable that I needed then wondering why it wasn't there.

My official thought: "DOH!"

So here's my takeaway. It's getting easier to build complex functionality with the new tools. They do a ton for you. Awesome.

But they hide things. Nothing in my debugging would show me which variable was getting updated or how.

I'm not blaming Microsoft. Well.. ok... I am, simply because I need someone to blame.

But it's a challenge that developers face now. Devs have always had to use code we didn't control. But the degree to which its needed is getting much higher. Whether we're using EJB or Windows something Foundation, you just have no clue what the code is really doing. If you make a simple mistake setting something up -- a hidden variable, a mistaken delegate, a copy/paste error in a config file -- can cost days in developer time to find. In the end, it could even be a bug in the toolset or a mistake in the docs.

It gives me pause for thought. I really wonder if this is the best way to approach things.

--kevin

Workflow and Exceptions

This guys says lots of cool, good, happy things about handing exceptions in Windows Workflow.

http://www.microsoft.com/uk/msdn/nuggets/nugget/60/Windows-Workflow-Foundation-Exceptions.aspx

AND he says it with a cool accent!

The basics are pretty simple, but there are some weird gotchas. Like you have to have a different Exception Handler for each kind of exception you want to handle, not just different code for each.

There are a couple additional things to keep in mind:

first, Studio (2008, anyway) doesn't seem to recognize that you have a workflow exception handler in place. So when you run the code in debug mode, it will stop at the exception and tell you that it's unhanded. If you hit F5, it'll will step into the handler. Seems like a disconnect between the development tool and the workflow foundation.

There are a bunch of things you can do with the exceptions, of course. But my real desire is usually to trap them, log them, then return them gracefully to the calling process.

The second thing to be aware of is how to do this. Lots of options, but if you create a public, read-only property to your workflows called, say "Exceptions", and defined as , say, a custom collection (or generic) to hold the exceptions, these will get auto-magically returned to the caller. However, they are cleverly hidden so that it's not obvious where they are.

Upon completion of the workflow, all the publics get serialized and passed as a parameter to the delegates registered for the workflowRuntime.WorkflowCompleted, workflowRuntime.WorkflowTerminated, workflowRuntime.WorkflowAborted events.

So somewhere in the code, there should be a something like this:

workflowRuntime.WorkflowCompleted += OnWorkflowCompleted;

OnWorkflowCompleted is the delegate defined by:

protected void OnWorkflowCompleted(object sender, WorkflowCompletedEventArgs e)

Typically, here is where you will set the wait handle:

waitHandle.Set();

where waitHandle is defined as :

protected AutoResetEvent waitHandle = new AutoResetEvent(false);

But the 2nd parameter (WorkflowCompletedEventArgs e) is really just a serialized representation of the workflow itself. So all the public properties are available as properties.

So, in my case, I just have my exception handlers add the exception to the public collection owned by the flow. This is really cool for capturing all the exceptions in one place. For example, say I'm persisting some data elements. It could be that many of the data elements are bad. If I throw an unhandled exception for the first one, I never see the rest until the data gets scrubbed and reposted. Then I bomb on the 2nd one. But with this approach, I can just add the exceptions to the output collection and de-serialize in the calling code.