Menu:

Support:

The development of tools like nipper requires access to devices, documentation and configuration files. If you have access to any of those items, please consider donating them to the project. I will be happy to take into account any special confidentiality requirements.

Monetary donations are gratefully recieved and can be made through the Nipper Source Forge pages (click here).

Latest:

- Nipper
- BannerGrab
- SSLScan
- SunPatchCheck
- Viewstate
- Data Sources
- IP Calc
- Manglefizz
- ODBC Stats
- SQL Query

Licence:

The software downloadable from this site are covered by GPL and LGPL versions 2 and 3, depending on the package. Check the licence included with each package for specific details.

Data Sources

Data Sources is a graphical ODBC configuration tool for UNIX systems, similar to the Windows data source control pannel applet. It allows users to easily configure connections to databases and enables administrators to quickly configure ODBC drivers and system data sources.

Data Sources provides a user interface which is similar to other ODBC configuration tools and should be instantly familiar to users of those tools.

Contents

Requirements

Data Sources has the following requirements:

Although Data Sources has only been tested with unixODBC, it will likely work with other ODBC libraries, such as those on Windows systems.

Installation

To build Data Sources, first extract the archive and change into the source directory. The build process follows the standard CMake build process:

ODBC Introduction

Open DataBase Connectivity (ODBC) provides a database independent access layer for applications. This means that an ODBC application does not need to know how to communicate with a database. An ODBC application uses the ODBC libraries to make requests and ODBC handles how the data is retrieved.

The diagram below provides a brief overview of ODBC.

The ODBC libraries make use of database drivers to communicate with the various database servers. Some database drivers are provided with unixODBC, others are provided by third parties. ODBC data sources are configured to access specific databases using the installed ODBC drivers. An ODBC application acesses a database using a named data source without needing any knowledge of how the data is stored.

Getting Started

ODBC data sources use ODBC drivers in order to connect to different types of database. Therefore, before any data sources can be created ODBC drivers will need to be added and configured. This section describes what need to be done before you can start configuring data sources.

ODBC Driver Automatic Setup

If an ordinary user were to use ODBC Data Sources to configure a data source without any ODBC drivers being configured they would recieve a warning. ODBC drivers need to be added before a data source can be configured for use with a driver, and only the root user is able to configure drivers.

When the ODBC Data Sources program is started as a root user, it looks for common ODBC drivers in the common ODBC driver locations. If ODBC Data Sources finds any drivers that have not already been added for use with ODBC, it will ask if you would like them to be added. This is the easiest and quickest way of adding and configuring ODBC drivers. The ODBC drivers will then be available for use with data sources.

Database Drivers

ODBC-based applications use data sources to access the data stored in databases. Data sources use ODBC drivers to communicate with a wide variety of databases. Therefore, before a data source can be configured for an application to access a particular database, a driver must first be configured. A number of ODBC drivers are supplied with unixODBC, others can be obtained from the database vendor or a third party (open source or commercial).

This section describes how to add ODBC drivers, configure them and remove them. All of the functionality described in this section is provided on the drivers tab of ODBC Data Sources (see screenshot below).


Adding Drivers

Only the root user is able to add ODBC drivers, users are limited to viewing an ODBC drivers configuration. The "Add" button is used to add ODBC drivers and starts a wizard (see screenshot below). ODBC Data Sources will look for missing drivers that it knows about and display them alongside the two template options (Server-Based and File-Based Drivers).

NOTE: If you select one of the detected driver options and do not select the "Expert" check there are no more steps to take, the driver will be added.

The next stage allows you to configure the driver name, description and the driver file(s) (see screenshot below). Driver libraries are commonly stored in /usr/lib on unix-based systems. Driver setup libraries are usually named similar to the driver, but with a "S" at the end of the name, see the driver documentation for more information.

NOTE: Even though it is not essential to specify a driver setup library, it is preferable if one is specified as ODBC Data Sources makes use of the setup library to offer advanced configuration options.

The last stage of the "Add ODBC Driver" wizard will let you know whether the driver was successfully added.

NOTE: Some drivers, such as the DB2 and Informix ones, have specific configuration peculiarities and the documentation provided with the driver should be read first. If you are having problems with a specific driver, the unixODBC project may have a configuration guide for your driver.


Configuring / Viewing Drivers

ODBC drivers can only be configured by the root user. However, any user can view an ODBC drivers configuration. The view or configure buttons (depending on the user) are only enabled when a driver has been selected


