Use JDBC+Mysql in Stata

Published by Michael Ewens

The new M1* Macs do not play well with the OBDC connectors for mysql or postgres in Stata. Fortunately, Stata 17 introduced the jdbc connection option, so I gave it a shot. It was a pain, so here is how I did it:

Step 0: Install Java on your Mac. I assume you have Mysql installed on your system.

Step 1: Download the JDBC connector from Oracle. Use the “Platform Independent” one.

Step 2: Unzip the file.

Step 3: Go to Finder, then “Go to Folder”, then go to /Library/Java/Extensions

Step 4: Copy the .jar from Step 2 into this folder.

Step 5: (this one may be the one that works for Stata). Add a copy of the same ,jar to your personal ado. Run “sysdir” to find your personal ado (you might have to create the folder). For me, I went to ~/Library/Application Support/Stata/ado/personal and pasted a copy of the .jar here.

Step 6: Add a class path so that your system knows about this new driver (this may not be needed). Open up your terminal and type

echo $CLASSPATH

If nothing shows up, then run

export CLASSPATH=/Library/Java/Extensions/mysql-connector-java-8.0.29.jar

[replace the name of the jar file if it is different. If you have non-missing for the first echo, then run “export CLASSPATH=${CLASSPATH}:/Library/Java/Extensions/mysql-connector-java-8.0.29.jar” (no quotes)

Step 7: Open up Stata.

Step 8: Suppose you have a database on your system called “Research” and mysql is running. Then type this command into Stata:

jdbc connect, jarpath("/Library/Java/Extensions/") driverclass("com.mysql.cj.jdbc.Driver") url("jdbc:mysql://localhost:3306/Research?zeroDateTimeBehavior=convertToNull") user("USER") password("PASS")

replacing “Research”, “USER” and “PASS”. Notice that condition on the url is important if you have ‘0000-00-00’ dates in your tables.

Step 8: If no errors, then run “jdbc showtables” and you should see a bunch of tables.

Step 10: Load in some data: “jdbc load, exec(“SELECT distinct entityid FROM deals”)”

Step 11 (optional): Add

jdbc connect, jarpath("/Library/Java/Extensions/") driverclass("com.mysql.cj.jdbc.Driver") url("jdbc:mysql://localhost:3306/Research") user("USER") password("PASS")

to an .ado file in your personal ado path called “runFirst.ado”. In all do files that need to access this DB, just add “runFirst” to the top of the do file so the JDBC connection to “Research” is available.