RxSqlServerData: Generate SqlServer Data Source Object
This is the main generator for S4 class RxSqlServerData, which extends RxDataSource.
RxSqlServerData(table = NULL, sqlQuery = NULL, connectionString = NULL, rowBuffering = TRUE, returnDataFrame = TRUE, stringsAsFactors = FALSE, colClasses = NULL, colInfo = NULL, rowsPerRead = 50000, verbose = 0, useFastRead = TRUE, server = NULL, databaseName = NULL, user = NULL, password = NULL, writeFactorsAsIndexes = FALSE) ## S3 method for class `RxSqlServerData': head (x, n = 6L, reportProgress = 0L, ... ) ## S3 method for class `RxSqlServerData': tail (x, n = 6L, addrownums = TRUE, reportProgress = 0L, ... )
NULL or character string specifying the table name. Cannot be used with
NULL or character string specifying a valid SQL select query. Cannot contain hidden characters such as tabs or newlines. Cannot be used with
table. If you want to use
TABLESAMPLE clause in
rowBuffering argument to
NULL or character string specifying the connection string.
logical specifying whether or not to buffer rows on read from the database. If you are having problems with your ODBC driver, try setting this to
logical indicating whether or not to convert the result from a list to a data frame (for use in
rxReadNext only). If
FALSE, a list is returned.
logical indicating whether or not to automatically convert strings to factors on import. This can be overridden by specifying
TRUE, the factor levels will be coded in the order encountered. Since this factor level ordering is row dependent, the preferred method for handling factor columns is to use
colInfo with specified
character vector specifying the column types to use when converting the data. The element names for the vector are used to identify which column should be converted to which type.
Allowable column types are:
"float32"(the default for floating point data for .xdf files),
float64as in R),
"int16"(alternative to integer for smaller storage space),
"uint16"(alternative to unsigned integer for smaller storage space),
"Date"(stored as Date, i.e.
"factor"type, the levels will be coded in the order encountered. Since this factor level ordering is row dependent, the preferred method for handling factor columns is to use
Note that equivalent types share the same bullet in the list above; for some types we allow both 'R-friendly' type names, as well as our own, more specific type names for .xdf data.
Note also that specifying the column as a "factor" type is currently equivalent to "string" - for the moment, if you wish to import a column as factor data you must use the
colInfoargument, documented below.
list of named variable information lists. Each variable information list contains one or more of the named elements given below. The information supplied for
colInfo overrides that supplied for
- Currently available properties for a column information list are:
type- character string specifying the data type for the column. See
colClassesargument description for the available types. Specify
typefor 0-based factor indexes.
levelsmust also be specified.
newName- character string specifying a new name for the variable.
description- character string specifying a description for the variable.
levels- character vector containing the levels when
type = "factor". If the levels property is not provided, factor levels will be determined by the values in the source column. If levels are provided, any value that does not match a provided level will be converted to a missing value.
newLevels- new or replacement levels specified for a column of type "factor". It must be used in conjunction with the
levelsargument. After reading in the original data, the labels for each level will be replaced with the
low- the minimum data value in the variable (used in computations using the
high- the maximum data value in the variable (used in computations using the
number of rows to read at a time.
integer value. If
0, no additional output is printed. If
1, information on the odbc data source type (
odbcFast) is printed.
additional arguments to be passed directly to the underlying functions.
positive integer. Number of rows of the data set to extract.
TRUE, row numbers will be created to match the original data set.
integer value with options:
0: no progress is reported.
1: the number of processed rows is printed and updated.
2: rows processed and timings are reported.
3: rows processed and all timings are reported.
logical specifying whether or not to use a direct ODBC connection. On Linux systems, this is the only ODBC connection available. Note:
useFastRead = FALSE is currently not supported in writing to SQL Server data source.
Target SQL Server instance. Can also be specified in the connection string with the
Database on the target SQL Server instance. Can also be specified in the connection string with the
SQL login to connect to the SQL Server instance. SQL login can also be specified in the connection string with the
Password associated with the SQL login. Can also be specified in the connection string with the
TRUE, when writing to an
RxOdbcData data source, underlying factor indexes will be written instead of the string representations.
tail method is not functional for this data source type and will report an error.
password arguments take precedence over
equivalent information provided within the
connectionString argument. If, for
example, you provide the user name in both the
arguments, the one in
connectionString is ignored.
object of class RxSqlServerData.
Microsoft Technical Support
## Not run: # Create an RxSqlServerData data source # Note: for improved security, read connection string from a file, such as # sqlServerConnString <- readLines("sqlServerConnString.txt") sqlServerConnString <- "SERVER=hostname;DATABASE=RevoTestDB;UID=DBUser;PWD=Password;" sqlServerDataDS <- RxSqlServerData(sqlQuery = "SELECT * FROM claims", connectionString = sqlServerConnString) # Create an xdf file name claimsXdfFileName <- file.path(tempdir(), "importedClaims.xdf") # Import the data into the xdf file rxImport(sqlServerDataDS, claimsXdfFileName, overwrite = TRUE) # Read xdf file into a data frame claimsIn <- rxDataStep(claimsXdfFileName) head(claimsIn) ## End(Not run)