RODBC 使用 sqlSave to MS SQL 时会丢失数字列的精度(并在日期上失败)

Question

为什么 RODBC 在使用 sqlSave 保存到 ms sql 时会丢失我的数字列的精度？

数字

在 R 中，我使用数字列创建以下数据帧。它们可能看起来已经四舍五入了，但这是一个印刷问题，它们不是四舍五入的。

>test <- data.frame(
          id=c(1,2,3),
          numbers=c(12345678.90,12345678.91,12345678.92)
          )
> str(test)
'data.frame':   3 obs. of  2 variables:
 $ id     : num  1 2 3
 $ numbers: num  12345679 12345679 12345679
> test$numbers==123456789
[1] FALSE FALSE FALSE

将其保存到数据库似乎表明丢失了一些精度：

> dbconn <- odbcDriverConnect("driver={SQL Server};server=HOSTNAME\SQL2014;database=test_db;trusted_connection=true")
> sqlSave(dbconn, test, fast=F, verbose=T)
Query: CREATE TABLE "test"  ("rownames" varchar(255), "id" float, "numbers" float)
Query: INSERT INTO "test" ( "rownames", "id", "numbers" ) VALUES ( '1', 1, 12345679 )
Query: INSERT INTO "test" ( "rownames", "id", "numbers" ) VALUES ( '2', 2, 12345679 )
Query: INSERT INTO "test" ( "rownames", "id", "numbers" ) VALUES ( '3', 3, 12345679 )

通过从数据库中取回数据帧，我们可以验证精度是否确实丢失：

> test2<-sqlFetch(dbconn, "test")
> test2
  id  numbers
1  1 12345679
2  2 12345679
3  3 12345679
> test2$numbers==12345679
[1] TRUE TRUE TRUE

浮点似乎是正确的列类型，那么有没有办法强制 RodbC 使用字符转换的替代方案？即使是默认的as.character也可以工作。

> as.character(test$numbers)
[1] "12345678.9"  "12345678.91" "12345678.92"

日期

日期类列会导致 ms sql 列的类型为 float，从而导致存储不正确的值：

> test <- data.frame(id=c(1,2,3),date=as.Date(c("2014-12-21","2014-12-22","2014-12-23")))
> sqlSave(dbconn, test, fast=F, verbose=T)
Query: CREATE TABLE "test"  ("rownames" varchar(255), "id" float, "date" float)
Query: INSERT INTO "test" ( "rownames", "id", "date" ) VALUES ( '1', 1, 2014-12-21 )
Query: INSERT INTO "test" ( "rownames", "id", "date" ) VALUES ( '2', 2, 2014-12-22 )
Query: INSERT INTO "test" ( "rownames", "id", "date" ) VALUES ( '3', 3, 2014-12-23 )
> test2<-sqlFetch(dbconn, "test")
> test2
   id date
 1  1 1981
 2  2 1980
 3  3 1979

由于日期被解释为公式：

> 2014-12-21
[1] 1981

---

> packageVersion("RODBC")
[1] ‘1.3.10’

Answer 1

在 RODBC 的代码中，似乎有内部调用来取消列出 as.matrix.dataframe，这可以解释这个问题：

> unlist(list(test))
     id1      id2      id3 numbers1 numbers2 numbers3 
       1        2        3 12345679 12345679 12345679 '

我最好的客人是围绕 RODBC 工作，并控制到字符的转换作为 varType 逻辑。

逻辑列被解释为 varchar，我们可以很容易地将其更改为 BIT 的

