Connecting to MSSQL from Domino
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; however, you may use an alternative package if you'd like.
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.