Some Words of Advice for Developers

By definition, everyone is new to the world of MySQL stored program development, because stored programs are themselves new to MySQL. However, Guy and Steven have both had plenty of experience in stored program development within other relational databases. Steven, in particular, has been a key figure in the world of Oracle PL/SQL (Oracle's stored program language) development for more than a decade. We hope that you will find it helpful if we share some advice with you on how you can work more effectively with this powerful MySQL programming language.

1.4.1. Don't Be in Such a Hurry!

We are almost always working under tight deadlines, or playing catch-up from one setback or another. We have no time to waste, and lots of code to write. So let's get right to itright?

Wrong. If we dive too quickly into the depths of code construction, slavishly converting requirements to hundreds, thousands, or even tens of thousands of lines of code, we will end up with a total mess that is almost impossible to debug and maintain. Don't respond to looming deadlines with panic; you are more likely to meet those deadlines if you do some careful planning.

We strongly encourage you to resist these time pressures and make sure to do the following before you start a new application, or even a specific program in an application:

Construct test cases and test scripts before you write your code

You should determine how you want to verify a successful implementation before you write a single line of a program. By doing this, you are more likely to get the interface of your program correct and be able to thoroughly identify what it is your program needs to do.

Establish clear rules for how developers will write the SQL statements in the application

In general, we recommend that individual developers not write a whole lot of SQL. Instead, those single-row queries and inserts and updates should be "hidden" behind prebuilt and thoroughly tested procedures and functions (this is called data encapsulation). These programs can be optimized, tested, and maintained much more effectively than SQL statements (many of them quite similar) scattered throughout your code.

Establish clear rules for how developers will handle exceptions in the application

If you don't set standards, then everyone will handle errors their own way or not at all, creating software chaos. The best approach to take is to centralize your error-handling logic in a small set of procedures, which hide all the details of how an error log is kept, determine how exceptions are raised and propagated up through nested blocks, and more. Make sure that all developers use these programs and do not write their own complicated, time-consuming, and error-prone error-handling code.

Use "stepwise refinement" (a.k.a. top-down design) to limit the complexity of the requirements you must deal with at any given time

We are usually tasked with implementing very complex requirements. If you try to "do it all" in one big stored program, it will rapidly devolve into spaghetti code that even you will not be able to understand later. Break your big challenges into a sequence of smaller challenges, and then tackle those more manageable problems with reasonably sized programs. If you use this approach, you will find that the executable sections of your modules are shorter and easier to understand, which makes your code easier to maintain and enhance over time.

These are just a few of the important things to keep in mind before you start writing all that code. Just remember: in the world of software development, haste not only makes waste, it virtually guarantees a generous offering of bugs and lost weekends.

1.4.2. Don't Be Afraid to Ask for Help

Chances are, if you are a software professional, you are a smart and well-educated individual. You studied hard, you honed your skills, and now you make a darn good living writing code. You can solve almost any problem you are handed, and that makes you proud.

Unfortunately, your success can also make you egotistical, arrogant, and reluctant to seek out help when you are stumped (we think we are supposed to know all the answers). This dynamic is one of the most dangerous and destructive aspects of software development.

Software is written by human beings; it is important, therefore, to recognize that human psychology plays a key role in software development. The following is an example.

Joe, the senior developer in a team of six, has a problem with his program. He studies it for hours, with increasing frustration, but cannot figure out the source of the bug. He wouldn't think of asking any of his peers to help because they all have less experience than he does. Finally, though, he is at wits' end and gives up. Sighing, he picks up his phone and touches an extension: "Sandra, could you come over here and take a look at my program? I've got a problem I can't figure out." Sandra stops by and, with the quickest glance at Joe's program, points out what should have been obvious to him long ago. Hurray! The program is fixed, and Joe expresses gratitude, but in fact he is secretly embarrassed.

Thoughts like "Why didn't I see that?" and "If I'd only spent another five minutes doing my own debugging I would have found it" run though Joe's mind. This is understandable but misguided. The bottom line is that we are often unable to identify our own problems because we are too close to our own code. Sometimes, all we need is a fresh perspective, the relatively objective view of someone with nothing at stake. It has nothing to do with seniority, expertise, or competence.

Besides, Sandra isn't going to think poorly of Joe. Instead, by asking her for help, Joe has made her feel better about herself, and so both members of the development team benefit.

We strongly suggest that you establish the following guidelines in your organization:

Reward admissions of ignorance

Hiding what you don't know about an application or its code is very dangerous. Develop a culture in which it is OK to say "I don't know" and encourages the asking of lots of questions.

Ask for help

If you cannot figure out the source of a bug in 30 minutes, immediately ask for help. You might even set up a "buddy system," so that everyone is assigned a person who is expected to be asked for assistance. Don't let yourself (or others in your group) go for hours banging your head against the wall in a fruitless search for answers.

Set up a formal peer code review process

Don't let any code go to QA (Quality Assurance) or production without being read and critiqued (in a positive, constructive manner) by other developers in your group.

1.4.3. Take a Creative, Even Radical Approach

We all tend to fall into ruts, in almost every aspect of our lives. Humans are creatures of habit: you learn to write code in one way; you assume certain limitations about a product; you turn aside possible solutions without serious examination because you just know it can't be done. Developers become downright prejudiced about their own programs, and often not in positive ways. They are often overheard saying things like:

• "It can't run any faster than that; it's a pig."
• "I can't make it work the way the user wants; that'll have to wait for the next version."
• "If I were using X or Y or Z product, it would be a breeze. But with this stuff, everything is a struggle."

But the reality is that your program can almost always run a little faster. And the screen can, in fact, function just the way the user wants it to. And although each product has its limitations, strengths, and weaknesses, you should never have to wait for the next version. Isn't it so much more satisfying to be able to tell your therapist that you tackled the problem head-on, accepted no excuses, and crafted a solution?

How do you do this? Break out of the confines of your hardened views and take a fresh look at the world (or maybe just the walls of your cubicle). Reassess the programming habits you've developed. Be creativestep away from the traditional methods, from the often limited and mechanical approaches constantly reinforced in our places of business.

Try something new: experiment with what may seem to be a radical departure from the norm. You will be surprised at how much you will learn and grow as a programmer and problem solver. Over the years, we have surprised ourselves over and over with what is really achievable when we stopped saying "You can't do that!" and instead simply nodded quietly and wondered to ourselves: "Now, if we do it this way, what will happen ...?"