An ‘Ah HAH!’ moment

I don’t talk much about what I do. Basically, I’m an application programmer. I write user-level apps that help folks do a specific task – that task generally being something non-geeky. (as opposed to system programmers who write things for other programs to use, or the like).

Today I was hung up on an annoying SQL problem where a task to update a ‘state’ table with all the registration, badging, checkedin information, etc – was acting wonky. It would look like it would work, then later it would show the wrong data.

It turns out a second instantiation of the call would -alter- the results set up by the first. So, one run would do the right thing. A second run on a different event would alter the data from the first run. The issue was I wasn’t constraining the update enough to limit the scope of the changes, so it was running roughshod over previously existing data.

The corrected query:

153     sql = "UPDATE reg_state,reg_history SET " +
154             "reg_state.state_badged = 1 WHERE " +
155             "state_registered=1 AND state_rid=hist_rid AND hist_actcode='BADGED' AND " +
156             "hist_cid=" + cid + " AND " +
157             "hist_rid=" + rid ;

This problem has been banging around in my head for 2 days now, I’m ecstatic that I finally nailed it down. Now I can go to lunch.

This brief snapshot into Dave’s Brain has been brought to you by…