Promoting Linux Requires Advertising. That's What Counts. TM
GnuCash Personal Finance Manager

Free/Open Source SQL Programming Systems

[ Recommended by Linux Journal! ] [Sitting Penguin] When developing a new SQL application, the developer/architect must select a programming interface that fits the requirements of the application. There is a rainbow of requirements to base this decision on:

Need It Yesterday
Is the goal to rapidly develop a data-driven GUI application? Then you probably shouldn't even be looking here, and instead look at RAD Tools.
Multiple Database Support
Does it have to access more than one, or even dozens of different brands of databases?
Multiple Operating System Support
Does it need to run on Gnu/Linux only, or other operating systems?
Will this application need to be around a decade from now? If so, it might be dangerous to base it on a commercial product, as the company that provides it may go out of business.
Language Interfaces
Is it more important to have the interfaces be in some specific programming language, or are you willing to code in C/C++? Note that most programming languages/environments, such as Perl, Python, PHP and Scheme, provide 'thin' wrappers/interfaces to an assortment of underlying C language interfaces. Some are listed below, but you should also check language-specific sites for more options.
Will the application be proprietary? If so, then you don't want to write code that links to GNU GPL licensed libraries. You want the GNU LGPL instead, or one of the other proprietary-code friendly licenses.
Data Dictionary/Introspection
Does the application have to work with arbitrary, unknown database tables? If so, then there needs to be a mechanism to discover the tables dynamically, at run time. And not just the tables, but also the types of the data contained in the fields, thier names, and thier meaning.

This page is not as actively maintained as it could be. My apologies for any link-rot that you may find.

This page is not a complete listing. Use the following cross-references to form a more complete picture.

Hot! If you like this page, then check out Enterprise Linux (TM)
Enterprise Linux Banner

Data-Dictionary Programming Interfaces

Programs that need to browse unknown databases (databases whose schema are not known at the time that the application is being written/compiled) have to solve the problem of figuring out what all of the SQL tables are, and what the data types (int, float, date, string, etc.) of each field in a table are. Collectively, this information is called the "data dictionary". The process of discovering what a database contains is analogous to the concept of "object introspection" or "reflection" that is found in Java, Scheme, CORBA's DynAny and Microsoft's C#/CLI/CLR .net strategy. The following programming interfaces attempt to provide the kind tools needed to be able to dynamically discover information about a database, and manipulate it in a straightforward manner.

hk_classes provides a set of C++ classes for accessing SQL databases. hk_classes is the non-graphical spin-off of Knoda, the KDE-based graphical forms-designer/report generator. Handy because it allows you to perform many basic query activitives without having to write any SQL queries.

BondDB from Treshna provides a database access abstraction suitable for the development of data-driven applications. BondDB is the non-GUI spin-off of the graphical, data-driven Bond rapid application development system. BondDB is dual-licensed under the GPL and a commercial license. The GPL prevents proprietary development on top of BondDB, which is exactly what the (non-free) commercial license provides.

libgda is the non-graphical spin-off of the GnomeDB project. It provides a set of abstract programming interfaces that can access not only SQL databases, but (in theory??) XML data providers, flat files, LDAP databases, and (in theory) other data sources. This project is still quite young (i.e. alpha/beta, missing many features) but it does provide the basic dynamic data/data dictionary interfaces needed to explore unknown databases.

ODBC and Related Programming Interfaces

The ODBC API programming standard was developed and championed by Microsoft to provide a single programming API to all of the commercial SQL databases, as well as to head off a competing industry standard that Microsoft did not control. Microsoft won, and uses ODBC to promote other Microsoft database products at every turn. Most Windows programmers use ODBC to connect to SQL databases. In actuality, ODBC is not too bad: it offers almost all of the basic features and functions that one might hope for in an SQL database API. Its only peculiarity and bizarrness is it's insistance on using "handles": a programming anachronism that Windows can't seem to shake. Despite this oddity, ODBC is a workable programming interface for Unix programmers, and is probably the best bet for anyone wishing to write code that can connnect to dozens of different databases. There's a good selection of alternatives, as seen below.

unixODBC provides ODBC support for Gnu/Linux. C/C++. Dual license: GPL and LGPL, allows both free and commercial uses. Appears to include drivers for more than a dozen databases, including most major commercial and free databases. (UDB, DB2, Sybase, Informix, AdabasD, Postgres, MySQL, MiniSQL, Empress, YARD, Interbase, Linter, Mimer, among others.) Includes a graphical ODBC Driver Manager, and a graphical data explorer.

