# ODBC

 Sections:

ODBC is an API for interacting with databases. ODBC is widely supported by operating systems (e.g. Windows), database systems (e.g. MariaDB), and commonly-used tools such as Stata. This compatability across environments comes at the expense of performance. Most users will not notice this drawback, however for high-performance needs, please contact RCS at research@hbs.edu to discuss alternatives.

Setup Instructions for Windows Users

Windows has a built-in ODBC administration tool. To access it, click on the Windows Start button, type "data sources" and then select Data Sources (ODBC) as shown below. For users with a Windows version earlier than Windows 7, you may not have this tool. Instead, you will use 32 Bit Administration Tool.

Grid and Mac - Configuration Files

.odbc.ini

This section only applies to non-Windows users.

The .odbc.ini configuration file found within your home directory is used to store ODBC connection details to any ODBC-compliant database server, such as our MariaDB server. This file will identify which driver to use for the connection as well as user account information and connection parameters to the server.

All ODBC connections are to be defined within a single .odbc.ini file. Within this configuration file, each grouping of parameters is preceded by [GROUP_NAME]. This is the DSN or data source name. Stata or any other program you wish to connect to your database will require you to specify which dsn you want to use.

Do I need this file?

If you are going to use Stata or any other tool that relies on an ODBC connection, then yes you must have this file.

I don't have this file (on the Grid). How do I create one?

You may request a template or create your own with the following details

[ODBC Data Sources]

server = HOSTNAME
port = 3306
database = jharvard
user = jharvard
sslmode = required
sslca = PATH_TO_SSL_CERT

Once your .odbc.ini is ready, move it your home directory and adjust file privileges to ensure no one else can read the file. On the Grid or on a linux or Mac local machine, you may prevent others from reading the file via the command:

chmod 700 ~/.odbc.ini

Can I use the same .odbc.ini on the Grid and on my local machine?

odbcinst.ini

This section only applies to non-Windows users.

The odbcinst.ini configuration file found within your local home directory is used to store ODBC driver details. This file is used in conjunction with .odbc.ini (see section above).

Do I need this file?

If you are going to be using your local machine with Stata or any other tool that relies on ODBC connections, then yes you must have this file.

I don't see this file on my local machine. Is there an .odbcinst.ini file on the Grid to copy?

Yes, but you will need to modify it. More specifically, you will need to update the path to the ODBC driver.

The Grid file may be found at /etc/odbcinst.ini

The path to the drivers may be found within this file by reading it. As an example, you may read it by using the command cat /etc/odbcinst.ini. You will notice that there is an entry for [mariaDB] and lists the driver path as /usr/lib64/libmyodbc5a.so. You should copy this driver file to your local machine. Once you have copied odbcinst.ini to your local home directory, edit it such that the driver path under [mariaDB] points to the local driver file.

Setup Instructions for Grid Users

The Grid already has an ODBC administration tool and appropriate ODBC drivers. However, each user needs to configure each of their ODBC connections within one .odbc.ini file in their home directory. The .odbc.ini file is described above. To check if you already have this file, please run the following command

ls -a ~/ | grep *odbc.ini

[figure - run mentioned command]

If no results are returned, then you will need to create one or request a template from RCS. If you do have an odbc.ini file, then you will need to confirm whether you have an existing connection with correct parameters or you will need to add a new connection. For more details, please see the .odbc.ini section of Grid and Mac - Configuration Files above.

Once your .odbc.ini file is ready for testing, please take a moment to confirm which dsn you will be using for your ODBC connection. The dsn is the connection group, as described in the .odbc.ini section referenced above. With the dsn in hand, run the following command on the Grid, replacing DSN with your dsn.

isql DSN

You will hopefully get a "Connected!" message as shown below, indicated the connection was successful. If you did connect, then enter quit; to exit the isql console (as shown below). If you did not connect, then please confirm you are using the correct dsn and the parameters are correctly set. Please contact RCs at research@hbs.edu for assistance.

[figure - successful isql connection, followed by "quit;"]

To configure a DSN on macOS you can use the ODBC Administrator GUI.  To use ODBC Administrator first open the ODBC Administrator from the Utilities folder in the Applications folder.  From the ODBC Administrator dialog, choose either the User DSN or System DSN tab and click Add:

Select the Connector/ODBC driver and click OK.  You will be presented with the Data Source Name (DSN) dialog. Enter the Data Source Name and an optional Description for the DSN:

Click Add to add a new keyword/value pair to the panel. Please configure the following connection parameters:

server = HOSTNAME
port = 3306
database = jharvard
user = jharvard