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:
- Install libmyodbc
- Install unixODBC
- Configure ODBC
- Test the connection
- 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