RSQLServer v0.2.0

0

Monthly downloads

0th

Percentile

SQL Server R Database Interface (DBI) and dplyr SQL Backend

Utilises The jTDS Project's JDBC 3.0 SQL Server driver to extend the RJDBC classes and DBI methods. It defines a SQLServerDriver, SQLServerConnection & SQLServerRsult S4 classes as extensions of the RJDBC equivalent classes with most DBI methods being thin extensions of the methods defined by RJDBC classes. However, the dbConnect interface is more convenient, and the data reading and writing capabilities found in RJDBC's fetch and dbWriteTable methods have been refined. The package also implements a SQL backend to the dplyr package.

Readme

RSQLServer

An R package that provides a SQL Server R Database Interface (DBI), based on the cross-platform jTDS JDBC driver.

Installation

You can install the package from CRAN:

install.packages("RSQLServer")

Or try the development version from GitHub:

# install.packages('devtools')
devtools::install_github('imanuelcostigan/RSQLServer')

NB: This package has been tested on Windows 7 x64 (>= 6.1) and OSX.

Config file

We recommend that you store server details and credentials in ~/sql.yaml. This is partly so that you do not need to specify a username and password in calls to dbConnect(). But it is also because in testing, we've found that the jTDS single sign-on (SSO) library is a bit flaky. The contents of this file should look something like this:

SQL_PROD:
    server: 11.1.111.11
    type: &type sqlserver
    port: &port 1433
    domain: &domain companyname
    user: &user winusername
    password: &pass winpassword
    useNTLMv2: &ntlm true
SQL_DEV:
    server: 11.1.111.15
    type: *type
    port: *port
    domain: *domain
    user: *user
    password: *pass
    useNTLMv2: *ntlm
AW:
   server: mhknbn2kdz.database.windows.net
   type: sqlserver
   user: sqlfamily
   password: sqlf@m1ly
   port: 1433

Usage

First ensure that your ~/sql.yaml file contains the AW entry described above:


#############
# DBI
#############

# Note we do not attach the RSQLServer package.
library(DBI)
# Connect to AW server in ~/sql.yaml
# This is an Azure hosted SQL Server database provided at someone else's 
# expense. Feel free to tip them some:
# http://sqlblog.com/blogs/jamie_thomson/archive/2012/03/27/adventureworks2012-now-available-to-all-on-sql-azure.aspx
aw <- dbConnect(RSQLServer::SQLServer(), "AW", database = 'AdventureWorks2012')
# RSQLServer only returns tables with type TABLE and VIEW.
# But this DB has lots of useless tables. 
dbListTables(aw)
dbListFields(aw, 'Department')
# Department table is in accessible through the HumanResources schema
# NB: The ModifiedDate field is returned as a POSIXct date type rather than 
# as a string per JDBC interface.
dbReadTable(aw, 'HumanResources.Department')

# Fetch all results
res <- dbSendQuery(aw, 'SELECT TOP 10 * FROM HumanResources.Department')
dbFetch(res)
dbClearResult(res)

# Disconnect from DB
dbDisconnect(aw)

#############
# dplyr
#############

# Note we do not attach the RSQLServer package here either
library(dplyr)
aw <- RSQLServer::src_sqlserver("AW", database = "AdventureWorks2012")
# Alas, cannot easily call tables in non-default schema
# Workaround is to SELECT whole table
# https://github.com/hadley/dplyr/issues/244
# Retrieves and prints first ten rows of table only
(dept <- tbl(aw, sql("SELECT * FROM HumanResources.Department")))
# The following is translated to SQL and executed on the server. Only
# the first ten records are retrieved and printed to the REPL.
rd <- dept %>% 
  filter(GroupName == "Research and Development") %>% 
  arrange(Name)
# Bring the full data set back to R
collect(rd)

Functions in RSQLServer

Name Description
SQLServerDriver-class An S4 class to represent a SQL Server driver
setops Intersect and setdiff methods
have_test_server Checks availability of TEST server
SQLServerResult-class An S4 class to represent a SQL Server result set
RSQLServer RSQLServer
SQLServer Create a SQLServer driver
src_sqlserver Connect to SQLServer or Sybase
SQLServerConnection-class An S4 class to represent a SQL Server connection
get_server_details Get server details from YAML file
No Results!

Last month downloads

Details

Type Package
Date 2016-03-22
License GPL-2
SystemRequirements Java (>= 1.3)
URL https://github.com/imanuelcostigan/RSQLServer
BugReports https://github.com/imanuelcostigan/RSQLServer/issues
Collate 'RSQLServer.R' 'Utils.R' 'dbi-classes.R' 'dbi-methods.R' 'dbi-shims.R' 'dplyr-imports.R' 'dplyr.R' 'jdbc-methods.R' 'onLoad.R' 'sql-backends.R' 'sql-methods.R'
RoxygenNote 5.0.1
NeedsCompilation no
Packaged 2016-03-22 08:57:39 UTC; imanuel
Repository CRAN
Date/Publication 2016-03-22 20:08:08

Include our badge in your README

[![Rdoc](http://www.rdocumentation.org/badges/version/RSQLServer)](http://www.rdocumentation.org/packages/RSQLServer)