ODBC

 Sections:

 

About ODBC

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.

Windows ODBC setup

 

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]
rcs_mariadb = RCS_MariaDB_ODBC_connection

[rcs_mariadb]
driver = mariaDB
server = HOSTNAME
port = 3306
database = jharvard
user = jharvard
password = PASSWORD
sslmode = required
sslca = PATH_TO_SSL_CERT

Please note that you will substitute jharvard and PASSWORD with your MariaDB username and password. For the other parameters such as HOSTNAME, please contact us at research@hbs.edu.

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?

You will have different entries under driver and sslca depending on whether you are on the Grid or on your local machine. The parameter ssl-ca defines the path to your SSL CA certificate file. The path to the SSL CA certificate file will be different across machines as a result. Please contact us at research@hbs.edu for assistance. The parameter driver defines which ODBC driver to use. For driver with your Grid .odbc.ini file, we recommend using mariaDB as it will ensure you are using the drivers managed by RCS. On your local machine however you will need to either specify the path to your ODBC driver or to the correct listing within your odbcinst.ini file (see next section).

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;"]

 

Setup Instructions for Mac Users

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:

Mac ODBC dsn dialog  

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:

 mac odbc dsn setup

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
password = PASSWORD
sslmode = required
sslca = PATH_TO_SSL_CERT

Please note that you will substitute jharvard and PASSWORD with your MariaDB username and password. For the other parameters such as HOSTNAME, please contact us at research@hbs.edu.