sessionInfo()R version 3.2.2 (2015-08-14)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1
 LC_COLLATE=English_United States.1252 LC_CTYPE=English_United
 LC_MONETARY=English_United States.1252
 LC_TIME=English_United States.1252
attached base packages:
 stats graphics grDevices utils datasets methods base
other attached packages:
 lubridate_1.3.3 RODBC_1.3-12
loaded via a namespace (and not attached):
 magrittr_1.5 plyr_1.8.3 tools_3.2.2 memoise_0.2.1 Rcpp_0.12.1
I have data on a SQL Server that I am connecting to where some of the
fields are defined as "float" so that the data is stored in the database as
an IEEE 754 value. Now when I read this is using RODBC, the data comes
across the interface in the floating point format; I used Wireshark to
examine the packets that were being sent. Some of the data is also defined
as "int" and comes across in binary.
When the data is read in with
df <- sqlQuery(db, "select * from mydb", as.is = TRUE)
The resulting dataframe has the floating point values as 'chr' and the
integer fields as 'int'; I would have expected the floating point fields to
be 'num'. Now in the "ODBC Connectivity" Vignette by Ripley there was the
comment that "double" data values come back as type 8, but on some systems
they may be type 6; well on SQL Server, "float" is type 6.
So what appears to happen, is this data is not recognized as a floating
point value and is therefore converted to a character. When the data is
made available to the R script, I then have to convert this back to
floating point. If I use "stringsAsFactors = FALSE" on the query, this
conversion back to floating point will be done within the RODBC package.
This becomes a problem when I have dataframes with several million rows and
multiple columns of numerics is that the conversion to/from characters is
adding time to the processing.
So I was wondering is there a workaround to this problem? Is it possible
to add the capability to RODBC when processing SQL Server to avoid this
conversion? Or is there some other way around this problem?
Data Munger Guru
What is the problem that you are trying to solve?
Tell me what you want to do, not how you want to do it.
[[alternative HTML version deleted]]