Build database-independent applications with ODBC and Q+E's 2.0 release of Database Library
Steve Apiki
What Microsoft's ODBC (Open Database Connectivity) is only beginning to provide today--database-independent applications for users and a single database target for developers--Q+E Software has been delivering for over two years with Q+E Database Library. Although it fit the bill, however, the initial release of Database Library wasn't quite flexible enough. As it freed applications from supporting multiple databases, it bound them to drivers sourced solely from Q+E, making Database Library 1.0 a less-than-open solution.
Database Library 2.0, built on ODBC itself, sets this tested database technology squarely on top of a well-supported multivendor standard. ODBC is still maturing, and addressing the ODBC API dire
ctly is coding a little too close to the metal; all the low-level details are up to the developer. Q+E Database Library 2.0 abstracts ODBC a few steps further, letting you build your application assuming you'll run on a database system that supports the most sophisticated database management functions available. If, instead, your application finds itself running against dBase files, Database Library will (for the most part) fill in the resulting gaps.
One Library, Many Databases
Q+E Database Library is actually not a single library but a family of DLLs. Using any development tool that can call a DLL (and that can range from Microsoft Excel to Borland C++), you build an application that conducts all its database access through calls to Database Library. The DLL, in turn, calls ODBC-compliant drivers that provide client-server access to the database. The data can ultimately reside in any database for which someone has built an ODBC driver, including SQL Server, dBase, or DB2.
Most of this inter
operability comes courtesy of ODBC, so you could write directly to the ODBC API. But not all ODBC drivers support all the features you may need, and you have to handle the differences on your own. Database Library masks these differences. For example, level-1 drivers and some level-2 drivers don't support backward-scrolling cursors, but Database Library provides this capability for you.
Also, Database Library is just easier to use. As a high-level interface, it can automate functions and tune for performance above the driver level, saving you considerable effort. This includes automatic establishment and tracking of additional database connections when you execute multiple SQL statements against databases (e.g., SQL Server) that allow only one statement per connection.
Equal Footing
The best part of Q+E Database Library, and the single item that most sets it apart from other ODBC toolkits, is its provision of an unvarying set of powerful database management functions on every database platfor
m. Using Database Library, you can safely write an application that assumes forward, backward, and random record selection, and even transaction support, regardless of the underlying database.
However, that functionality is not delivered solely by the DLLs; some of it relies on the support that is provided by Q+E's ODBC drivers. Q+E supplies 20 database drivers with Database Library. Q+E also sells these off-the-shelf ODBC drivers to end users as part of its ODBC Driver Pack. Although you can use them for development, you can't resell them with your final product; you need a distribution license from Q+E for that. However, you can opt not to provide a database driver at all, letting your users install Q+E or third-party drivers instead.
Forward- and backward-scrolling cursors are built into both the Q+E drivers and the library itself, so you're safe whether your customers have Q+E drivers or not. But although transaction support is also provided by the library, it relies on the transaction capab
ility of the database system itself. In the case of dBase (since the dBase-file driver must implement all the SQL management functions itself), this requires transaction capability in the driver. For the moment, that limits you to Q+E's driver.
ODBC is simply not as uniform a layer as it ought to be, and supporting some functions is out of the realm of a database access tool. Database Library works best when running on top of Q+E database drivers, and that's probably the way you should plan on distributing your application.
Building an Application
Q+E Database Library makes support for multiple data sources easy, but it doesn't do much to simplify the process of actually building a database-enabled application. It certainly doesn't compare on that score to VBX-based development kits like Coromandel's Integra VDB or Q+E's own MultiLink/VB.
Nevertheless, it is a fairly high-level library, and the API is not hard to work with. The test application shown on page 139, which can switch betwe
en several data sources, browse a database, join tables, and automatically update fields based on the contents of other fields, took about three days to produce from a blank Windows application skeleton.
In general, working with the Database Library means working with SQL. Once you've made a connection, you spend most of the rest of your code building SQL statements and processing those statements and their results using Database Library functions. I built the browser, for example, by building a SQL SELECT statement, executing it through the library, and scrolling back and forth through the results, which were bound to variables displayed in the edit controls.
Database Library also includes an assortment of current-record access functions. You can use these functions to (among other things) update the database without having to write SQL UPDATE statements.
The data-dictionary functions, new in 2.0, provide the information you need to let users choose data sources and tables. You can query
the database system for available database names and table names, and the results are returned as if you had executed a SQL SELECT statement. The library and driver handle all log-on details. These functions let you build your application without anticipating a certain database structure--a critical point for developers of shrink-wrapped applications.
Database Library 2.0 includes an entire Query Builder interface. You can use the Query Builder to let users build SELECT statements interactively through a dialog box. Version 2.0 also introduces parameterized SQL statements that let you bind variables to expressions in a SQL statement and thus programmatically modify queries with little effort.
The sample application uses commit and rollback functions to update all related records in a single transaction. Transaction support, explicit record locking, and determination and setting of database isolation levels are all handled by the library. Again, although the data reliability you'll get is a func
tion of the driver and the database system itself, you can use these calls transparently on any database system.
During the development process, I ran the sample application exclusively on a test Q+E dBase ODBC driver. When it was completely done, I ran it against Microsoft SQL Server, through the Microsoft ODBC driver. Except for exposing an error in my application (I was pulling in system tables when I didn't need to and was getting back EOFs [end-of-file] when trying to read them), the program ran flawlessly. Overall, portability from database system to database system was outstanding; the only real lesson here is that no matter how good the underlying library is, you should plan to test your application on every system you intend to support.
The finished application was fast, even without any tuning on my part. The Database Library provides functions to set virtually every parameter that can affect performance (or reliability), so you can optimize database access by hand if performance is cr
itical.
Toward Greater Independence
Q+E Database Library 2.0 and ODBC are intricately intertwined for the moment, but that's at the middle of the driver stack; your application (at the top) need only concern itself with the Database Library API. This insulation affords your application another level of database independence and allows Q+E to support other database access standards as they mature. Q+E has announced plans to support Borland's IDAPI and could support other database access methods in the future. Since version 2.0 of the library is also backward-compatible with version 1.0, users of version 1.0 can now support ODBC by simply sliding version 2.0 under their existing applications.
Besides database independence, Q+E Database Library provides a measure of operating-system-platform independence as well. As of this writing, Database Library 2.0 runs on Windows only. Q+E says that it will ship OS/2, Macintosh, and Unix versions of the Database Library by the end of the first quarter. All
these non-Windows versions will remain ODBC-compliant, and Q+E will provide ODBC drivers on those platforms.
If you're developing Windows database applications today and you can't use a VBX, Database Library 2.0 is a no-brainer--it's the only sensible choice. If you can use a VBX in your development environment and are after simplicity, a toolkit like Integra VDB or MultiLink/VB might be better; however, Database Library 2.0 is still the more powerful and flexible option.
Key New Features In Version 2.0
-- ODBC compliance
-- Data-dictionary functions for finding data sources
-- Built-in query builder
The Facts
Q+E Database Library 2.0.....$699
(Driver distribution requires additional license.)
Q+E Software, Inc.
5540 Centerview Dr., Suite 324
Raleigh, NC 27606
(800) 876-3101
(919) 859-2220
fax: (919) 859-9337
Illustration: An Application Built with Q+E Database Library 2.0
(1) Q+E's data-dictiona
ry functions qeSources(), qeDatabases(), and qeTables()
provide a catalog of data sources visible to the client workstation. You
can use these functions to let users switch between data sources at run
time. Once the choice is made, your application makes the connection to the
database through the library and the database driver, which together can
handle all log-on details.
(2) qeColumns() provides the application with a list of columns in the
database. You build SQL SELECT statements and pass these to Q+E to gather
field information from the database (in this case, Microsoft's SQL Server).
(3) If the underlying database driver supports SQL joins, your application can
build a SELECT statement that joins independent tables. However, you can't
join tables that come from separate sources (a limitation of ODBC). Both of
these tables are dBase files.
(4) Using SQL statements and Q+E record-based functions, you can retrieve all
the data required for a simple
browser window in a single loop. Q+E
Database Library, subject to the sophistication of the driver, handles
transactions, record locking, and forward, backward, and random record
selection.
Steve Apiki is a BYTE contributing editor. He is senior developer at Appropriate Solutions, Inc. (Peterborough, NH), a consulting firm specializing in cross-platform development. You can reach him on the Internet or BIX at
apiki@bix.com
.
Q+E: The Key to ODBC
