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 framedbWriteTable()
code now being wrapped in dbWithTransaction()
dbDataType()
works for AsIs
objects and returns NVARCHAR
and VARBINARY
SQL types with lengths of at least onedbHasCompleted()
returns TRUE
for empty ResultSets dbDisconnect()
returns a warning if called on a connection that is already closed and otherwise closes the connection and returns TRUE
.dbGetInfo()
for SQLServerConnection
now complies with DBItest
expectationsdbGetInfo()
for SQLServerDriver
returns the JDBC driver version (3.0) as driver.version
and jTDS verion as client.version
instead of the jTDS client version and NA
respectively. SQLServer()
no longer accepts argumentsA number of other changes have been made to the DBI
backend:
dbBegin()
, dbCommit()
, dbRollback()
methods for DBI genericsdbWriteTable()
is faster by always using transactions (BEGIN
before and COMMIT
after), and optionally much faster by way of the batch
option.dbWriteTable()
to match generic documented in the DBI package. It also now returns TRUE
invisibly.dbWriteTable()
now fails when attempting to append to a temporary table (#75)dbSendStatement()
method which required the extension of SQLServerResult
to SQLServerUpdateResult
the latter of which is used to dispatch the dbGetRowsAffected()
method (#95). Added batch
option to both dbSendStatement()
and dbSendQuery()
for insert/update speedup (#69, #90, #106, @r2evans).dbBind()
method to replace the internal .fillStatementParameter()
method which required the extension of SQLServerResult
to SQLServerPreResult
the 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 INSERT
and UPDATE
)sqlCreateTable()
for SQLServerConnection
which is called by db_create_table()
. (#76)dbDataType
maps R character objects of sufficiently long length to VARCHAR(MAX)
on newer version of MSSQL rather than TEXT
as the latter is being deprecated.dbConnect()
are now NULL
where other default values were assigned. This does not change the behaviour of the method.pattern
argument to 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)dbGetInfo()
method for SQLServerResult
has been removed and calls the default DBI method which calls dbHasCompleted()
, dbGetRowCount()
etc. The latter methods have been implemented for SQLServerResult
and are exported.dbIsValid()
implemented for SQLServerDriver
and always returns TRUE
.show()
methodsA 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 db_desc()
db_create_table()
, db_write_table()
and db_insert_into()
for SQLServerConnection
db_drop_table()
supports the IF EXISTS
SQL clause if supported by SQL Server (#75)temporary
argument to db_insert_into()
which overwrites existing table if set to TRUE
and if necessary. sql_select()
method supports the DISTINCT
keyword and includes TOP
keyword when query results are ordered.compute()
and copy_to()
implementations are replaced by db_compute()
and db_copy_to()
implementationsdb_explain()
is more informative (e.g. prints relative cost of operations)db_analyze()
unsupported and simply returns TRUE
.db_query_fields()
method for SQLServerConnection removed in favour of default dplyr method. The latter better handles sub-queries.intersect()
and setdiff()
methods are removed in favour of default dplyr
methods.as.numeric()
and as.character()
calls now cast scalar input values to SQL types FLOAT
and NVARCHAR(4000)
respectively rather than NUMERIC
and TEXT
respectively (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:dbBind
for 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 dbSendUpdate()
, 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.dbUnloadDriver()
which returns TRUE
in all instances rather than FALSE
as 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 ?SQLServer
(#8)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)jtds_url()
NEWS.md
)Any scripts or data that you put into this service are public.
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.