If not using the database, please disconnect

I’m maintaining some Windows programs created by the PowerBuilder software. The original developer didn’t plan for the programs to be used by many people. So the instant one of the programs was run, a database connection to the Sybase database was opened. And left there.

As more programs were created in this manner (and added to the suite of programs my team is in charge of), the number of total users also increased. Since the connections were held in place, table locks between users became a real problem, because a user could be done with an operation, but still hold onto the table. This also meant the database became clogged up with connections, usually non-active.

The better solution is to open the connection when you’re going to do any database operation, and then close it once you’re done. But the original programs were developed like eons ago. If I understand it correctly, client programs back then assumed they had total control over the database. Contrast that with the web applications of today, and let me just say that, I have my work cut out for me…

I decided to write something on this after reading Raymond’s article on cookie licking. So if you’re not using any database functions, please disconnect.

If you depend on order, use an order by

It was an uneventful morning. All of us were at our computers, softly typing in the noiseless office. Tippity tap, tippity tap… (Except for me, since I was cranking out code to complete a project by a deadline, and was furiously testing the physical limits of the keyboard. But I digress…)

[image by tysmith]

A phone rang, breaking the silence.

“Hello? Yes… Uh huh… REALLY?!”, answered my colleague.

What followed was a whirlwind of exclamations and activity back and forth between two of my colleagues as they discussed the situation. Some description of something in a report wasn’t correct, as reported by a user, and they were tracing the origins of the error. I didn’t find out what the problem was, since I had my own problems to deal with (tap-tap-tap).

Even with my earphones on, I could still vaguely figure out what’s going on. It’s not that they’re loud (although there’s that…), just that I’m aware of my surroundings. The problem boiled down to a select statement.

Let’s say the database table looked like this:

create table product
(
product_id char(8) not null,
effective_date datetime not null,
product_description varchar(50) not null
)
alter table product primary key (product_id, effective_date)

Based on the narrative I gave, and the structure of the table, you should reasonably be able to figure out the issue. No?

Alright, so the product description in a report wasn’t correct. There’s an effective date column, so I would think the record with the latest effective date was more relevant. What if the select statement had a where clause only on the product_id?

select product_id, product_description
from product
where product_id = 'PROD0001'

Based on the table structure and the primary key, there’s every reason to believe that there were multiple records with the same product ID. Why the original programmer failed to take note of this is beyond me…

In the case where there were 2 or more records with product ID as “PROD0001″, the default order was product ID ascending, then effective date ascending (according to the primary key). What happened to the description based on the latest effective date? It’s right at the bottom of the result set. What was required? The description based on the latest effective date.

Since the default was to use the record at the top of the result set, the description based on the earliest effective date was used. Hence the error.

There were multiple records for the same product IDs. The reason this problem didn’t occur was that the description was the same for the respective IDs. Until now.

My colleagues ordered the results by effective date in descending order, and all was well.

There were other instances where the result set of an unordered select statement came out fine, until the order was different. The data could have, by coincidence, been inserted in the correct order, hence the retrieval automatically had that order. The data could have, because of a primary key, been automatically ordered when retrieved. But that’s no excuse to depend on the default ordering.

If you depend on the order in your results, use the order by clause.

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.

[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.