Skip to content
An R package for connecting to databases using JDBC.
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
.settings
R Workaround for issue #90: turning off autocommit for RedShift when ex… Apr 9, 2019
docs
extras
inst
java Fixing FeatureNotSupported error in a consistent manner. Regenerating… Mar 31, 2019
man-roxygen
man Rerunning roxygen. Adding runAsBatch argument to renderTranslateExecu… Apr 4, 2019
tests
vignettes
.Rbuildignore Updating news with release date Nov 25, 2018
.classpath Dropping Mac build in Travis for now. Sep 21, 2017
.gitattributes
.gitignore
.project
.travis.yml Increasing version number, updating documentation. Apr 19, 2019
DESCRIPTION
DatabaseConnector.Rproj
NAMESPACE Rerunning roxygen. Adding runAsBatch argument to renderTranslateExecu… Apr 4, 2019
NEWS.md
README.md
_pkgdown.yml
compare_versions
cran-comments.md Updating documentation Feb 15, 2019
deploy.sh

README.md

DatabaseConnector

Build Status codecov.io CRAN_Status_Badge CRAN_Status_Badge

DatabaseConnector is part of the OHDSI Methods Library.

Introduction

This R package provides function for connecting to various DBMSs.

Features

  • Create connections to the various database platforms:
    • MicrosoftSQL Server
    • Oracle
    • PostgresSql
    • Microsoft Parallel Data Warehouse (a.k.a. Analytics Platform System)
    • Amazon Redshift
    • Apache Impala
    • Google BigQuery
    • IBM Netezza
    • SQLite
  • Statements for executing queries with
    • Error reporting to file
    • Progress reporting
    • Multiple statements per query
  • Support for fetching data to ffdf objects
  • Insert data frame to a database table
  • Supports the DBI interface
  • Integrates with RStudio's Connections tab

Examples

connectionDetails <- createConnectionDetails(dbms="postgresql", 
                                             server="localhost",
                                             user="root",
                                             password="blah",
                                             schema="cdm_v4")
conn <- connect(connectionDetails)
querySql(conn,"SELECT COUNT(*) FROM person")
disconnect(conn)
## regular data insert
insertTable(connection = connection, 
            tableName = "scratch.somedata", 
            data = data, 
            dropTableIfExists = TRUE, 
            createTable = TRUE, 
            tempTable = FALSE, 
            useMppBulkLoad = FALSE)
            
## bulk data insert with Redshift or PDW
insertTable(connection = connection, 
            tableName = "scratch.somedata", 
            data = data, 
            dropTableIfExists = TRUE, 
            createTable = TRUE, 
            tempTable = FALSE, 
            useMppBulkLoad = TRUE)

Technology

DatabaseConnector is an R package using Java's JDBC drivers.

System Requirements

Requires R. Also requires Java 1.6 or higher (Oracle Java is recommended. Issues have been reported when using GCJ.)

Dependencies

Please note that this package requires Java to be installed. If you don't have Java already intalled on your computed (on most computers it already is installed), go to java.com to get the latest version.

To be able to use Windows authentication for SQL Server, you have to install the JDBC driver. Download the .exe from Microsoft and run it, thereby extracting its contents to a folder. In the extracted folder you will find the file sqljdbc_4.0/enu/auth/x64/sqljdbc_auth.dll (64-bits) or sqljdbc_4.0/enu/auth/x86/sqljdbc_auth.dll (32-bits), which needs to be moved to location on the system path, for example to c:/windows/system32.

DatabaseConnector also depends on the OHDSI DatabaseConnectorJars and SqlRender packages.

For Redshift Bulk Mpp inserts, the cloudyR aws S3 pacakge is required.

Getting Started

To install the latest stable version, install from CRAN:

install.packages("DatabaseConnector")

To install the latest development version, install from GitHub:

install.packages("devtools")
devtools::install_github("ohdsi/DatabaseConnectorJars")
devtools::install_github("ohdsi/DatabaseConnector")

To download and use the JDBC drivers for BigQuery, Impala, or Netezza, see these instructions.

User Documentation

Support

License

DatabaseConnector is licensed under Apache License 2.0. The JDBC drivers fall under their own respective licenses.

Development

DatabaseConnector is being developed in R Studio.

Development status

Stable. The code is actively being used in several projects.

Acknowledgements

  • This project is supported in part through the National Science Foundation grant IIS 1251151.
You can’t perform that action at this time.