How do I set up an ODBC Data Source Name for Stata on Mac or Linux/Unix?

Configuring ODBC Data Source Names (DSNs) for the Mac OS X and Linux/Unix platforms can be daunting for beginners, so this FAQ provides an overview of the steps you need to take. Most steps are common to both platforms, but some are specific to the database to which you are connecting. Before you start configuring the driver manager, make sure you know which database you are using.

Obtaining iODBC

On both Mac and Linux, make sure that your system is running the iODBC driver manager, which stores and manages all the information you need to connect to an ODBC data source. iODBC is an Open Source driver manager maintained by OpenLink Software and released under a dual LGPL/BSD license.

• Linux
  The good news is that most versions of Linux ship with iODBC installed either by default or as an option. If you need to install iODBC, see the documentation for your version of Linux, or, if you are comfortable compiling source code, see http://www.iodbc.org for the latest version as well as some generic RPMs.
• Mac OS X
  Mac OS X versions 10.2 and later ship with an iODBC utility, ODBC Administrator, which you can find under Applications:Utilities.

Obtaining unixODBC

If you are using a Linux/Unix machine and Stata 10 or later, you can also connect to a database with the unixODBC driver manager. In Stata, type set odbcmgr unixodbc to use the unixODBC driver manager. To change back to the default iODBC driver manager, type set odbcmgr iodbc.

Creating and configuring an ODBC Data Source

At this point, you should have either iODBC or unixODBC installed and running on your system. To configure an ODBC DSN, you will need to specify several parameters, which may vary, depending on the database. Here are a few of the most common parameters (items in bold are usually required):

• DSN: a DSN of your choosing.
• Database name: the name of the existing database.
• Driver: the driver you will use to communicate with the database (make sure you use the right driver for your database).
• Server name: the domain name or IP address of the server that hosts the database.
• Username: the username you are using to connect to the database.
• Password: the password corresponding to the username.
• Port number: the port number you are using to connect to the database.

You can configure the ODBC data source either by using the iODBC Administrator utility or by modifying the ODBC configuration files manually.

1. iODBC Administrator Utility
   On both the Mac and Linux platforms, you can configure an ODBC data source using the iODBC Administrator (see http://www.iodbc.org for instructions on downloading and installing this utility). Using the iODBC Administrator, you can configure the following DSN information:
   
   
   • System DSN: An ODBC data source that you want to make available to anyone using the computer.
   • User DSN: A data source specific to a user.
   • Drivers: Links to the available database drivers. For example, if more than one DSN is connecting to a PostgreSQL database, you can define a Driver for all the DSNs to reference. If the driver location ever changes, you can modify the driver information, and all the DSNs will automatically refer to the new location.
2. Modifying ODBC configuration files
   The best way to configure ODBC is to modify the configuration files directly. These configuration files are well documented on the iODBC and unixODBC website. The most important configuration file is the .odbc.ini file (the period that precedes the filename makes this file invisible to most directory listings).
   
   The .odbc.ini file contains the connection parameters for system and user DSNs. The .odbc.ini file for System DSNs is usually stored in the system directory. For user DSNs, it is always stored in the user’s home directory, and there are normally as many .odbc.ini files as there are users.
   
   Here is an example of an actual .odbc.ini that will work on either Mac or Linux/Unix for a PostgreSQL database named 'Bug'. When placed in the user’s home directory, this file provides access to the ODBC database specified:

    [ODBC Data Sources]
    Bug = PostgreSQL 
           
    [Bug]
    Debug = 1
    CommLog = 1
    ReadOnly = no
    Driver = /usr/local/lib/psqlodbc.so
    Servername = 64.xxx.xxx.xx
    FetchBufferSize = 99
    Username = kevin 
    Password = passwordforkevin
    Port = 5432
    Database = bugdb
    
    [Default]
    Driver = /home/kst/tmp/libiodbc-3.0.5/odbcsdk/lib/oplodbc.so.1
   

   The Driver, Servername, and Username parameters are common to all database types on both the Mac and Linux/Unix platforms. The names and required information for the other parameters may differ, depending on the type of database. See your database’s documentation for information about specific parameters.

Testing the ODBC DSN

Usually, iODBC ships with a small test application you can use to test the connection parameters of any DSN you define. Alternatively, you can start Stata and type odbc list to bring up a list of available DSNs. If you find any errors, you will need to change a connection parameter in the DSN before you try to connect again. I usually have to test a DSN two or three times before I get all the parameters right.