RSQLServer (version 0.2.0)

src_sqlserver: Connect to SQLServer or Sybase

Description

Use src_sqlserver to connect to an existing SQL Server or Sybase database, and tbl to connect to tables within that database.

Usage

src_sqlserver(server, file = NULL, database = "", type = "sqlserver", port = "", properties = list())

Arguments

server

the server address or recognised alias thereof.

file

defaults to using the server details file in $HOME/sql.yaml. The server details including type, port and any optional properties can be sourced from this file. If the server name is found in file, the details therein are used (and in particular, those provided in other arguments to this function are ignored). The connection method prefers server details to be provided in a "sql.yaml" file rather than provided as arguments to this function. If you wish to specify the details as parameters, ensure that either the file does not exist or that the server details are not in the YAML file.

database

the name of the database hosted on the server. If an empty string, a connection to the default database on server is assumed.

type

the server type. Must be either "sqlserver" or "sybase". Defaults to "sqlserver".

port

the TCP/IP default port. This will be coerced to a string. Defaults to 1433 if an empty string (jTDS behaviour).

properties

One or more optional connection properties. in a named list. Note if you intend to set the useNTLMv2 property to 'true' from the default API value of 'false', you will need to make a specific authentication driver available to the SQL Server driver, although this hasn't worked particularly well in testing. See RSQLServer for more details. Should you wish to use Windows authentication to connect to the server, I recommend you set the following optional parameters: set useNTLMv2 to 'true', domain to your domain and user and password to your username and password on domain. jTDS' SSO functionality is flaky.

Value

a dplyr SQL based src with subclass sqlserver

Examples

## Not run: 
# library(dplyr)
# # Connection basics ---------------------------------------------------------
# # To connect to TEST database, assumed to be specified in your ~/sql.yaml
# # file (see \code{\link{have_test_server}}), first create a src:
# my_src <- src_sqlserver("TEST")
# # Then reference a tbl within that src
# my_tbl <- tbl(my_src, "my_table")
# # Methods -------------------------------------------------------------------
# # You can then inspect table and perform actions on it
# dim(my_tbl)
# colnames(my_tbl)
# head(my_tbl)
# # Data manipulation verbs ---------------------------------------------------
# filter(my_tbl, this.field == "that.value")
# select(my_tbl, from.this.field:to.that.field)
# arrange(my_tbl, this.field)
# mutate(my_tbl, squared.field = field ^ 2)
# # Group by operations -------------------------------------------------------
# by_field <- group_by(my_tbl, field)
# group_size(by_field)
# by_field %>% summarise(ave = mean(numeric.field))
# # See dplyr documentation for further information on data operations
# ## End(Not run)

Run the code above in your browser using DataCamp Workspace