UCL Logo
Child pages
  • Connect STATA to MySQL using unixODBC on Ubuntu

Versions Compared


  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin

Getting STATA to connect to MySQL on Ubuntu isn't difficult so long as one critical step isn't missed out. I couldn't find this step on any guides so hopefully this will help others save some time on most 'NIX systems. This guide doesn't cover installing MySQL or STATA.

The steps are:

  1. Install libmyodbc
  2. Install unixODBC
  3. Configure ODBC
  4. Test the connection
  5. Connect STATA

Let's get started.

Code Block
titleInstall libmyodbc and unixodbc
$ sudo apt-get install libmyodbc
$ sudo apt-get install unixodbc
$ sudo apt-get install unixodbc-dev # The critical step - without this STATA ODBC does not work
Code Block
titleCheck contents of /etc/odbcinst.ini are as follows
$ cat /etc/odbcinst.ini
[ODBC Drivers]
MySQL = Installed

Driver = /usr/lib/odbc/libmyodbc.so
Setup  = /usr/lib/odbc/libodbcmyS.so
Code Block
titleAdd the MySQL database as a data source in ~/.odbc.ini
$ vi ~/.odbc.ini
[ODBC Data Sources]
database_name = MySQL # for simplicity I've used the database name here too

Description = A description
Driver      = MySQL
database    = database_name
server      = # Connecting on the same machine
user        = your_mysql_username
password    = your_password # optional - this is plain text!
Code Block
titleIf you store your database password in ~/.odbc.ini set the permission so only you have access
$ chmod 700 ~/.odbc.ini
Code Block
titleTest the connection
$ isql database_name
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |

You should now be able to run SQL commands try running:

Code Block
SQL> show tables;
| Tables_in_database_name                                         |
|...                                                              |
|                                                              ...|
Code Block
titleQuit isql
SQL> quit
$ _
Code Block
titleLaunch STATA and set the ODBC manager in the command window - permanently is optional
set odbcmgr unixodbc[, permanently]

Import the database into STATA by going to File -> Import -> ODBC data source

The database should be listed as an ODBC data source - make your selection. Done!

Anthony Thomas
MRC Centre of Epidemiology for Child Health
UCL, Institute of Child Health