Remove Drivers

Only the root user is able to remove ODBC drivers. As the root user, the "Remove" button is enabled when a driver has been selected. ODBCConfig will ask for confirmation of the driver removal before removing it. Additionally, data sources which use the driver and the root user has access to can also be removed.

NOTE: The remove data source option may not work if the version of unixODBC is 2.2.11. A patch has been submitted to the project that fixes this problem in newer versions (when they are released).

Obtaining Drivers

A number of ODBC drivers are supplied with unixODBC, these include:

Further ODBC drivers can usually be obtained from the database provider or from a open source ODBC driver project. These drivers include:

The unixODBC project web site contains a larger list of available drivers, including commercial drivers.

Data Sources

ODBC data sources are used by ODBC-based applications to access database data. There are three types of data source (DSN).

User Data Sources (User DSN)

User data sources can be created, modified and deleted by any user. User DSN are personal to that user and not visible to other users. The user data source configuration is commonly stored in a file called ".odbc.ini" in the users home directory.


System Data Sources (System DSN)

A system data source can only be created, modified and deleted by the roor user. The system data source is available for all users on the system and users can view the system data source configuration. The system data source configuration is commonly stored in a file called "odbc.ini" in the "/etc/unixODBC/" directory.


File Data Sources (File DSN)

A file data source is available to all users who can access the directory it is stored within. If a file data source is configured on a shared network filesystem then it will be available to all users on the network who can access the shared filesystem. The root user can create, modify and delete file data source. However, it maybe possible for users to manually modify a file data source, depending on the users file permissions. The default directory for the creation of file data sources is commonly the "/etc/unixODBC/ODBCDataSources" directory.


Adding Data Sources

From the data source tab that you want to configure a datasource for, click the "Add" button. Adding a data source is done using a wizard. On the first stage of the wizard you must select the ODBC driver to use with the data source.

The configuration options differ for each ODBC driver, different databases require different settings. The settings shown in the screenshot below are for the Postgres ODBC driver.


Testing Data Sources

A data source configuration can be tested to confirm that it is correctly configured. Testing functionality is provided in the main ODBC Data Sources dialog and from the add data source wizard. The former requires a data source to be selected in the data source list. Once the "Test" button has been selected, you may be prompted to login, if login details are required.

If a connection to the database is successfully made you will see a "Successfully connected" message. If not then either the authentication details were incorrect, or the data source needs reconfiguration (or maybe the database server is not available).

Debug Options

The debug options should all be turned off by default and can consume a large chunk of disk space. Due to the disk space issues with creating a log of everything there is a "Turn Off" button to quickly turn debugging off. Only the root user is able to modify the debug settings.

Trace

The trace options will log all ODBC calls to the specified file. Enabling this option can consume a large portion of disk space, depending on the ODBC activity on the system.

Connection Pooling

Connection pooling is usually only used on servers, such as web servers that need access to a database server. However, connection pooling has security issues and should be disabled if not used.

ODBC Development

This is only intended as a brief overview of ODBC development and assumes the use of C as the development language. For a more thorough ODBC development guide, visit www.unixodbc.org.

Overview

A basic ODBC application that performs a simple query will do the following:

Compiling ODBC C programs

To compile an ODBC source file called "odbc.c" into the executable "odbc", use the following command:

gcc -o odbc -lodbc odbc.c
Creating / Freeing ODBC handles

Before connecting to a database using ODBC you must first create a environment and a connection handle.

// Create environment handle...
returnCode = SQLAllocEnv(&envHandle);

The ODBC environment structure holds the specifications of the ODBC environment, such as the current ODBC version. An ODBC connection handle is also required.

// Allocate connection handle...
returnCode = SQLAllocConnect(envHandle, &conHandle);

Using the connection handle, settings such as the connection timeout can be modified. The return value from these commands and odbc commands need to be checked to see if they succeeded.

if ((returnCode == SQL_SUCCESS) ||
       (returnCode == SQL_SUCCESS_WITH_INFO))
{
    // Success code goes here
}

All of the handles need to be released when no longer required.

// Free connection handle...
SQLFreeHandle(SQL_HANDLE_DBC, conHandle);
  
// Free environment handle...
SQLFreeHandle(SQL_HANDLE_ENV, envHandle);
Connecting to a data source

The connect function requires the name of the data source, the data source username and password (if required).

// Connect...
returnCode = SQLConnect(conHandle, (SQLCHAR*)"web", SQL_NTS,
          (SQLCHAR*)"postgres", SQL_NTS, (SQLCHAR*)"", SQL_NTS);

The code above uses a data source called "web" with the username "postgres" and no password. Once the data source connection is no longer required it must be disconnected.

// Disconnect...
SQLDisconnect(conHandle);
Performing a query

Before performing a query a statement handle needs to be made. The statement handle is then used to make the query and retrieve the results. After the statement is no longer required it must be released to allow ODBC to free resources.

// Allocate statement handle...
 returnCode = SQLAllocStmt(conHandle, &stateHandle);

Once a statement handle has been obtained a query can be executed. First the SQL statement is set, then executed.

// Prepare statement...
returnCode = SQLPrepare(stateHandle, (SQLCHAR*)"select *
    from user", SQL_NTS);
 
// Execute statement...
returnCode = SQLExecute(stateHandle);

Once finished with the SQL statement and ODBC connection they should be released and disconnected.

// Free statement handle...
SQLFreeHandle(SQL_HANDLE_STMT, stateHandle);
 
// Disconnect...
SQLDisconnect(conHandle);
Retrieving the query results

There are a number of functions that can be used to access the results of the ODBC query.

// Get number of columns...
returnCode = SQLNumResultCols(stateHandle, &colNum);
 
// Get number of rows...
returnCode = SQLRowCount(stateHandle, &rowNum);
 
// Fetch a row of results
returnCode = SQLFetch(stateHandle);
 
// Get data from the results
returnCode = SQLGetData(stateHandle, currentCol, SQL_C_CHAR,
       (SQLPOINTER)sqlValue, sizeof(sqlValue), &indicator);
A simple ODBC application

Here is the source code for a very simple ODBC application (there are lots of comments to help out). It makes use of a data source called "web" and uses a username called "postgres" with no password.

Downloads

The latest version of Data Sources can be found in the KDE SVN repository, in trunk/playground/base/datasources.

Data Sources downloads.
VersionDescriptionMD5
0.9.1Source codee3691f6f4a63d325eff229e690e7e863

Changes

Note   : Check Subversion commits for latest updates
         Ian Ventura-Whiting (Fizz)

Version: 0.9.2
Author : Ian Ventura-Whiting (Fizz)
Date   : 26/11/2006
Changes: Feature : Auto-Detection of ODBC Drivers now
                   supports:
                   * MySQL ODBC 2.50 Driver
                   * MySQL ODBC 3.51 Driver
                   * FreeTDS Sybase and MS-SQL Driver
                   * Oracle Driver
                   * IBM DB2 Driver
                   * PostgreSQL Driver
                   * Mini SQL Driver
                   * Mimer SQL Driver
                   * Text Driver
                   * Internet News Driver
                   * Easysoft Interbase Driver
                   * Easysoft Firebird Driver
                   * Informix Driver
                   * SQLite Driver
                   * DataDirect 4.20 DB2 Wire Driver
                   * DataDirect 4.20 Informix Wire Driver
                   * DataDirect 4.20 Oracle Wire Driver
                   * DataDirect 4.20 SQL Server Wire Driver
                   * DataDirect 4.20 Sybase Wire Driver
                   * SAP Driver
                   * OpenLinks iODBC Driver
                   * Easysoft ODBC-ODBC Bridge
                   * Easysoft ODBC-ODBC Bridge (MT)
                   * Easysoft ODBC-JDBC Gateway
                   * Easysoft Oracle Driver
                   * Easysoft Sybase Driver
         Fixed   : License (now LGPL)
         Fixed   : Manually adding ODBC driver
         Fixed   : User viewing expert options

Version: 0.9.1
Author : Ian Ventura-Whiting (Fizz)
Date   : 08/10/2006
Changes: Fixed   : Test data source regardless of whether
                   the user clicked cancel.
         Fixed   : CMake warning.
         Fixed   : Non-editable QComboBox.

Version: 0.9
Author : Ian Ventura-Whiting (Fizz)
Date   : 03/10/2006
Changes: This product has been renamed to Data Sources
         from ODBCConfig. This version of Data Sources
         is Qt4-based, only minor updates and patches
         will be applied to the Qt3-based version
         (0.8.x). The version 1 release is targetted to
         be KDE 4-based and part of a suite of Database-
         related tools.
         Feature : Warns non-root users if no ODBC drivers
                   have been configured.
         Feature : For root users, detects uninstalled
                   drivers at startup.
         Feature : Full wizard-based creation of ODBC
                   drivers and Data Sources.
         Feature : Test data sources from the wizard.
         Feature : Qt4 rewrite!
         Feature : QMake & CMake build system support
         Improved: "Whats this" help.
         Improved: Documentation

Version: 0.8
Author : Fizz
Date   : 08/12/2005
Changes: Feature : Added Test DSN functionality.
         Feature : Configure / View data sources which
                   have no driver setup library.
         Feature : Manual added.
         Improved: Added unixODBC contributors to about
                   dialog.
         Improved: Allows configuration of additional
                   data source properties.
         Improved: Allows configuration of additional
                   driver properties.
         Improved: Better default for file DSN path.
         Improved: Debug options "Whats this" and
                   "Tool tips" text.

Version: 0.7
Author : Fizz
Date   : 01/12/2005
Changes: Feature : Added 64 bit support.
         Improved: Added additional driver properties.

Version: 0.6
Author : Fizz
Date   : 27/11/2005
Changes: Feature : Added debug functionality.
         Improved: File DSN for users.
         Fixed   : Issue with system DSN introduced after
                   file dsn changes were made.

Version: 0.5 (pre-submission version)
Author : Fizz
Date   : 26/11/2005
Changes: Feature : Auto-Detection of ODBC Drivers
                       Firebird (Easysoft Driver)
                       Informix
                       SQLite
         Improved: Search a list of common library locations
                   for the driver and setup libraries.
         Improved: Added dialog auto-resize to fit contents.
         Improved: Useful help in error message boxes.
         Improved: About dialog and included license.
         Improved: Added license details to all C++ files.
         Improved: Added license file to the source folder.
         Fixed   : A problem removing file DSN introduced
                   with some of the file DSN changes.

Version: 0.4
Author : Fizz
Date   : 24/11/2005
Changes: Add features, improved stuff...
         Feature : Added File DSN support.
         Feature : Added "Whats this" text.
         Feature : Added "Tool tip" text.
         Feature : Auto detects ODBC drivers that are already
                   installed.
         Feature : ODBC Drivers Auto Setup.
         Improved: Added program and app icons (thanks to KDE).
         Improved: Improved the "add ODBC driver" result text.
         Improved: Remove expert check when not required.
         Fixed   : Blank DSN field saving.
         Fixed   : Driver setup for faulty setup library.

Version: 0.3
Author : Fizz
Date   : 12/10/2005
Changes: Add features, improved stuff...
         Feature : Advanced data source configuration.
         Improved: Data source configuration labels.
         Improved: Changed name from ODBCDataSources to
                   ODBCConfig for planned replacement of
                   the unixODBC tool.
         Improved: File browse widget added to data sources
                   configuration.
         Improved: Added remaining error message boxes.

Version: 0.2
Author : Fizz
Date   : 02/10/2005
Changes: Add features, improved stuff, fixed stuff...
         Feature : Double-click on list items to view/configure.
         Feature : Option to remove related DSN when removing
                   the driver (Functionality submitted to the
                   unixODBC project).
         Feature : Auto-Detection of ODBC Drivers
                       Mimer SQL
                       MySQL (MyODBC 2.5 Driver)
                       Oracle
                       IBM DB2
                       Interbase (Easysoft Driver)
                       Mini SQL
         Feature : Setup libraries for server and file data
                   sources.
         Improved: DSN and driver view/config dialogs.
         Improved: Dialog title text.
         Fixed   : Widget tab order and default buttons.

Version: 0.1
Author : Fizz
Date   : 01/10/2005
Changes: Initial Release, features include...
         Feature : Add/Remove ODBC Drivers.
         Feature : Auto-Detection of ODBC Drivers
                       PostgreSQL
                       MS-SQL & Sybase (FreeTDS Driver)
                       MySQL (MyODBC 3.51 Driver)
                       Text
                       Internet News
         Feature : Easy ODBC Driver Setup.
         Feature : Configure/View ODBC Drivers.
         Feature : Add/Remove User and System Data Sources.
         Feature : Configure/View User and System Data Sources.

License

The latest version of Data Sources is covered by LGPL version 2. You can get a copy from here. Previous versions were licensed using GPL version 2, you can obtain a copy from here.