Printed Icetips Article

Today, SQL is where it's at. If you want to be a big player in the market to write database clients, you have to know some SQL.

I'm not talking about designing SQL datbases or administering them. I'm talking about writing a client to a database, the thing you write in Clarion.

Companies these days are turning away from Clarion because they think it is not an adequate tool for heavy duty programs. Those companies are wrong, but they make the conclusion based on what they see. Some Clarion programmer drops in a few templates generated procdures and announces he is finished. And the customer ends up disappointed. Rightly so!

Clarion's templates will not make the kind of browses and reports that you need to deliver to your customers. Customers expect programs to be fast and easy to use, and the template generated browses and reports are neither.

What helps you write good SQL clients is a change in thinking, and this change involves several things:

1. The old SET,NEXT,NEXT,NEXT method doesn't work very well with SQL. You have to think first of entire result sets--that is, all customers matching a certain criteria, all accounts past a certain age, etc. You will end up processing each eventually on a row-by-row basis, but you first have to concentrate on getting the data back all at once and quicky. SQL is ideal for that. Unfortunately, the templates are not.

2. The traditional browse starts at the beginning of the table, and then the customer chooses in some way (locator, etc.) where he really wants it to start. That is backward! For a large table, give the customer the starting point before you even think of querying the table. Use locator fields, and then use the SQL "LIKE" clause to get matching records. Then show only the records that match. And when you get them, get ALL matching records, not just the first screenful.

3. Let the server do some work for you. With template generated browses and reports, you are extremely limited in what you can make the server do. Let's say you have a report and you use the "traditional" Clarion way of doing it. You might end up with something like this:

Read Customer Table
  For each Customer, read Account table
    For each Account, read Transaction table.

That's a horrible way of doing a report on an SQL system. You are going to end up with tens of thousands of round trips to the server. Your report might take an hour to print. But if you do it the right way, you can typically reduce the trips to the server to three, and turn an hour-long report into a 2 minute report.

Also, the server can do some calculations like SUM and COUNT that will make your program faster and more accurate.

4. Stop thinking you know more about what your customer needs than he thinks he needs. It's his business and you must presume that he knows what he needs. He's offering to give you money. All you have to do is give him what he wants, according to his own "stupid" ideas, and swallow your pride. Think of it this way. During the work day you are selling pride. You give up some of it, and you get more money.

5. Learn to use the database you are given. If you work only on databases that you design, you will never be doing big time stuff. But if you want to move on and upward, you have to be able to use databases that are designed by somebody else. No more of this "I insist that my date and time fields be LONG because Clarion works better that way!" Learn to handle the date-time fields that SQL has. I don't like it either, but I like getting paid.

6. Handle security right. Don't write your program with the assumption that it has access to the entire database. If you do, then you are creating a security risk, because there has to be a super-login-user just to let your program log in, and that user's account can be used by OTHER applications to destroy a company's business. If you just fill in template and dictionary prompts the normal way, you are creating a big security hole for your customer. NO SECURITY TEMPLATE OR ADD ON TOOL WILL FIX THIS PROBLEM. Be aware of it!

Changing your though mode from SET,NEXT,NEXT,NEXT to GET-ALL-NEEDED is painful for Clarion programmers. But it's necessary.

If you stop and think where database programming is today, it seems bewildering. But not really, when you simplify what the problem is. Despite the multitude of data access methods (ODBC, OLE DB, ADO, etc.), it still all seems to be based on the common denominator SQL. If you can handle an SQL database intelligently, with a very fast client, giving your customer the speed and power he needs, then you just concentrate on SQL and use the tools that you already have in Clarion.

I have uploade to http://www.icetips.com/downloadfile.php?FileID=59 a small example package of using some techniques I think will help. It is an ABC app, and DCT, and a Microsoft Access database. If you have the Access ODBC driver already installed on your computer, it should work. It uses the template generated FORM procedures, but not the browses. All the browses are hand coded into WINDOW template procedures for flexibility and speed.