Chop off their heads

He looked cautiously around, examining every little detail in the room. Each step he took was slow and calculating. His eyes stared at the space above the floor, as if he could see the passing of his quarry through that very space. Raising his right arm, he negligently rested the head of a blood-stained axe upon his shoulder.

He stood still. A drop of blood dripped from the axe for what seemed an eternity, and splattered the floor. He turned, and light glinted off the axe where it wasn’t bloody. A gasp escaped from the closet. He grinned, and shrugged the axe onto both his hands.

He’s cold-hearted. He’s cruel. He’s a murderer.

Axe on chopping block
[image by Geoffery Holman]

No, I’m not writing a horror story. That wasn’t quite scary enough. Although what I’m going to tell you is frightening enough… It was a dark and stormy night… uh…

It was some data patching task. I was to delete some data from a table. I entered the SQL statement

delete from ImportantTable

and promptly executed that statement without providing the where clause!

Oh in the name of all that is good! My heart was pounding like I just finished running a marathon. My hands started sweating. I felt a heat spreading from my neck to my head. “What have I done!”

Luckily, I was using TOAD, a user interface for accessing Oracle databases. And in Oracle, as much as I hate it, any changes to database tables are not committed till you specify it so. There’s a commit button in TOAD. You can also type in “commit” and execute that.

So what I did was roll the changes back, with a handy “rollback” button. Whew…

Like I said before, I prefer any SQL statement I execute to be, you know, really executed. I’ve had experiences where I was debugging my web application and was wondering why the data wasn’t refreshed. The select query using TOAD returned the correct set of data. Why wasn’t the web application doing so too? Because the changes in the database weren’t committed. A waste of 2 hours of my life…

But that’s with Oracle. The other databases aren’t so forgiving. But I like that. Anyway, from then on, I’m very careful about executing update, insert and delete statements.

Still, that wasn’t enough for my paranoid mind, oh no no no. What if I need to have several statements on screen, and a few of them are updates and deletes? Perhaps you would suggest commenting them out. Well, in Query Analyzer (of SQL Server), all you need to do is highlight the statement and you can execute only the highlighted section. If you don’t highlight the commenting syntax (2 dashes or 2 forward slashes), the statement gets executed.

Well, this won’t do at all. So I came up with a fail-safe method; I chopped off the heads of any SQL statement performing “dangerous” operations. So all my “dangerous” statements look like this:

nsert into ImportantTable
values('CODE0001','Very important code')

pdate AnotherImportantTable
set id_desc='An alternative description'
where id_code='IMPT0001'

elete from SuperImportantTable
where price < 500

I add the appropriate header alphabet when I'm going to execute the statement. After execution, I lop the header alphabet again. This leaves the statement still on screen so I know what I did. And in the unfortunate event that I accidentally execute the entire statement when I wasn't supposed to, the execution will fail, because the statement isn't properly formed.

So that's my method of handling SQL statements. When in doubt, chop off their heads first.