Commercial support via EasySoft.

iODBC is a LGPL'ed implementation of ODBC conforming to the Microsoft and X/Open SQL CLI (Call Level Interface) specifications. Language wrappers available for PHP and Perl, C++, Java, Python, TCL, others. Dozens of drivers available, mostly for commercial databases. Ported to dozens of different Unixes, including HP/UX, IRIX, MacOS classic, MacOS X, SCO, Sequent, Solaris, Unixware, AIX, FreeBSD, DG/UX and even OpenVMS.

This project appears to be an open-sourcing of the one of the ODBC products from OpenLink Software, which appears to continue to be actively involved in the maintenance of this project, and appears to offer commercial support for these interfaces. There's a fair amount of blurring between what's part of iODBC, and what's an OpenLink product, and the site encourages you to explore other OpenLink products.

ODBC++ provides a set of cross-platform C++ class libraries for ODBC access. Uses a thread-safe design, allows multi-threaded execution. LGPL.

FreeODBC++ provided LGPL'ed C++ ODBC interfaces for Gnu/Linux, FreeBSD and Windows. The C++ interfaces are based on/derived from the JDBC class hierarchy, and thus implements a good subset of JDBC in C++. On Unix, this is built on top of unixODBC, and thus offers the same driver selection.

SQLxx provides some simple C++ iODBC wrappers for postgres and MySQL. LGPL.

FreeTDS implements the TDS (Tabular Data Stream) protocol that is used by Sybase and Windows clients to communicate with database servers. The native interface is C, and there are Perl and PHP wrappers for FreeTDS. There is also a FreeTDS driver for the JDBC 1 and 2 API's. The code is LGPL'ed, allowing commercial software to be developed on top of it. Uses a modern automake-based build system. Browsable CVS on sourceforge. This is a good choice if you know that all of your data will be on the Microsoft SQL Server.

OTL provides a C++ STL stream iterator interface to ODBC, Oracle and DB2. STL is, of course, the Standard Template Library for C++. If you use STL, this is the thing for you.

DTL makes ODBC recordsets look like an STL container. It provides standard STL iterators. Include C++ reflection (introspection??) support, thus automatically providing data-dictionary/dynamic runtime discovery.

OpenLink ODBC
An ODBC client library is available from OpenLink Software. Provides Gnu/Linux support for Oracle, Sybase, Progress, Ingres and Informix with their client libraries.

SQL Internals

Tools, bits and pieces you might expect to find inside of an SQL database.

SQL-Statement is an SQL Parser: it takes as input an SQL string, parses it, and allows you to work with its guts. Written in perl. GPL or Artistic License.

Open SQL Parser
Open SQL Parser is written in PHP, GPL license. This is currently a very very young project, currently at version 0.0.3.

ga SQL parser for Delphi 5, Delphi 6 and Kylix ga SQL parser for Delphi 5, Delphi 6 and Kylix. GPL license. A fairly young project.

Other SQL Programming Interfaces and Tools

Other systems.

SQL Fairy
SQL Fairy is a tool for converting between one SQL dialect and another. It is also capable of generating documentation, HTML and XML as output. Written in perl, GPL license.

The SQSH SQL Shell for Unix provides a powerful interface for Sybase and Microsoft SQL databases. Basically allows extensive shell scripting.

EasySQL provides a uniform interface library (API) to MiniSQL and MySQL. Non-free, shareware, includes source code.

MultiBase is a 4GL and development environment for the rapid deployment of SQL applications.

Vortex from Trifox offers a uniform API/concurrent access tool to a large number of databases, including most major SQL vendors, as well as flat-file ADABAS C, ISAM and FoxBase, dBase databases.

In particular, take a look at:

Both support Adabas D.

Recital SDK
Recital offers an SDK (Software Developers Kit)

Documentation, Other Notes


Last Updated June 2003 by Linas Vepstas

Copyright (c) 1996-2003 Linas Vepstas.

Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.1; with no Invariant Sections, with no Front-Cover Texts, and with no Back-Cover Texts. A copy of the license is included at the URL, the web page titled "GNU Free Documentation License".

All trademarks belong to their respective owners.
Go Back to the Enterprise Linux(TM) Page
Go Back to the Linas' Home Page