Connecting to MSSQL from Domino

Submitted originally by: petter.olsson

Overview

This article describes how to connect to a Microsoft SQL Server (MSSQL) from Domino.

Options for connecting to MSSQL from Domino

• Using the pymssql package to interact with MSSQL from Python
  • Environment setup
  • Credential setup
  • Usage
• Using the RODBC package to interact with MSSQL from R
  • Environment setup
  • Usage

Python and MSSQL

Domino recommends the pymssql package for interacting with MSSQL databases from Python.

Environment setup

Use the Dockerfile instruction below to install pymssql in your environment.

This instruction assumes you already have pip installed.

RUN pip install pymssql

For a basic introduction to modifying Domino environments, watch this tutorial video.

Credential setup

There are several environment variables you should set up to store secure information about your MSSQL connection. Set the following as Domino environment variables on your user account:

• DB_SERVER
• DB_USERNAME
• DB_PASSWORD

Read Environment variables for secure credential storage to learn more about Domino environment variables. 

Usage

Read the pymssql documentation for detailed information on how to use the package. Below is a simple example for connecting to MSSQL with Python where:

• you have set up environment variables noted above
• the server hosts a database named "myData" with a table named "addresses"

from os import getenv
import pymssql

server = getenv("DB_SERVER")
user = getenv("DB_USERNAME")
password = getenv("DB_PASSWORD")

conn = pymssql.connect(server, user, password, "myData")
cursor = conn.cursor()

cursor.execute('SELECT * FROM addresses')
row = cursor.fetchone()
while row:
    print("ID=%d, Name=%s" % (row[0], row[1]))
    row = cursor.fetchone()

conn.close()

R and RODBC to MSSQL

Domino recommends the RODBC library for interacting with MSSQL databases from R.

Environment setup

Use the Dockerfile instruction below to add the MSSQL drivers to your Ubuntu 16.04 environment.

RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
RUN apt-get update
RUN ACCEPT_EULA=Y apt-get install msodbcsql17

For a basic introduction to modifying Domino environments, watch this tutorial video.

Usage

Read the RStudio RODBC documentation for detailed information on how to use the package.