UCL WIKI

UCL Logo
Child pages
  • Connect STATA to MySQL using unixODBC on Ubuntu
Skip to end of metadata
Go to start of metadata

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