ODBC — Open Database Connectivity

The real ODBC

The original ODBC specification was written at Microsoft and allowed database or programs as diverse at SQL Server and Access to be used from applications. The ODBC was designed for C and makes extension of C pointers. It is very hard to use and on-line examples are largely non-existant. In the last few years, Oracle has become one of the leading ODBC evangelists.

Here are some signficant reference materials on the ODBC.

• Microsoft ODBC—Open Database Connectivity Overview
• ODBC API Reference
• ODBC 2.0 Programmer’s Manual

Easysoft provides a couple of tutorials for using the ODBC. They are very hard to use

• ODBC from C Tutorial Part 1
• ODBC from C Tutorial Part 2 — Fetching Results

ODBC examples

After wading through page of ODBC documentation and a few partially correct examples, I have generated a few examples we can use in class. All of these examples are using a MySQL database. Notice how easy it should be to switch these applications to other database management systems with ODBC drivers.

• A C-ODBC program that queries a database
• A C-ODBC program that uses a prepared statements
• A Makefile for compiling these programs

The DSN

ODBC uses the data source name (or DSN) to provide convenient names for databases. This is not the same thing as a connection string.

In the examples above a connection string, containing a reference to a data source name, was used; because I couldn’t get the DSN to work by itself. This seems to be a common problem in the Linux world, but Windows application developers seem to have better luck.

JDBC examples

Because Java is an object-oriented language, it doesn’t fit the pointer-oriented model of C. Consequently, Java connects to databases using the JDBC, which is really an interface to ODBC drivers. These examples, written in Java, illustrate is use of JDBC using a MySQL driver. Of course, with the change of one line the database management system could be changed.

• Java example that lists pairs of friends
• Java example that lists friends

Java JDBC API

Oracle provide a large collection of on-line tutorials of the JDBC which are rather readable. Here are references to the JDBC classes used in my examples.

• DriverManager
• Connection
• Statement
• ResultSet

Compiling and running the Java

There is nothing unusual about compiling or running the Java version. You can use NetBeans or the command line java compiler. You may need to give the location of the jar for the MySQL driver on some systems.

[…]$ javac -d . ListFriends.java
[…]$ javac -d . ListFriendsOf.java

Running the programs is a little more complicated. You will need a shell script that specifies the location of the jar file containing the MySQL driver for the java runtime.

• Shell script to start Java application
• Shell script to start Java application

SQLite3 examples

The ODBC is difficult to use. Some database management systems, such as sqlite3 and even mysql support simpler programming interfaces.

Also, scripting languages such as Perl, PHP and Python have interfaces that are a much better fit to their programming philosophy.

C SQLite3 examples

The C/C++ interface to SQLite is described in An Introduction to the SQLite C/C++ Interface; however, you might want to start with the quickstart documentation.

• C example that only opens the database
• C example that does a lot of checking
• C example that lists pairs of friends
• C example that lists the friends of x
• C example with a callback
• Makefile for C examples

Python SQLite3 examples

These are documented in the description of the sqlite3 module. It follows Python’s standard database API.

• Python example that lists pairs of friends
• Python example that lists the friends of x