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
- Using the RODBC package to interact with MSSQL from R
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.
Comments
0 comments
Please sign in to leave a comment.