UCL WIKI

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

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.

Install 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
Check contents of /etc/odbcinst.ini are as follows
$ cat /etc/odbcinst.ini
[ODBC Drivers]
MySQL = Installed

[MySQL]
Driver = /usr/lib/odbc/libmyodbc.so
Setup  = /usr/lib/odbc/libodbcmyS.so
Add 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

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

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

SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_database_name                                         |
+-----------------------------------------------------------------+
|...                                                              |
|                                                              ...|
+-----------------------------------------------------------------+
Quit isql
SQL> quit
$ _
Launch 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

https://wiki.ucl.ac.uk/x/sIaLAQ