This repository has been archived by the owner on Apr 14, 2018. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 34
/
Copy pathREADME.Rmd
114 lines (85 loc) · 3.72 KB
/
README.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
---
output: github_document
---
<!-- README.md is generated from README.Rmd. Please edit that file -->
```{r, echo = FALSE}
knitr::opts_chunk$set(
collapse = TRUE,
comment = "#>",
fig.path = "README-"
)
```
# RSQLServer
[](https://www.tidyverse.org/lifecycle/#retired)
[](https://cran.r-project.org/package=RSQLServer)
[](https://travis-ci.org/imanuelcostigan/RSQLServer)
[](https://ci.appveyor.com/project/imanuelcostigan/rsqlserver)
[](https://codecov.io/gh/imanuelcostigan/RSQLServer)
An R package that provides a SQL Server R Database Interface ([DBI](https://github.com/rstats-db/DBI)), based on the Microsoft's [JDBC driver](http://jtds.sourceforge.net/index.html).
## Package status
This package is no longer being actively maintained as there is now an excellent, much better supported package [odbc](https://github.com/r-dbi/odbc). I will accept clean pull requests, but won't be implementing new features or fix bugs. Over time, this package will likely be archived.
## Installation
You can install the development version from GitHub:
```{r, eval=FALSE}
# install.packages('devtools')
devtools::install_github('imanuelcostigan/RSQLServer')
```
And when the package is back on CRAN, you can install it the usual way:
```{r, eval=FALSE}
install.packages("RSQLServer")
```
## 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:
```yaml
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
```
## Usage
Ensure that your `~/sql.yaml` file contains a valid SQL Server entry named `TEST`. In the following, the `TEST` server, generously provided by Microsoft for the purposes of this package's development, has a database containing band data sets.
### DBI usage
The following illustrates how you can make use of the DBI interface. Note that we **do not** attach the `RSQLServer` package.
```{r}
library(DBI)
con <- dbConnect(RSQLServer::SQLServer(), server = "TEST", database = "rsqlserver")
dbWriteTable(con, "band_members", dplyr::band_members)
dbWriteTable(con, "band_instruments", dplyr::band_instruments)
# RSQLServer only returns tables with type TABLE and VIEW.
dbListTables(con)
dbReadTable(con, 'band_members')
dbListFields(con, 'band_instruments')
# Fetch all results
res <- dbSendQuery(con, "SELECT * FROM band_members WHERE band = 'Beatles'")
dbFetch(res)
dbClearResult(res)
```
### dplyr usage
The following illustrates how you can make use of the dplyr interface. Again, we **do not** attach the `RSQLServer` package.
```{r}
library(dplyr, warn.conflicts = FALSE)
members <- tbl(con, "band_members")
instruments <- tbl(con, "band_instruments")
members %>%
left_join(instruments) %>%
filter(band == "Beatles")
collect(members)
```
Clean up
```{r}
dbRemoveTable(con, "band_instruments")
dbRemoveTable(con, "band_members")
dbDisconnect(con)
```