Skip to content

Instantly share code, notes, and snippets.

@JonathanWillitts

JonathanWillitts/odbc_on_mac.md

Forked from erikvw/odbc_on_mac.md
Last active March 29, 2024 09:28
Show Gist options
  • Save JonathanWillitts/7b5a519bd40dd730b98ce1ad75e859e8 to your computer and use it in GitHub Desktop.
Save JonathanWillitts/7b5a519bd40dd730b98ce1ad75e859e8 to your computer and use it in GitHub Desktop.
Download ZIP
mysql, ODBC, STATA 16 on MAC (M1 Silicon and Intel-based)
Raw
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 it's easier to use ODBC Manager. Download and install ODBC Manager from:

http://www.odbcmanager.net

Using ODBC Manager

Drivers

  1. Under 'Drivers', choose 'Add...' to add a new DB driver
  2. Enter at a minimum: 
    Driver Name: MariaDB ODBC 3.1 Driver
Driver File (Apple Silicon): /opt/homebrew/Cellar/mariadb-connector-odbc/3.1.15/lib/mariadb/libmaodbc.dylib
           or (Intel-based): /usr/local/Cellar/mariadb-connector-odbc/3.1.15/lib/mariadb/libmaodbc.dylib

User DSN

  1. Under 'User DSN', choose 'Add...' to add a new data source
  2. Select the 'MariaDB ODBC 3.1 Driver'
  3. Enter a Data Source Name (DSN) (recommend to use same as remote DB name)
  4. Configure the DSN, with the following keyword/values: 
    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 numbers.

5. Test remote ODBC connection

You can use isql to test your ODBC connection.

isql -v <dsn_name> <user> <passwd>
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_<database>                                            |
+-----------------------------------------------------------------+
| table_one                                                       |
| table_two                                                       |
| ....                                                            |
+-----------------------------------------------------------------+

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:

# On Apple Silicon Macs
export LD_LIBRARY_PATH=/opt/homebrew/lib/:/opt/homebrew/mariadb-connector-odbc/ && \
/Applications/Stata/StataIC.app/Contents/MacOS/StataIC &

# On Intel-based Macs
export LD_LIBRARY_PATH=/usr/local/Cellar/:/usr/local/Cellar/mariadb-connector-odbc/ && \
/Applications/Stata/StataIC.app/Contents/MacOS/StataIC &

# Note, possible alternative Stata install locations:
# - /Applications/Stata/StataSE.app/Contents/MacOS/StataSE
# - /Applications/Stata/StataMP.app/Contents/MacOS/StataMP

7. ODBC in STATA

Specify ODBC driver manager

# For this session only
set odbcmgr unixodbc

# Permanantly
set odbcmgr unixodbc, permanently

List your ODBC connections

. odbc list

Data Source Name                   Driver
-------------------------------------------------------------------------------
ODBC                               
<dsn_name>                        /opt/homebrew/Cellar/mariadb-connector-odbc (Apple Silicon)
<dsn_name>                        /usr/local/Cellar/mariadb-connector-odbc/ (Intel-based)
-------------------------------------------------------------------------------

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("select * from mytable") clear dsn("<dsn_name>")
Raw
odbc_stata_on_mac_quickstart.md

Quickstart guide to configure ODBC driver on Mac for use with Stata

Condensed version of: odbc_on_mac.md, adopted from @erikvws original Gist

# Install Homebrew - https://brew.sh/
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

# Install unixodbc - https://formulae.brew.sh/formula/unixodbc#default
brew install unixodbc

# Install MariaDB ODBC connector - https://formulae.brew.sh/formula/mariadb-connector-odbc#default
brew install mariadb-connector-odbc

# Download, install and configure ODBC Manager, from: http://www.odbcmanager.net 
   # Add new DB driver:
   #    Driver Name: MariaDB ODBC 3.1 Driver
   #    Driver File: /opt/homebrew/Cellar/mariadb-connector-odbc/3.1.15/lib/mariadb/libmaodbc.dylib
   #             or: /usr/local/Cellar/mariadb-connector-odbc/3.1.15/lib/mariadb/libmaodbc.dylib
   
   # Add Data Source Name (DSN) for each remote db to be connected to:
   #    1) Under 'User DSN', choose 'Add...' to add a new data source
   #    2) Select the 'MariaDB ODBC 3.1 Driver'
   #    3) Enter a Data Source Name (DSN) (recommend to use same as remote DB name)
   #    4) Configure the DSN, with the following keyword/values:
   #            SERVER: localhost
   #            PORT: 3307
   #            CHARSET: utf8
   #            DATABASE: <database>
   #            USER: <user>

# Create ssh connection to DB server:
ssh -fN -L 127.0.0.1:3307:127.0.0.1:3306 <remote_user>@<remote_server> 