sqlSave2<-function(dbconn, dat, tablename = NULL, ...){
  # get the name of the variable passed as dat, taken from sqlSave implementation
  if (is.null(tablename)) 
    tablename <- if (length(substitute(dat)) == 1) 
      as.character(substitute(dat))
  else as.character(substitute(dat)[[2L]])
  if (length(tablename) != 1L) 
    stop(sQuote(tablename), " should be a name")
    varTypes <- sapply(dat, function(x){
    if(is.numeric(x)){
      "float"
      }else if(is.factor(x)|is.character(x)){
        paste0("[varchar](",max(nchar(as.character(x))),")")
        } else if(is.logical(x)){"BIT"} else if("Date"%in%class(x)) {
          "date"
          } else if ("POSIXt"%in%class(x)){
            "smalldatetime"
            }else{NA}})# let RODBC determine
  varTypes <- varTypes[!is.na(varTypes)]
  dat2<-as.data.frame(sapply(dat, function(x){
    if(is.numeric(x)){
      x2<-sprintf("%.15e",x)
      if(sum(is.na(x))>0)
        x2[is.na(x)]<-NA
      x2
    } else if(is.logical(x)){
      x2<-rep(0,length(x))
      x2[!is.na(x) & x==T]<-1
      if(sum(is.na(x))>0)
        x2[is.na(x)] <- NA
      x2
      } else as.character(x)
  }))
  sqlSave(dbconn, dat2, tablename, fast=FALSE, varTypes=varTypes, ...)
}

使用以下数据帧

> test <- data.frame(
    id=c(123456789.12,123456789.23,123456789.23), 
    date=as.Date(c("2014-12-21","2014-12-22","2014-12-23")),
    datetime=as.POSIXlt(c("2014-12-21 10:00","2014-12-22 11:00","2014-12-23 12:00")))
> str(test)
'data.frame':   3 obs. of  3 variables:
 $ id      : num  1.23e+08 1.23e+08 1.23e+08
 $ date    : Date, format: "2014-12-21" "2014-12-22" "2014-12-23"
 $ datetime: POSIXct, format: "2014-12-21 10:00:00" "2014-12-22 11:00:00" "2014-12-23 12:00:00"

我们得到而不是默认值：

> sqlSave(dbconn, test, fast=F, verbose=T)
Query: CREATE TABLE "test"  ("rownames" varchar(255), "id" float, "date" float, "datetime" float)
Query: INSERT INTO "test" ( "rownames", "id", "date", "datetime" ) VALUES ( '1', 123456789, 2014-12-21, 2014-12-21 10:00:00 )
Error in sqlSave(dbconn, test, fast = F, verbose = T) : 
  42000 102 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '10'.
[RODBC] ERROR: Could not SQLExecDirect 'INSERT INTO "test" ( "rownames", "id", "date", "datetime" ) VALUES ( '1', 123456789, 2014-12-21, 2014-12-21 10:00:00 )'

以下：

> sqlSave2(dbconn, test)
Query: CREATE TABLE "test"  ("rownames" varchar(255), "id" float, "date" date, "datetime" smalldatetime)
Query: INSERT INTO "test" ( "rownames", "id", "date", "datetime" ) VALUES ( '1', 1.234567891200000e+08, N'2014-12-21', '2014-12-21 10:00:00' )
Query: INSERT INTO "test" ( "rownames", "id", "date", "datetime" ) VALUES ( '2', 1.234567892300000e+08, N'2014-12-22', '2014-12-22 11:00:00' )
Query: INSERT INTO "test" ( "rownames", "id", "date", "datetime" ) VALUES ( '3', 1.234567892300000e+08, N'2014-12-23', '2014-12-23 12:00:00' )

通过以下验证：

> test2<-sqlFetch(dbconn, "test")
> test2
         id       date            datetime
1 123456789 2014-12-21 2014-12-21 10:00:00
2 123456789 2014-12-22 2014-12-22 11:00:00
3 123456789 2014-12-23 2014-12-23 12:00:00
> str(test2)
'data.frame':   3 obs. of  3 variables:
 $ id      : num  1.23e+08 1.23e+08 1.23e+08
 $ date    : Factor w/ 3 levels "2014-12-21","2014-12-22",..: 1 2 3
 $ datetime: POSIXct, format: "2014-12-21 10:00:00" "2014-12-22 11:00:00" "2014-12-23 12:00:00"
> test2$id==123456789
[1] FALSE FALSE FALSE