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

odbc_on_mac.md

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

Install brew

https://brew.sh

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")