Utilises The 'jTDS' project's 'JDBC' 3.0 'SQL Server' driver to extend 'DBI' classes and methods. The package also implements a 'SQL' backend to the 'dplyr' package.
You can install the development version from GitHub:
And when the package is back on CRAN, you can install it the usual way:
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: 18.104.22.168type: &type sqlserverport: &port 1433domain: &domain companynameuser: &user winusernamepassword: &pass winpassworduseNTLMv2: &ntlm trueSQL_DEV:server: 22.214.171.124type: *typeport: *portdomain: *domainuser: *userpassword: *passuseNTLMv2: *ntlm
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.
The following illustrates how you can make use of the DBI interface. Note that we do not attach the
library(DBI)con <- dbConnect(RSQLServer::SQLServer(), server = "TEST", database = 'DBItest')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)#>  "band_instruments" "band_members"dbReadTable(con, 'band_members')#> name band#> 1 Mick Stones#> 2 John Beatles#> 3 Paul BeatlesdbListFields(con, 'band_instruments')#>  "name" "plays"# Fetch all resultsres <- dbSendQuery(con, "SELECT * FROM band_members WHERE band = 'Beatles'")dbFetch(res)#> name band#> 1 John Beatles#> 2 Paul BeatlesdbClearResult(res)#>  TRUE
The following illustrates how you can make use of the dplyr interface. Again, we do not attach the
library(dplyr, warn.conflicts = FALSE)members <- tbl(con, "band_members")instruments <- tbl(con, "band_instruments")members %>%left_join(instruments) %>%filter(band == "Beatles")#> Joining, by = "name"#> # Source: lazy query [?? x 3]#> # Database: SQLServerConnection#> name band plays#> <chr> <chr> <chr>#> 1 John Beatles guitar#> 2 Paul Beatles basscollect(members)#> # A tibble: 3 x 2#> name band#> * <chr> <chr>#> 1 Mick Stones#> 2 John Beatles#> 3 Paul Beatles
dbRemoveTable(con, "band_instruments")#>  TRUEdbRemoveTable(con, "band_members")#>  TRUEdbDisconnect(con)#>  TRUE
RSQLServer was archived by CRAN after dplyr v0.4 irredeemably broke the dplyr SQL Server backend provided by this package. Well we are back on CRAN and there have been a lot of changes since you last saw this package including improvements to the DBI backend, compatibility with the latest iteration of the dplyr/dbplyr backend and removing the reliance on RJDBC's middleware.
The next version of this package will likely swap the jTDS driver for the official Microsoft JDBC driver and make further interface changes to better align with the more explicit interface specifications in the DBI package.
A number of changes have been made to improve DBI compliance as specified by tests in the
DBItest package (#60):
fetch()on non-query statements return an empty data frame
dbWriteTable()code now being wrapped in
AsIsobjects and returns
VARBINARYSQL types with lengths of at least one
TRUEfor empty ResultSets
dbDisconnect()returns a warning if called on a connection that is already closed and otherwise closes the connection and returns
SQLServerConnectionnow complies with
SQLServerDriverreturns the JDBC driver version (3.0) as
driver.versionand jTDS verion as
client.versioninstead of the jTDS client version and
SQLServer()no longer accepts arguments
A number of other changes have been made to the
dbRollback()methods for DBI generics
dbWriteTable()is faster by always using transactions (
COMMITafter), and optionally much faster by way of the
dbWriteTable()to match generic documented in the DBI package. It also now returns
dbWriteTable()now fails when attempting to append to a temporary table (#75)
dbSendStatement()method which required the extension of
SQLServerUpdateResultthe latter of which is used to dispatch the
dbGetRowsAffected()method (#95). Added
batchoption to both
dbSendQuery()for insert/update speedup (#69, #90, #106, @r2evans).
dbBind()method to replace the internal
.fillStatementParameter()method which required the extension of
SQLServerPreResultthe latter of which allows statements with bindings to present a ResultSet interface to DBI (ResultSets are only created after values are bound to parameterised statements in JDBC). (#88)
dbBind()now supports multi-row binding (e.g., for
SQLServerConnectionwhich is called by
dbDataTypemaps R character objects of sufficiently long length to
VARCHAR(MAX)on newer version of MSSQL rather than
TEXTas the latter is being deprecated.
NULLwhere other default values were assigned. This does not change the behaviour of the method.
dbListTables()which allows you to list all tables matching a pattern.
dbExistsTable()now passed table name to
dbListTables()as a pattern to be matched which should improve its performance.
dbColumnInfo()succeeds in running (#96, @r2evans)
SQLServerResulthas been removed and calls the default DBI method which calls
dbGetRowCount()etc. The latter methods have been implemented for
SQLServerResultand are exported.
SQLServerDriverand always returns
A number of changes were made to
dplyr backend including a refactoring of its code across to the newer
dbplyr package. As a result, dplyr >= 0.7.0 is required:
src_desc()defunct in favour of
IF EXISTSSQL clause if supported by SQL Server (#75)
db_insert_into()which overwrites existing table if set to
TRUEand if necessary.
sql_select()method supports the
DISTINCTkeyword and includes
TOPkeyword when query results are ordered.
copy_to()implementations are replaced by
db_explain()is more informative (e.g. prints relative cost of operations)
db_analyze()unsupported and simply returns
db_query_fields()method for SQLServerConnection removed in favour of default dplyr method. The latter better handles sub-queries.
setdiff()methods are removed in favour of default
as.character()calls now cast scalar input values to SQL types
NVARCHAR(4000)respectively rather than
TEXTrespectively (default in dplyr).
This package no longer depends on
RJDBC. As such a number of user visible changes have been made:
dbSendQuery()only executes queries rather than other arbitrary SQL statements. See rstats-db/DBI#20. It also no longer supports calling stored procedures (callable statements).
dbSendQuery()can execute parameterised queries. See
?DBI:dbBindfor more details on parameterised queries.
dbSendUpdate()which was based on RJDBC's method and which executes non-query SQL statements will be deprecated in favour of the more descriptive
dbExecute()which has been implemented upstream in DBI (the latter of which calls
dbSendStatement()). See rstats-db/DBI#20. Unlike RJDBC's
dbExecute()does not yet support calling stored procedures as these do not seem to be explicitly supported by any other DBI backend.
dbExecute()arguments have been changed to reflect the DBI generic.
TRUEin all instances rather than
FALSEas was the case in RJDBC.
A number of previously imported RJDBC methods have now been reimplemented in this package with no user visible changes.
arrange()method now returns whole result rather than top 100 rows (#124). This was implemented by changing the default behaviour of
sql_select()method and may result in breaks to existing code.
dbConnect()interface has been enhanced but breaks backward compatibility consequently enhanced. Server details can be specified in a YAML file which are passed to dbConnect. See
fetch()now processes the data frame returned by
RJDBC::fetch()to better map database field types to their equivalents in R such as integer, logical, Date, POSIXct and raw. (#31, #47)
dbWriteTable()can now write Date, POSIXct and other non-character and non-numeric field types to equivalent SQL representations (#32)