Stata

Stata uses a generic connection protocol called ODBC to connect to databases. The instructions below are meant to provide guidance on configuring your environment as well as configuring Stata. Once you have been able to connect to your database, you should review available ODBC-specific Stata commands. The ODBC manual for Stata may be found at https://www.stata.com/manuals/dodbc.pdf. To help get you started, here are a few basic commands:

  • Load all data from a database table called MyTable, and specify the Data Source Name (dsn) as MyDataSourceName

odbc load, exec("select * from MyTable") dsn ("MyDataSourceName")

If you would like to load data from a table within another database, then replace MyTable with DATABASE.TABLE (please note the period between database name and table name) such as ProjectDB.ProjectTable

 

Stata on the Grid, interactive session

These instructions will guide you to connecting to a database while in an interactive Stata session. RCS already provides an appropriate ODBC driver on the Grid. However, you need to ensure you have an .odbc.ini configuration file in your Grid home directory. If you do not have this file, then you must first create it. To do so, please review the instructions found under the Configuration Files section (http://grid.rcs.hbs.org/db-configuration-files)

Once you have your local .odbc.ini file and have launched Stata within NoMachine, you should ensure the Stata ODBC manager parameter is set to unixODBC and that you can query available Data Source Names. Please enter the following Stata commands as shown in the screenshot below:

set odbcmgr unixODBC
odbc list

db-connecting-stata-query-dsn
 

 

Troubleshooting

If you get an error, then you may have previously tried to run an ODBC command in the current Stata session. The ODBC manager must be set correctly (i.e. to unixODBC) before any ODBC commands may be run. Otherwise, changes to the ODBC manager parameter will not take effect. To remedy this problem, please restart your Stata session and set the ODBC manager to unixODBC before trying any other commands. If you still get an erorr, then please contact Research Computing Services.

If you do not get any results after running odbc list and did not receive an error, then your .odbc.ini file is not correctly set. Please review the instructions found under the Configuration Files section (http://grid.rcs.hbs.org/db-configuration-files). You may contact Research Computing Services for assistance.

 

Next Steps

The next step is to test if your Data Source Name is correctly configured. Please select a Data Source Name (e.g. MyDataSourceName) and run the following command:

odbc query "MyDataSourceName"

db-connecting-stata-query-dsn-tables

 

Troubleshooting

If you receive an error, then please verify

  • your username and password are correct within your local .odbc.ini
  • the SSL path is correct within your local .odbc.ini
  • the driver group within your local .odbc.ini matches an appropriate driver group within the system odbcinst.ini

Data Source Name is correctly configured

If your odbc query command retrieved the correct list of tables without error, then your connection is all set!

Stata on your Local Windows

Windows users will need to first download the latest ODBC driver. While MariaDB has developed an ODBC driver, the most-recently-tested version does not support SSL connections and therefore will not work. Subsequently, we recommend Windows users download and install the latest MySQL connector at https://dev.mysql.com/downloads/connector/odbc/ (You do not need to sign up for any account. If you are asked to, then look at the bottom where you should find "No thanks, just start my download"). For convenience, please select the MSI installer for the appropriate system type, either 32-bit or 64-bit. If you are unsure of which system type you have, then within the Command Prompt please run the following command:

wmic os get osarchitecture

db-connecting-stata-windows-get-system-type

Once you have downloaded the installer, run it. When asked to select TypicalComplete, or Custom, please select Typical. After the installation has completed, launch the ODBC administrator by clicking on the Start menu in and then typing odbc as shown below. If you are using 64-bit Windows, then select Data Sources (ODBC), otherwise select 32 Bit ODBC Administration Tool.

db-connecting-stata-windows-launch-odbc-admin

Within the ODBC Administrator window, please verify that the MySQL ODBC connector is present under the Drivers tab. Next, switch to the User DSN tab, click Add..., and then select the appropriate MySQL ODBC connector (there may be two versions of the driver, ANSI and Unicode, in which case RCS recommends using ANSI) followed by clicking on Finished. A new window will open to configure your Data Source.

Configuring your Data Source should be straightforward. The required fields, as shown below, are:

Data Source Name = Any name may be used, but please do not include spaces
TCP/IP Server = Our MariaDB host name
User = MariaDB username
Password = MariaDB password
Database = You may leave this blank. It is recommended to use your personal database

You must also include the path to your SSL certificate. As a reminder, a SSL certificate is provided when you request a new MariaDB account. To include the SSL certifiacte path, click on Details to expand the list of available parameters. Under the SSL tab, find the SSL Certificate Authority field and enter the path to the local SSL certificate.

Please be sure to click on Test to validate your connection settings!

db-connecting-stata-windows-configure-dsn2

 

Now you may launch Stata and run the command below to confirm Stata can see the new Data Source you just configured.

odbc list

db-connecting-stata-windows-query-dsn

To test that Stata may connect to the MariaDB server, run the following command

odbc query "MyDataSourceName"

This command will query for available tables within your database. If you did not provide a database name, then the result with be null.

db-connecting-stata-windows-query-dsn-tables

If your odbc query command retrieved the correct list of tables without error, then your connection is all set!

 

Stata on your Local Mac

If you do not have ODBC set up on your Mac please follow the detailed instructions on our ODBC page.  Once your ODBC is set up you may launch Stata and run the command odbc list as shown above to confirm Stata can see the new Data Source you just configured.  Similarly you may then run odbc query to retrive the list of tables in your database.