mysql, ODBC, STATA 16 on MAC (M1 Silicon)

1. Setup ODBC on your Mac (including Apple Silicon)

Install brew

unixodbc package from brew works for Apple Silicon

See https://formulae.brew.sh/formula/unixodbc#default

Install unixodbc with brew:

brew install unixodbc

2. Setup a connector for mysql

The MariaDB connector is a drop in replacement for the mysql-connector and works for Apple Silicon.

Install MariaDB connector with brew:

brew install mariadb-connector-odbc

3. Configure ODBC Drivers and User DSN

You can inspect the ODBC config files locations:

odbcinst -j

You can edit the files manually but its easier to use ODBC Manager. Download and install ODBC Manager from:

http://www.odbcmanager.net

Using ODBC Manager

Drivers Driver: MariaDB ODBC 3.1 Driver File: /opt/homebrew/Cellar/mariadb-connector-odbc/3.1.15/lib/mariadb/libmaodbc.dylib

User DSN SERVER: localhost PORT: 3307 CHARSET: utf8 DATABASE: <database> USER: <user>

4. SSH tunnel to remote mysql server

ssh -fN -L 3307:localhost:3306 `<remote server>`

if you are using 3307 for something else, use any of the higher port number.

5. Test remote ODBC connection

You can use isql to test your ODBC connection.

isql -v REMOTE_DB <user> <passwd>

6. Opening STATA

macos will not set the env variable LD_LIBRARY_PATH even if you export to path in your profile (.zshrc) (See also about SIP https://support.apple.com/en-us/HT204899).

To get past this load STATA from the command line and set the LD_LIBRARY_PATH at the same time:

export LD_LIBRARY_PATH=/opt/homebrew/lib/:/opt/homebrew/maridb/ && \
/Applications/Stata/StataIC.app/Contents/MacOS/StataIC &

7. ODBC in STATA

List your ODBC connections

. odbc list

Data Source Name                   Driver
-------------------------------------------------------------------------------
ODBC                               
REMOTE_DB                        /opt/homebrew/Cellar/mariadb-connector-odbc
-------------------------------------------------------------------------------

Authenticate ODBC connection:

Go to File | Import | ODBC data sources
Click on Authentication at the top right of the ODBC data sources dialog
enter user and password
select the data source
close the dialog

Run your query using an ODBC connection:

odbc load, exec("selec * from mytable") clear dsn("REMOTE_DB")

erikvw/odbc_on_mac.md