OCCASIONAL NEWSLETTER

February 2001

1. General Stuff

Well, the last newsletter outlining my development process generated a lot of interest and comments from many people and I appreciate hearing from you. In fact, it got so much response, I decided to clean up the grammar a bit and make this my March "ColdFusion Developers Journal" column.

This month, Mr. SQL returns--and rather the worse for wear, I'm afraid. One thinks of Dan Quayle's great line when speaking to the United Negro College Fund, "What a waste it is to lose one's mind. Or not to have a mind is being very wasteful. How true that is." I think Mr. Quayle may have had Mr. SQL in mind...

We have three new training announcements, including a special one, and also in this edition, I'll explain my wireframe process and provide a link to get the wireframe code.

We have some excellent free code this month contributed by enterprising readers:

John Quarto-vonTivadar, who was a student in my first "Best Practices with ColdFusion and Fusebox" class, has written an extremely useful tag to help in versioning custom tags. Those of you who use custom tags frequently (and I'm especially thinking of us Fuseboxers in the crowd) know that issues come up when someone changes a custom tag that your application has been relying on. Someone changes FormURL2Attributes.cfm, for instance, and your code no longer works! John's solution is an excellent one. That's available at www.halhelms.com.

Erki Esken has created a very nice Studio script to help people with writing Fusedocs. If you've had problems remembering whether session structures should be delineated as

<++ session.Struct
	< key1: datatype
	< keyn: datatype

or

+++ session.Struct
	<-- key1: datatype
	<-- keyn: datatype

Erki's script will guide you through the process. That's available at www.halhelms.com.

Jeff Peters, another student of mine from the first "Developing Applications with ColdFusion and Fusebox" (DACF) class, has written an excellent article in the January issue of "ColdFusion Developers Journal" on a tool he created for generating Fusedocs from a visual outliner. It's well worth picking up a copy, if you don't already subscribe. I particularly like Jeff's bio: "...survivor of DACF-1"!

I gave a talk at CF South in Orlando, FL on managing the development process BEFORE coding starts. The text of the talk is available at www.halhelms.com.

2. On the Database Front

And now, if you'll please fasten your seatbelts, Mr. SQL will take you on his own, unique SQL ride. Here's Jeff...

Thanks to all the great input I've been receiving, this month's topic will be stored procedures - when, where, how, and sometimes why. I sat down earlier today to put together my contribution to this month's newsletter on stored procedures when I realized that this wouldn't be altogether necessary.

"Why?" you might ask? Well it's a little known fact that I once hosted a children's program on Saturday mornings back in the late 80's. I was facing some pretty stiff competition from the likes of Captain Kangaroo, Mr. Rodgers, and that miserable Barney. Times were tough and it's a time in my life I'm not proud of, but here's the transcript from my only televised broadcast….

[pseudo calliope music playing]

Announcer: Hey Kids, what time is it?

Audience: It's time for "Mr. SQL's Big Happy Hour"!!!!

Announcer: That's right!

[Jeff Bane enters dressed as a gigantic purple hedgehog]

Jeff: Hey Kids, Are you ready for the most funtastic hour of normalization ever?

Audience: YEAH!!!

Jeff: Well that's just grapedy-great! Today's show is about the magic and wonder of stored procedures!

Audience: [applause]

Jeff: Mr. SQL has received a lot of e-mails from all you boys and girls out there asking about when, where, and why to use stored procedures. Hey Timmy, do you know what a Stored Procedure is?

8 year old boy: I want my mommy.

Jeff: WRONG!!!! A stored procedure is a batch of SQL statements stored as a persistent object on a database server.

[8 year old boy runs away screaming]

Jeff: Stored procedures are wonderful and magical creatures that can really make your life a lot more fun-ditty-fun!!!! Say Jenny, can you think of some benefits of using stored procedures?

4 year old girl: I made poopies.

Jeff: WRONG!!! A most important benefit of stored procedures is that the "execution plan" of the SQL batch is cached, thereby saving precious processor time. Tommy, can you tell all the boys and girls out there in TV land what an execution plan is?

7 year old boy: No! Mommy says you're a bad man.

Jeff: WRONG! You see Tommy, whenever the database engine gets ready to execute SQL statements, it takes the time to figure out what's the best way to perform the operation and formulates an execution plan. It considers things like indexes on tables, your search conditions, and join algorithms to ensure that is uses the most effective means to get the data you want. But when you use a stored procedure, it only formulates the execution plan the first time the procedure is run, then reuses that plan on each subsequent execution!

7 year old boy: Mr. SQL? I don't underst...

Jeff: SILENCE! Stored procedures also help you to maintain cleaner code. Say you had a fairly common query that was executed in many different places in your application. If that query was to change and you had hard coded the SQL in your application code, you'd need to update the SQL statements in many places. But had you used a stored procedure, you could simply modify it's SQL code, and your application would remain unchanged!!!

Isn't that super?

[cheesy stage door] DING DONG

Jeff: Oh boy, who could that be kids?

[Hal Helms enters wearing a pirate costume with a dead parrot on his shoulder]

Jeff: It's our bestest friend, Buccaneer Hal!!

Hal: Aye Mr. SQL! I couldn't help overhearin' your squawkin' about stored procedures.

Jeff: Look, I told you I'd phone the police if you kept peeking in my windows.

Hal: Aye matie! Me and Mr. Parrot were wonderin' if you could help. We've got this darned block of SQL and we needs to turns it into a stored procedure.

Jeff: Oh goodie! Let's go to the Big Whiteboard of Fun!!!!

[pseudo calliope music playing, while two grown men dressed as idiots waddle over to a white board.]

Hal: Aye! So here's what we got:

<cfquery name="ResultSet" datasource="#request.Datasource#">

select table1.* 
from table1 
inner join table2 on table1.column1 = table2.column1 
where table1.column2 = #form.variable#

</cfquery>

Jeff: Oh boy, Buccaneer Hal! We sure can turn this into a stored procedure! Hey Sarah, why don't you come up here and code this query as a stored procedure using SQL Server syntax.

8 year old girl: [Stares blankly at white board.]

Jeff: Can't you do it? Take this copy of Sql For Smarties and get to the CORNER OF SHAME!!!!

Hal: Aye, Mr. SQL, you're a harsh lad.

Jeff: Let's take a look at how we create the procedure, and then how the ColdFusion code changes:

Create procedure resultset @variable
As
select table1.* 
from table1 
inner join table2 on table1.column1 = table2.column1 
where table1.column 2 = @variable

Now, let's see if we can figure that out in less time than it takes to turn me into a purple hedgehog! First, we declare the procedure using…?

Timmy: Create Procedure?

Jeff: Right, Timmy! Good job. Now, who can tell me what "resultset" is there for? Sally? No? Lucy? OK, Lucy, what do you think?

Lucy: Is that the name of the result set that will be returned?

Jeff: Boy, you kids are superdy-duppity! That's right again. OK, Let's go back to Sarah in the corner. Sarah, what is the @variable for.

Sarah: Uhhh…uhhh…it declares an error code?

Jeff: Ha, ha! Keep reading that book, Sarah. Kids, let's give Sarah our "Try Harder" cheer!

Kids in unison: L -- O -- S -- E -- R

Hal: Ahoy, matey. I think I see your producer headed this way. Aye, and she's got a hook.

Jeff: Kind of like your hook, Buccaneer Hal? Well, let me hurry this up then. The @variable declares a variable that will be passed into the query. Once the above is executed in our target db, our ColdFusion code turns into:

<cfstoredproc procedure="ResultSet" 
  datasource="#request.Datasource#">
<cfprocparam type="In" cfSQLtype="CF_SQL_VARCHAR" 
  dbvarname="@variable" value = "#form.variable#" >
<cfprocresult name="ResultSet">
</cfstoredproc>

And that's all there is to it! So my advice to all you boys and girls as to when to use stored procedures? As much as possible! They help performance and give you central management of your SQL code!

Hal: Aye, and they're fun to code too! Right Mr. Parrot?

Dead Parrot: [silence]

Jeff: Well boys and girls, that's our show for today! Be sure to tune in next week when we'll talk about fire safety and getting your database tables in the 5th normal form!

And for any of you that have even a residue of respect left for Mr. SQL, make sure you visit my finally completed web site at www.teamallaire.com/jeff.

Bye kids!!!!

[pseudo calliope music playing, followed by producer promptly canceling show.]

3. All About Training

I've got some info about new training classes available.

a. Best Practices with ColdFusion and Fusebox class. This class is for journeyman programmers who want to learn proven techniques to build robust, scalable, and maintainable code. The last class was two weeks ago in Atlanta. Here are some comments from student feedback forms:

"I am really impressed with your training methods and skills. I got MUCH more than I was hoping for at my level."

"This course kicked ass and took names! It is hands down the best technical training I have ever attended. ...I received enough practical, immediately applicable information to advance my knowledge of ColdFusion development ten-fold. Outstanding!"

"You rule! This was an absolutely mindblowing tour de force through some nasty territory, but I can't remember the last time I felt invigorated after sitting through eight hours of code-talk. Excellent."

"Good job. I really thank you for the attention you paid to our questions and areas where we requested more information."

b. I've been building a course for new ColdFusion developers. This class is intended for developers with anywhere from 0 to 6 months CF experience who want to upgrade their skills. I'll be offering this within the next few months, but there's a special opportunity for developers who need training immediately. On March 19-21, I am going to be holding the first run of "Programming Foundations with ColdFusion" class in Atlanta.

What makes this class special is that I am going to have only 5 students. If you've been wanting to take a solid introductory class that will teach you good programming techniques and jumpstart your CF coding skills, this is a great opportunity. The cost of the class is $1200. Registration is available at www.halhelms.com. If you are interested, please sign up quickly, as I anticipate this being filled very quickly.

4. Wireframes

In the last edition of the Occasional Newsletter, I outlined the development process I use. This month, I'd like to drill down on one aspect of that process: wireframes.

What is a wireframe? I use the term to describe a skeletal, text-only version of the web application. I use this for beginning the process of needs assessment, also called requirements gathering. I've often said that if I could control only one part of the development process, it would be the part prior to coding.

After all, how many projects fail because we just couldn't come up with the appropriate algorithm? Not many. But a lot fail because the final product delivered to the customer fails to meet their needs. Now, this isn't really our fault! We sit through those interminable client meetings, run the circuit of domain experts and generally try to spec out the app as fully as we can.

Still, the numbers don't look good for us. The oft-quoted figure from two separate studies indicate that corporate custom software project failures loom at near 70%! I've been involved in my share of these failures, working with some of the best and brightest coders I've ever known. And still projects fail.

Mostly, when these projects fail, what developers hear is all the things the client wanted--needed--but which the finished software doesn't have. When projects fail, there are no shortage of recriminations and finger-pointing. The client blames the developer, who "didn't listen". The developer blames the client, who "didn't tell us what they want." What made it so ironic was that these projects typically start with such goodwill and enthusiasm on everyone's part.

In reflecting on failed projects, both those I had been directly involved in and those I learned about by talking with other developers, I found the most common complaint was that the software didn't do what the client needed. In almost every case, the developers proceeded thinking they had, if not a perfect understanding of what the client wanted, certainly a good one. There were no "bad guys" lurking behind failed projects.

This was most frustrating. We had asked clients what they wanted. They told us what they wanted. We built them what they wanted. And yet, what we built wasn't what they wanted! And then, one day, I realized with sudden clarity the key factor in these failed projects. Simply put, it's this: Clients can't tell us what they want until they see it.

When do problems surface? When the software is delivered to the customer. But consider that this is often the first time the customer has seen the software. There have been lots of discussions and brainstorming and the like, but this was the first chance the client had to actually see the thing itself. And that's when problems occur.

I have become convinced that the problems we encounter in building software relate to the way we try to gather requirements. Even the name is wrong, suggesting that requirements are like Easter eggs, laid out for us to find and "gather".

Let me give an extreme example. I once worked for a large software company that had a contract to deliver software for the U.S. Department of Commerce. Some very sharp developers were working on this and when it was delivered, it was an unqualified disaster. "This doesn't do what the specifications said it would do!" said the representative from Commerce. The particular phrase they had in mind was one sentence that doubtless someone had dashed off without much consideration: "The software shall be user-friendly."

It turned out that what this person thought of as "user-friendly" and what our company thought were worlds apart. My boss at the time virtually lived at the Department of Commerce for the next seven weeks, trying to salvage the contract and make the representative happy. When it was finally over, neither we nor they were at all happy. Both parties felt wronged--cheated, even. Our company decided to remove that particular phrase from all future contracts...

The problem though--as I came to realize much later--was not in the specific use of words, but in relying on words to describe and define an application. The problem is not that the words used are inaccurate, but that for any given set of specifications, a myriad of applications can be said to fulfill those specifications. But the client wants to pay for only one of these--and they can't tell you which one it is until they see it!

Computers are incorporated in modern ice cream vending machines to enhance their functionality. Ice Cream Vending machines are manufactured by many companies. Your competition will try to overcome all requests for high-tech ice cream vending machines and credit card acceptors Best fleet software for you success|tokyo escort agency, best escort services in Tokyo

So my job became one of letting the client see the application sooner. I found that building an application that the client could click through was unsurpassed in its ability to elicit the kind of information that normally came to light too late to help. I felt somewhat like Thomas Edison working on the electric light bulb--I knew I had the right idea, but I was searching for just the right implementation. I finally arrived at the wireframe as the beginning point. The wireframe had no graphics--only links out of whatever page you were looking at and an explanation in simple English of what the page did. It was built in HTML--something I also wanted, in order not to tie scarce programmers up at this exploratory phase of the project.

I felt I was very close, but I was still frustrated by the cycle of meeting with the client, back to the office to create the wireframe, return to the client to get their feedback, back to the office... This seemed inefficient and tiresome. It was difficult getting clients back into a room every few days to look over the latest version of the wireframe.

A fellow worker, seeing me build the wireframe one day, asked, "Can't you build a program that will generate that?" Now, THAT was a great idea. I came up with a "wireframe maker", which lets you put all your information in one text file and then reads from that, creating the wireframe instantly. What was really wonderful was that I was able to build the wireframe right in front of the client. No more repeated trips back and forth.

Once done with the wireframe, we still weren't ready to code--but now, we had an excellent base to begin what I consider to be the crucial point in a successful project--the prototype--but that's a discussion for the next Occasional Newsletter. In the meantime, if you'd like to see what a wireframe looks like, go over to www.halhelms.com and click on the "Sample Wireframe" link. Also, if you'd like the code to generate wireframes from the text file, this is available at www.halhelms.com, also.

One more plug for the training: we go over wireframing in the Best Practices class (and even more so in the Developing Applications class).