# Test SSH connection/ODBC driver:
isql -v <dsn_name> <user> <passwd>
+---------------------------------------+
| Connected!                            |
...
+---------------------------------------+
SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_<database>                                            |
+-----------------------------------------------------------------+
| ....                                                            |
+-----------------------------------------------------------------+


# Launch Stata:
# On Apple Silicon Macs
export LD_LIBRARY_PATH=/opt/homebrew/lib/:/opt/homebrew/mariadb-connector-odbc/ && \
/Applications/Stata/StataIC.app/Contents/MacOS/StataIC &

# On Intel-based Macs
export LD_LIBRARY_PATH=/usr/local/Cellar/:/usr/local/Cellar/mariadb-connector-odbc/ && \
/Applications/Stata/StataIC.app/Contents/MacOS/StataIC &

# Note, possible alternative Stata install locations:
# - /Applications/Stata/StataSE.app/Contents/MacOS/StataSE
# - /Applications/Stata/StataMP.app/Contents/MacOS/StataMP


# Set ODBC driver manager
set odbcmgr unixodbc, permanently

# Test ODBC in Stata Command Window:
. odbc list

# Authenticate connection to DB:
# Via:  File | Import | ODBC data sources
#  or:  By setting `password` keyword/value in DSN

# Test loading date, e.g.,
odbc load, exec("select * from mytable") clear dsn("<dsn_name>")

# or
odbc load, table("mytable") noquote


# Final note: To kill SSH connection once finished, use:
pkill -f "ssh -fN -L 127.0.0.1:3307:127.0.0.1:3306 <remote_user>@<remote_server>"
Raw
start_ssh_tunnel.sh
################################################################################
# Creates SSH tunnel between Mac/UNIX-based OS and remote server.
#
# Configure `remote_user` and `remote_host` before using.
#
# Usage: start_ssh_tunnel.sh
#
################################################################################
# User configurable settings:
remote_user=
remote_host=
# Connection settings:
local_bind_address=127.0.0.1
local_port=3307
destination_host=127.0.0.1
destination_port=3306
# Create SSH tunnel to server
echo "Creating ssh tunnel (press Ctrl + C at any time to disconnect/quit) ..."
ssh -N ${remote_user}@${remote_host} \
-L ${local_bind_address}:${local_port}:${destination_host}:${destination_port} \
-o ServerAliveInterval=120
Raw
stata_odbc.sh
################################################################################
# Creates SSH tunnel between Mac/UNIX-based OS in background, and runs Stata.
#
# Configure `remote_user`, `remote_host`, `odbc_lib_path` and `stata_path`
# before using.
#
# Usage: stata_odbc_mac.sh
#
# Note: SSH tunnel will be terminated on exiting Stata.
#
################################################################################
# User configurable settings:
remote_user=
remote_host=
# Connection settings:
local_bind_address=127.0.0.1
local_port=3307
destination_host=127.0.0.1
destination_port=3306
# Configure LD_LIBRARY_PATH for Apple Silicon or Intel-based Macs:
# Uncomment for Intel-based Macs:
odbc_lib_path=/usr/local/Cellar/:/usr/local/Cellar/mariadb-connector-odbc/
# Uncomment for Apple Silicon Macs
# odbc_lib_path=/opt/homebrew/lib/:/opt/homebrew/mariadb-connector-odbc/
# Stata Path
stata_path=/Applications/Stata/StataIC.app/Contents/MacOS/StataIC
# stata_path=/Applications/Stata/StataSE.app/Contents/MacOS/StataSE
# stata_path=/Applications/Stata/StataMP.app/Contents/MacOS/StataMP
# Create SSH tunnel to server, configure library path and launch Stata
ssh -fN -L ${local_bind_address}:${local_port}:${destination_host}:${destination_port} ${remote_user}@${remote_host}
export LD_LIBRARY_PATH=${odbc_lib_path} &&
"${stata_path}" && \
pkill -f "ssh -fN -L ${local_bind_address}:${local_port}:${destination_host}:${destination_port} ${remote_user}@${remote_host}"
@samreshkr
Copy link

I tried opening Staata using step 6:

export LD_LIBRARY_PATH=/usr/local/Cellar/:/usr/local/Cellar/psqlodbc/ && \ 
/Applications/Stata/StataMP.app/Contents/MacOS/StataMP &

ODBS is for postgresql and Stata location is MP instead of ic. Everything else is exactly the same. I get the following errors:

set odbcmgr unixodbc    

-set odbcmgr- not allowed; 'odbcmgr' not recognized
r(199);


. odbc list
The ODBC file libiodbc.dylib could not be found on this system.    
Setting the unix LD_LIBRARY_PATH environment variable may correct this error.
r(680);

For reference I am using Stata13. Am I going wrong somewhere?

@JonathanWillitts
Copy link
Author

I'm sorry I've just tried a quick search and coudn't find anything.

'odbcmgr' not recognized sounds like maybe odbcmgr isn't an option of Stata 13 (but I can't confirm this).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment