Querying MySQL from Stata

Step 0: Install the ODBC driver

  1. Download and install the iODBC driver manager. The next step will fail if this isn't installed first.
  2. Download and install the ODBC driver.

Step 1: Set up ODBC driver

See these instructions. My ODBC configuration (~/.odbc.ini) looks like:

[ODBC Data Sources]
mysql = MySQL ODBC 5.3 ANSI Driver

[ODBC]
Trace         = 0
TraceAutoStop = 0
TraceFile     = 
TraceLibrary  = 

[mysql]
Driver      = /usr/local/lib/libmyodbc5a.so
Description = desc here
SERVER      = localhost
PORT        = 3306
USER        = root
PASSWORD    = passwordhere
DATABASE    = databasehere

Make sure your driver location is correct. It could also be something like /usr/local/mysql-connector-odbc-5.3.7-macos10.12-x86-64bit/lib/libmyodbc5a.so.

Step 2: Make a ODBC call from Stata

For example:

clear
set odbcdriver ansi
odbc list
// Option 1
odbc load, table("tablename") dsn("mysql") clear noquote
// Option 2
odbc load, exec("select * from tablename") dsn("mysql") clear noquote

Using a plugin is another option, but I haven't tried this.

Update: Loading in SQL from an external file

This allows you to do something like:

cd "/path/to/your/folder"
clear
loadsql using your-sql.sql, dsn(mysql)