[R] SQL Server "float" is not handled by RODBC -- Is there a workaround?

[jim holtman]

Here is the system I am using:
=====================================
> 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

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United
States.1252
[3] LC_MONETARY=English_United States.1252
LC_NUMERIC=C
[5] LC_TIME=English_United States.1252

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base

other attached packages:
[1] lubridate_1.3.3 RODBC_1.3-12
loaded via a namespace (and not attached):
[1] magrittr_1.5  plyr_1.8.3    tools_3.2.2   memoise_0.2.1 Rcpp_0.12.1
stringi_0.5-5 digest_0.6.8
[8] stringr_1.0.0
========================================

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?

Jim Holtman
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]]