Bob DeAnna

Subscribe to Bob DeAnna: eMailAlertsEmail Alerts
Get Bob DeAnna: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


Article

.NET Feature — Database Toolkits: Portable and Cost-Effective Software

.NET Feature — Database Toolkits: Portable and Cost-Effective Software

We're living in an information age. Our daily life involves absorbing useful information and filtering out garbage. Information (data) plays an important role in our daily life. People, especially businesses, need to organize large amounts of disparate information. The information needs to be organized in such a way that you can easily access desired data quickly. The first step is to design a database, which balances normalization with data integrity and performance requirements. But that's just the first step. It's just as important to be able to programmatically access the data from the database in an intuitive and consistent way. That's where ODBC comes into play.

Why Use ODBC?
ODBC stands for Open Database Connectivity. It's a widely used API for database access. ODBC is based on the Call-Level Interface (CLI) specifications from X/Open and ISO/IEC for database APIs. It uses Structured Query Language (SQL) as its database access language.

ODBC is especially suitable for applications using several different databases. ODBC is popular because it's independent of any one database management system or operating system. It provides an interface that's portable to multiple platforms and relational database management systems. A single application can be connected to different databases with no changes, recompilation or re-linking required. At the same time, ODBC provides a powerful set of facilities for controlling and using databases.

Despite all the advantages of ODBC, using ODBC can still be a non-trivial task.

Disadvantages of ODBC
1.  Multiple installation of Driver Manager and driver
In a client/server environment a Driver Manager and a series of ODBC drivers are usually installed on a server. A Driver Manager and an ODBC driver have to be installed on each client as well. This allows each client to access any data maintained on the server, but leads to increased cost for hardware and maintenance. Figure 1 illustrates the situation.

2.  Overhead of setup and teardown of database
There are several basic steps for using ODBC:
• Connecting to a data source.
- Load the Driver Manager and allocate the environment handle
- Register the ODBC version
- Allocate the DBC handle. Also set any application connection attributes.
• Initialize the application
- Find out the capability of the driver through SQLGetInfo
• Prepare, build, and execute the query
- Allocate the statement handle and set the statement attribute
- Build and execute SQL statement
• Process the results
- Usually involves binding an application variable to a column in the result set and retrieves the result
• Disconnecting from the data source
- Free statement handle
- Free DBC handle
- Free environment handle

Here we illustrate the typical steps in programming with ODBC. For application developers to work on ODBC, they need to do a setup such as allocating handles (environment, dbc, statement), setting the desired attribute, performing the actual query, and cleaning up, which involves freeing the handles, disconnect, etc.

Finding Solutions
Elegant solutions exist for these problems. Here are some tips on finding them. Ideally, it shouldn't require an ODBC environment on the client machine. Second, it should be portable and simplify the process of database programming without sacrificing speed and performance.

One such solution, Recursion Software's Database Toolkit, consists of two components, client and server. The server makes use of the native operating system's ODBC environment to respond to the requests of various database clients. The client exposes APIs to database applications. Figure 2 demonstrates the architecture of this toolkit.

Benefits of Use
This solution is a cross-platform C++ solution for accessing a wide range of ODBC-compliant databases. Its purpose is to reduce development costs. It achieves this goal by reducing the need to have multiple ODBC drivers for different databases installed on the client machines. It also reduces software configuration complexity by configuring and maintaining your ODBC drivers in just one location. Figure 3 illustrates this scenario.

The toolkit contains classes and methods exposing simple APIs for building portable database applications quickly. It eases the burden of dealing with the following issues: connecting to the database, environment handling management, statement handling creation, executing and disconnecting from the database by putting highly repeated code in an easy-to-use API. There's no need for the application layer to do the basic set-up and clean-up routines.

Here are the steps for using this kind of solution:

  1. Library initialization
  2. Connect to the databases
  3. Execute the SQL statements
Listing 1 is a complete example that illustrates these steps: It illustrates one variant of the connection. A connection to the database server can also be built using another approach as shown in Listing 2.

More Stories By Bob DeAnna

Bob DeAnna joined Recursion Software in January 2006 and is responsible for directing product strategy and, most notably, leading the architecture and positioning of Voyager, the company’s intelligent pervasive distributed computing platform. Through his guidance the product has been enhanced to include native support for .NET, CF, Android, RETE-based rules engines, embedded databases, and the ability to run on nearly 20 embedded and mobile device platforms. Bob brings more than 24 years of experience in software architecture, development and mentoring. His expertise is in distributed application frameworks such as JEE, CORBA, .NET and ATMI. Bob holds a Bs in mechanical engineering from Rutgers University and a continuing education degree in C/C++ and Unix Programming from New York University.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.