Introduction to ODBC Resources

The hardware and bandwidth for this mirror is donated by dogado GmbH, the Webhosting and Full Service-Cloud Provider. Check out our Wordpress Tutorial.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]dogado.de.

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)

These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.
Health stats visible at Monitor.