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.
| Code Block |
|---|
| language | bash |
|---|
| title | Install libmyodbc and unixodbc |
|---|
| linenumbers | false |
|---|
|
$ 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 |
|---|
| language | bash |
|---|
| title | 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 |
| Code Block |
|---|
| language | bash |
|---|
| title | 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! |
| Code Block |
|---|
| language | bash |
|---|
| title | If you store your database password in ~/.odbc.ini set the permission so only you have access |
|---|
|
$ chmod 700 ~/.odbc.ini |
| Code Block |
|---|
| language | bash |
|---|
| title | Test the connection |
|---|
|
$ isql database_name
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> |
You should now be able to run SQL commands try running:
| Code Block |
|---|
SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_database_name |
+-----------------------------------------------------------------+
|... |
| ...|
+-----------------------------------------------------------------+
|
| Code Block |
|---|
| language | bash |
|---|
| title | Quit isql |
|---|
|
SQL> quit
$ _ |
| Code Block |
|---|
| title | 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
