It is Wednesday, May 09, 2007 and I am sitting in the Admiral's Club at the Buenos Aires airport. I just finished two days in this lovely city. My first day was spent enjoying the chilly, but very sunny afternoon, walking for four hours around the city. It is a busy, busy place with many buses throwing way too much gritty exhaust into the air (hey, but they are Mercedes Benz buses! I think that Americans would be generally surprised to see that in many parts of the world MB vehicles are not seen as luxury machines....). My favorite sight was the Floralis Generica fountain sculpture located between the University of Law and Science and the Malba art museum. Here is a photo of this beautiful creation:
As I approached it at around 2 PM, the sun reflected brightly off the pool at the flower's base directly on to the undersides of the petals, so they shimmered intensely. At night, the petals close! Very unique and enjoyable to see....
On my second day in BA, I had to work. Well, that was only fair. Quest did, after all, pay for ticket and room at the extremely pleasant Hilton Hotel.
My work was, however, a great pleasure. Through the diligent efforts of Enrique Nunez-Ruiz of Quest and Aldo of Latinsys, our partner in Argentina , 150 people had registered for a three hour seminar on best practices – and almost 100 people actually showed up! So I "did my thing" – but with a big difference from the usual best practices session: I had to slow down my delivery significantly, because we were using simultaneous translation. That is, I spoke into the microphone, and a translator sitting inside a glass box at the back of the room translated instantly into Spanish, and the attendees listened to her voice through headphones as I spoke and gestured and typed.
Now, that is a hard way to learn from someone, especially someone like me, who talks way too fast all the time, and has a very hard time remembering to stop and take a breath.
But I did it! The translators were not mad at me, everyone seemed pleased, and I did not go way over my time limit...a major personal victory for me.
In the meantime, I continue to fine-tune and improve the way I talk about key best practices. I have long promoted using table APIs to replace direct SQL statements in one's application code. This is how I now try to open up programmers' eyes to the reason this is so important: I perform a magic trick.
The magic trick is that I will convince you through the power of logic alone that SQL IS BAD!
Here's how the trick works:
1. Would you agree with me that hard-coding is bad? Hard-coding is when you "freeze" (make static) in your code some information about your application. The classic example is a hard-coded literal value. We all generally agree that hard-coding is bad, because when that value or information changes, you have to find all the places you "froze" that value in place, and change it.
So I show attendees this code and ask them to identify the hard-codings:
CREATE OR REPLACE PROCEDURE process_employee (
department_id_in IN NUMBER)
IS
l_id NUMBER;
l_dollars NUMBER;
l_name VARCHAR2 (100);
/* Full name: LAST COMMA FIRST (ReqDoc 123.A.47) */
CURSOR emps_in_dept_cur
IS
SELECT employee_id, salary
, last_name || ',' || first_name l_name
FROM employees
WHERE department_id = department_id_in;
BEGIN
OPEN emps_in_dept_cur;
LOOP
FETCH emps_in_dept_cur
INTO l_id, l_dollars, l_name;
analyze_compensation (l_id, l_dollars);
IF l_dollars > 1000000 THEN must_be_ceo (); END IF;
UPDATE employees
SET salary = l_dollars
WHERE employee_id = employee_id;
EXIT WHEN emps_in_dept_cur%NOTFOUND;
END LOOP;
END;
[I see, by the way, at least NINE hard-codings in this program. How many can you find?]
So we talk about that and I get everyone to nod: Yes, yes, hard-coding is bad.
OK. Next step. I claim that EVERY SQL STATEMENT YOU WRITE IS AN INSTANCE OF HARD-CODING.
What can I possibly mean? Think of a three way join, an insert into a table with a foreign key. What information is embedded inside these statements? Information about the relationships between our tables, in essence, the entity-relationship diagram of the application.
In other words, every SQL statement you writes captures (and freezes) a portion of the ERD as it exists at this current point in time.
They are all hard-codings.
So...time for symbolic logic:
1. Hard-coding is bad.
2. SQL statements are hard-codings.
3. Therefore, SQL statements are bad.
And that, dear friends, is the sort of fun we had in Buenos Aires!
Well, it is time to catch my flight to Lima and then on to Bogota, for more magic tricks with PL/SQL....