When I first started programming
in DOS Basic, random access files provided all the database power I needed. However, as my
programs grew more complex and the demands of the end users increased, I found that I
needed faster searching and sorting, and I turned to a number of indexers and database
managers to provide these capabilities. However, now that I spend most of my time
developing Windows applications, I've found that I need an even greater level of
sophistication in my database access code. I've discovered client-server database
architectures and learned a few things about SQL, the Structured Query Language. SQL,
which is sometimes pronounced "ess cue ell" and sometimes pronounced
"sequel," is a database manipulation language. Its syntax bears some resemblance
to Basic, and it is a convenient way of working with databases of all sizes, but
particularly medium- and large-sized databases. It provides a lot of tremendous
capabilities. For example, you can gather a list of all the customers in a single zip code
with a single SQL statement:
SELECT * FROM CUSTOMERS WHERE 'ZIP CODE' = 48127
I've also worked with ODBC, Microsoft's Open Database Connectivity
engine. ODBC provides a connection a common interface between your program and the
underlying databases. It allows you to use a standard set of API calls to access virtually
any underlying database, whether it is a shared file database (like dBASE or Microsoft
Access), or a full-featured client-server database (like Oracle or Informix).
ODBC provides two lures to the
programmer: the end user can connect the software to his or her database of choice without
any changes required in the program, and the programmer only needs to learn one set of
database access methodologies, ODBC. (If you needed to call each database's native
drivers, your program's logic might need to be completely rewritten every time your end
users wanted you to support a new database.) All you need is Microsoft's ODBC engine
(which is freely available from Microsoft), and an ODBC driver for the specific
database(s) to which you will be connecting (many of which are also freely available from
the database vendors' web sites). In fact, ODBC drivers are currently available for 55
different database formats. SQL and ODBC are powerful technologies, and they can be a
great boon to you if you need to do serious database access.
There is only one problem:
Microsoft's ODBC SDK, like most of their other SDKs, is oriented toward the C language. If
you wish to use ODBC, you will need to convert the header files and write a lot of
"helper" functions to make your programs work smoothly, or you can invest in a
library that has already done all the hard work. This is where SQL Tools Pro comes in.
[See? That long preamble really is relevant!] SQL Tools is an add-on library for PB/CC and
PB/DLL that encapsulates the ODBC API and allows you to embed SQL statements directly in
your PowerBASIC programs. It is an extensive library, with dozens and dozens of functions
and procedures that make accessing ODBC databases a relatively simple task.
The library is shipped as a single
150k DLL with a series of include files that facilitate access to the library routines.
You add to your program a "master" include file (which brings in all the other
needed SQL Tools include files), and then your program makes calls to the SQL Tools
functions to perform all your database access. Of course, you must also ship the SQL Tools
DLL with your program, but at about 150k, it is not a terribly cumbersome addition. The
product provides you with nearly complete access to the ODBC API (through version 3.0),
and the thorough documentation will help you quickly learn the ropes of adding SQL and
ODBC to your programs.
Although SQL Tools is a remarkably
well-designed product, it does have a few rough edges, most notably in the installation
process. Perfect Sync prefers to ship exclusively by e-mail, so you will receive an e-mail
message with a large, self-extracting archive attached to it. The e-mail message contains
brief (and, unfortunately, incomplete) installation instructions. I recommend you save or
print this message, since there is no README.TXT file inside the self-extracting archive.
Once I unpacked the archive onto my hard drive, I had to use the Windows Explorer to
access the documentation (which is supplied in both Windows Help and Adobe Acrobat
formats). Once I could read the documentation, I found much more detailed installation
instructions than the e-mail message contained. In addition to being largely
irrelevant at this point, these instructions were still incomplete. I found three
additional self-extracting archives inside the SQL Tools directory: sample programs,
sample databases, and Microsoft's ODBC files. None of these self- extracting archives were
mentioned in the documentation. With a little effort and experimentation, I got all the
files unpacked onto my hard drive in their proper subdirectories.
Fortunately, the documentation does
a good job of explaining the SQL Tools sample programs. When you unpack Microsoft's ODBC
files, you'll see README.1ST file, but if you try to use the Windows Explorer to open the
file, Windows will ask you to associate an application with the ".1ST" file
extension. Thus, you will need to open Notepad (or an equivalent text editor) and manually
navigate through your hard drive to the file before you can read it. Once I managed to get
SQL Tools installed properly, I found the product fairly easy to use, but I have done some
work in the past with both ODBC and SQL. These technologies will likely be somewhat
intimidating to the new database programmer. However, the SQL Tools manual does a great
job of covering these topics in reasonable depth, and references to further educational
resources, many of them free, are included that will help the reader gain a more complete
understanding of these topics.
There are two versions of SQL Tools:
Standard and Pro. The Standard version allows you to access 1 or 2 databases at one time,
with 1 or 2 active SQL statements for each database. The Pro version allows you to access
up to 256 databases at one time, with up to 256 active SQL statements for each database.
It also provides a much more extensive set of functions that give you greater control over
the ODBC engine and the databases themselves. (Please note that a database is a collection
of tables. A table is a single data file, which is composed of records and fields, called
rows and columns in the database world. These terms may not be the ones to which you are
accustomed, so please note the definitions carefully.)
Our ratings for SQL Tools Pro 1.0 are:
Ease of Use:
Is there anything a database access library needs to do that SQL Tools
cannot provide? We honestly could not think of a single thing (in large
measure due to the fact that the database work is carried out using SQL statements and
ODBC calls). SQL Tools is a remarkably well-thought-out package, has an excellent design,
and encapsulates the ODBC interface very well.
We awarded SQL Tools a 5 for its
capabilities. Our only concern is that the developers may not be able to find enough new
features to add to the product to create version 2.0! Overall, SQL Tools is relatively
easy to use. As mentioned above, we do have a few concerns about the installation of the
product. The lack of a README.TXT file with a recap of the e-mail message's brief
installation instructions was an unwelcome discovery, and it was inconvenient to have to
figure out how to finish the installation of the three undocumented self-extracting
archive files. This package also requires Basic programmers to learn SQL in order to
access databases. The syntax of SQL is reminiscent of Basic, and SQL fundamentals
are fairly easy to learn, but the nuances only come through extensive experience. These
drawbacks are mitigated by the excellent structure of the function and procedure calls. We
gave SQL Tools a 3 for ease of use.
The documentation is, in a word,
outstanding. As noted, the documentation comes in both Windows Help and Adobe Acrobat
formats. The Acrobat file is more than 650 pages long, and the Windows Help file is 1.7
MB, but they are very well organized, are very thorough, and do an excellent job of
teaching you to use the product. The introductory SQL information will also help you get
started quickly. If you've read my reviews before, you'll know that I firmly believe that
a product should come with a printed manual. Unfortunately, SQL Tools follows the crowd in
this regard, providing only electronic-format documentation. If it had a printed manual,
SQL Tools would get a perfect score, but the paperless format forces me to downgrade the
documentation to a 4.
We gave SQL Tools a 5 for
reliability. We uncovered no bugs in our testing, and the program handles a wide variety
of ODBC- compliant databases without a hiccup. Of course, you must keep within the
limitations of your ODBC driver; for example, it's impossible to use a SQL JOIN statement
on all the flat file databases with which I've worked. (Please be aware that SQL Tools
provides you with functions that can query the exact capabilities of the driver, so your
program will never have to "guess" if a function can be used.)
Finally, we awarded SQL Tools a 4
for performance. SQL Tools itself is snappy enough, but the reliance on the ODBC engine
makes database access slower than when using native drivers. However, the flexibility of
an ODBC connection allows you to hook your applications to many different databases in the
field with no code changes (as long as you program for the least common denominator of
functionality among ODBC drivers), so this is an acceptable trade-off for most developers.
[By the way, PowerBASIC + SQL Tools runs circles around Visual Basic's internal database
access tools, especially the new DAO and ADO technologies (which are based on ActiveX).]
If you are doing heavy duty database
access, you should purchase the Pro version. But if your database needs are less extensive
(especially if you do not need to use multithreaded access to the database), the Standard
version will save you a few dollars.
In summary, if you need to do
serious database access with PB/DLL or PB/CC, purchase a copy of SQL Tools. You'll be glad
you did. For more information on SQL Tools Pro, contact Perfect Sync, Inc.
--By Alan C. Earnshaw
SQL Tools and SQL Tools Pro
Published by Perfect Sync, Inc.