[R-sig-DB] RODBC in R-3.1.1 and R-3.0.3

[McGehee, Robert]

Hi Paul,
I can confirm the behavior has changed for me as well upon upgrade to R 3.1.0. I use RODBC to connect to Microsoft SQL Server. While the exact example you provided didn't produce the bad/changed behavior, it wasn't too hard to find an example that did.

# R 3.1.0
> x <- sqlQuery(channel=con, "SELECT cast(1 as numeric(20,16));")[[1]]
> x
[1] "1.0000000000000000"
> class(x)
[1] "character"

# R 3.0.3
> x <- sqlQuery(channel=con, "SELECT cast(1 as numeric(20,16));")[[1]]
> x
[1] 1
class(x)
[1] "numeric"

Cheers, Robert

-----Original Message-----
From: r-sig-db-bounces using r-project.org [mailto:r-sig-db-bounces using r-project.org] On Behalf Of Paul Gilbert
Sent: Monday, April 14, 2014 8:06 PM
To: r-sig-db using r-project.org
Subject: [R-sig-DB] RODBC in R-3.1.1 and R-3.0.3

I am seeing a difference in RODBC_1.3-10 behaviour in R-3.1.1 and 
R-3.0.3 when getting double precision numbers from  a database and I am 
wondering if others are seeing similar behaviour on other systems and 
with other database engines. This seems to be provoked by the change in 
type.convert().

This is happening for me with a Linux (Ubuntu 13.10 64-bit) PostgreSQL 
server, both running R on the server and running R on a Linux client 
(Mint 15 Cinnamon 2.0.14 64-bit). It should be possible to reproduce it 
as follows.

 > require("RODBC")
Loading required package: RODBC

# assuming user/password/host info in .odbc.ini
# and appropriate privileges:

 > con <-  odbcConnect(dsn='test')
 > sqlQuery(channel=con, "CREATE TABLE ZZ  ( id VARCHAR(40),
     v double precision DEFAULT  NULL );")
[1] "No Data"

 > sqlQuery(channel=con,
+     "INSERT INTO ZZ VALUES ( 'a', 0.2)")
character(0)

# RODBC in R-3.1.0 then gives
 > r <- sqlQuery(channel=con, "select v from ZZ where id='a';")
 > str(r)
'data.frame':	1 obs. of  1 variable:
  $ v: Factor w/ 1 level "0.20000000000000001": 1


 > r <- sqlQuery(channel=con,
      "select v - 0.2 from ZZ where id='a';")
 > str(r)
'data.frame':	1 obs. of  1 variable:
  $ ?column?: int 0

So the loss in accuracy that type.convert uses to decide it should 
return a factor seems to be generated by extra digits that odbc and/or 
postgresql are deciding should be added.

# RODBC in R-3.0.3 gives
 > r <- sqlQuery(channel=con, "select v from ZZ where id='a';")
 > str(r)
'data.frame':	1 obs. of  1 variable:
  $ v: num 0.2

 > print(r, digits=20)
                       v
1 0.2000000000000000111

# RPostgreSQL in R-3.1.0 gives (unchanged from R-3.0.3)
 > require("RPostgreSQL")

# assuming user/password info in .pgpass :
 > con <- dbConnect("PostgreSQL", dbname='test', host='192.168.1.152')
 > r <- dbGetQuery(con,"select v from ZZ where id='a';")
 >  str(r)
'data.frame':	1 obs. of  1 variable:
  $ v: num 0.2
 >  print(r, digits=20)
                       v
1 0.2000000000000000111

And in the psql client I get

test=> select v from ZZ where id='a';
   v
-----
  0.2
(1 row)

I'm working on a work-around for a package that uses RODBC and I am 
wondering if the difficulty is unique to my setup.

Thanks,
Paul Gilbert

_______________________________________________
R-sig-DB mailing list -- R Special Interest Group
R-sig-DB using r-project.org
https://stat.ethz.ch/mailman/listinfo/r-sig-db