Introduction to ODBC Resources

The odbc.resourcer package is for accessing databases implementing the Open Database Connectivity (ODBC) API, more specifically for a MS SQL Server server database.

Testing

For testing purpose, you can use the example provided at <installation path>/odbc.resourcer/examples/mssql/.

This directory contains an example of how to use the odbc.resourcer package to connect to a Microsoft SQL Server database and retrieve table values.

Prerequisites

Run an instance of Microsoft SQL Server.

# get the docker image
make pull
# start the container
make up

Add some initial data in the database. You can use the provided init-scripts/init.sql script to create a sample database and table.

# initialize the database with sample data
make init
# list databases
make databases

You may need to install some system dependencies:

sudo make ubuntu-deps

Usage

Create a resource and a resource client:

library(odbc.resourcer)
res <- resourcer::newResource(url = "odbc+mssql://localhost:1433/EpidemiologyDB/PatientData", identity = "sa", secret = "YourStrong@Password123")
# Create a resource client
client <- resourcer::newResourceClient(res)

Coerce resource to a data frame:

df <- client$asDataFrame()

Disconnect the client:

client$close()

Troubleshooting

For trouble shooting use a direct connection with odbc and DBI packages:

library(odbc)
library(DBI)

# Connect using odbc package
conn <- DBI::dbConnect(odbc::odbc(),
                  Driver = "ODBC Driver 18 for SQL Server",
                  Server = "localhost,1433",
                  Database = "EpidemiologyDB",
                  UID = "sa",  # Use 'sa' instead of 'myuser'
                  PWD = "YourStrong@Password123",  # Use the SA password from docker-compose
                  TrustServerCertificate = "yes")

# Test the connection
DBI::dbGetQuery(conn, "SELECT TOP 5 * FROM PatientData")
# Disconnect
DBI::dbDisconnect(conn)