How do I set up an ODBC Data Source Name for Stata on Mac or Linux/Unix?
Configuring ODBC for Mac and Linux/Unix
Kevin S. Turner, StataCorp
April 2003, updated August 2007
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.
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.
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.
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
- 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
- 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.
Important: Make sure you are using the correct driver for
your database. For example, if you have a PostgreSQL database, you will need
to use the PostgreSQL ODBC driver, or you will not be able to access the
database. Drivers for most of the popular databases, such as Oracle and
MySQL, may be available when you install the iODBC driver manager, or you
can download the appropriate driver from your database vendor’s website.
You can configure the ODBC data source either by using the iODBC
Administrator utility or by modifying the ODBC configuration files manually.
- iODBC Administrator Utility
On both the Mac and Linux platforms, you can configure an ODBC data
source using the iODBC Administrator (see
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
- 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
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
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
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.
Stata for Windows
Stata for Unix
Stata for Mac