| Dan Pressnell 2006-03-09, 7:55 am |
| If you download the file "Basic SQL Handling Setup.zip" from icetips.com and
look at the code, you might think, "Um, there's only one line of SQL in this
entire thing. How can this guy says this is a decent SQL example app?"
But that kind of thinking will hold you back. The entire app uses SQL every
time it does file processing. Sure, there is not much SQL coding in it, but
the browses, the update window, and the report all use SQL. If you make a
browse using an SQL driver, it helps to think "I'm using SQL, or at least
this is going to SEND SQL to the server, so I need to think what is going to
happen." If you think like this, you can avoid common pitfalls like having
a browse that doesn't have a unique key or sort order.
Take the one report procedure that is in the app, and examine what it does:
1. thisreport.setorder('Sch:SchoolName') -- This line will cause Clarion
to send an ORDER BY clause to the server when it queries the database. It
will be something like "...ORDER BY SCHOOLNAME". You don't need a key for
the report file, and there doesn't have to be an index on SchooName defined
on the server. But you should think, "How big is my table, will this take
an hour? Do I need to define an index on the server to speed this up?"
2. thisreport.setfilter('Sch:City = ''' & pCity & '''') -- This line will
cause Clarion to generate a WHERE clause for its query. Something like
"...WHERE CITY='Dallas'". But did you notice the bug in my code? Under
some circumstances, this could cause the report to contain every school for
every city! This is not always caught by testing, and can be an
embarrassment when a user discovers it. Understanding what Clarion does
when you have this type of bug helps you prevent it in the first place.
3. thisreport.reset() -- This line causes Clarion finally to prepare the
SQL statement that uses your view structure, your filter, and your sort
order.
4. loop while thisreport.next() = 0 -- This line actually does two
things. The FIRST time through the loops, Clarion sends the SQL query, and
retrieves the first record. After that, each time through the loop, it
simply retrieves the next record from the result set.
Another thing to consider is the "thisreport" object itself. It's derived
from the ViewManager class. This class will automatically set an ODBC
"block cursor" of 20 rows, causing the retrieval of your data to be much
faster than it would be otherwise. The Clarion file driver will manage that
block cursor for you, which would be very messy if you had to do it
yourself. (Think of programming for an array which handles partial results
of 20 records at a time.)
About the report procedure itself, aside from the SQL considerations:
From looking at the procedure, you don't see any apparent SQL at all. In
fact, you could do exactly the same thing for a TPS file. That's a good
thing, because you limit what you have to think about when writing your
code. The less you have to think about, the fewer bugs you are going to
create. The more you use one common technique, the more you turn yourself
into a human macro for all the repetitive things that you have to do,
meaning that you can save your brain for those uncommon tasks. In other
words, you spend less time reinventing the same wheel, freeing up that time
to think about things you can spend time thinking about more productively.
It leads to this paradox, which is nevertheless true: The LESS time you
think about your code for the basics, the BETTER your code becomes, and the
more productive you become.
Another thing about the report procedure is that it uses the report
template, and then ignores it! Actually, it uses the ThisReport object, and
the template code handles file opening and closing, and the VIEW. Another
benefit of using the report template is that in the report designer, you
have access to the dictionary tables and fields. But as for the logic of
the code for retrieving the data and printing the report details, this
technique gives you a degree of control that is difficult otherwise. So
what does this have to do with SQL? Everything! It doesn't matter how good
you are at constructing SQL alone. You don't just retrieve data, you
organize it and present it as well. The best SQL in the world will not make
a good program. It takes more, such as how your report code works, how your
browses and other windows are constructed for the user, etc. It's all one
big package, and ignore one part of it, and you are wasting your time. Your
potential customers are not going to like your programs simply because you
use Clarion or because you know SQL. As with every other programming
language in the world, Clarion is only as good as the programmers who use it
and the techniques they use. It reminds me of a story I once read posted by
an amateur flute player. His flute sucked. The music was terrible and the
quality of his flute was holding him back. Then one day a professional
musician visited him. The musician picked up the flute and played some, and
the music was beautiful. Weird how that happens, huh?
Dan
|