dbColumnInfo error

[r2evans]

Expected behaviour

From DBI.pdf:

library(DBI)
conn2 <- dbConnect(RSQLite::SQLite(), ":memory:")
res2 <- dbSendQuery(conn2, "SELECT 1 AS a, 2 AS b")
dbColumnInfo(res2)
#   name    type
#1    a integer
#2    b integer
dbFetch(res2)
#   a b
#1 1 2
dbClearResult(res2)

Actual behaviour

library(DBI)
# ~/sql.yaml defines "SQL_DEV"
conn <- dbConnect(RSQLServer::SQLServer(), server="SQL_DEV", database = "mydatabase")
res <- dbSendQuery(conn, "SELECT 1 AS a, 2 AS b")
dbColumnInfo(res)
# Error in `$<-.data.frame`(`*tmp*`, "field.name", value = "a") : 
#   replacement has 1 row, data has 0
dbFetch(res)
#   a b
#1 1 2
dbClearResult(res)
# [1] TRUE

Steps to reproduce behaviour

(as above)

dbGetInfo(conn)$db.version
# [1] '12.0.2000'
devtools::session_info()
# Session info -------------------------------------------------------------------
#  setting  value                       
#  version  R version 3.3.1 (2016-06-21)
#  system   x86_64, mingw32             
#  ui       RTerm                       
#  language (EN)                        
#  collate  English_United States.1252  
#  tz       America/Los_Angeles         
#  date     2016-08-22                  
# Packages -----------------------------------------------------------------------
#  package    * version    date       source                                     
#  assertthat   0.1        2013-12-06 CRAN (R 3.3.0)                             
#  DBI        * 0.5        2016-08-22 Github (rstats-db/DBI@1e37697)             
#  devtools     1.12.0     2016-06-24 CRAN (R 3.3.1)                             
#  digest       0.6.10     2016-08-02 CRAN (R 3.3.1)                             
#  dplyr        0.5.0.9000 2016-08-22 Github (javierluraschi/dplyr@6220be8)      
#  evaluate     0.9        2016-04-29 CRAN (R 3.3.0)                             
#  htmltools    0.3.5      2016-03-21 CRAN (R 3.3.0)                             
#  lazyeval     0.2.0      2016-06-12 CRAN (R 3.3.1)                             
#  magrittr     1.5        2014-11-22 CRAN (R 3.3.0)                             
#  memoise      1.0.0      2016-01-29 CRAN (R 3.3.0)                             
#  purrr        0.2.2      2016-06-18 CRAN (R 3.3.1)                             
#  r2         * 0.4.22     2016-08-22 local                                      
#  R6           2.1.3      2016-08-19 CRAN (R 3.3.1)                             
#  Rcpp         0.12.6     2016-07-19 CRAN (R 3.3.1)                             
#  rJava        0.9-8      2016-01-07 CRAN (R 3.3.0)                             
#  rmarkdown    1.0        2016-07-08 CRAN (R 3.3.1)                             
#  RSQLServer   0.2.099    2016-08-22 Github (imanuelcostigan/RSQLServer@1692c2b)
#  stringi      1.1.1      2016-05-27 CRAN (R 3.3.0)                             
#  stringr      1.1.0      2016-08-19 CRAN (R 3.3.1)                             
#  tibble       1.1        2016-07-04 CRAN (R 3.3.1)                             
#  withr        1.0.2      2016-06-20 CRAN (R 3.3.1)                             
#  yaml         2.1.13     2014-06-12 CRAN (R 3.3.0)                             

[imanuelcostigan]

Does the issue persist when using 27ab759 (DBI 0.5 compatibility branch)?

[r2evans]

Yes. The relevant change to above:

devtools::session_info()
# ...
# RSQLServer   0.2.099    2016-08-22 Github (imanuelcostigan/RSQLServer@27ab759)
# ...

Trace from the session, if it helps:

trace("dbColumnInfo", browser, exit = browser, signature = "SQLServerResult")
# [1] "dbColumnInfo"
dbColumnInfo(res)
# Tracing dbColumnInfo(res) on entry 
# Called from: eval(expr, envir, enclos)
# Browse[1]> 
# debug: {
#     cols <- rJava::.jcall(res@md, "I", "getColumnCount")
#     df <- data_frame(field.name = character(), field.type = character(), 
#         data.type = character())
#     if (cols < 1) 
#         return(df)
#     for (i in 1:cols) {
#         df$field.name[i] <- rJava::.jcall(res@md, "S", "getColumnName", 
#             i)
#         df$field.type[i] <- rJava::.jcall(res@md, "S", "getColumnTypeName", 
#             i)
#         ct <- rJava::.jcall(res@md, "I", "getColumnType", i)
#         df$data.type[i] <- jdbcToRType(ct)
#     }
#     df
# }
# Browse[2]> 
n
# debug: cols <- rJava::.jcall(res@md, "I", "getColumnCount")
# Browse[2]> 
n
# debug: df <- data_frame(field.name = character(), field.type = character(), 
#     data.type = character())
# Browse[2]> 
cols
# [1] 2
# Browse[2]> 
n
# debug: if (cols < 1) return(df)
# Browse[2]> 
df
# # A tibble: 0 x 3
# # ... with 3 variables: field.name <chr>, field.type <chr>, data.type <chr>
# Browse[2]> 
n
# debug: for (i in 1:cols) {
#     df$field.name[i] <- rJava::.jcall(res@md, "S", "getColumnName", 
#         i)
#     df$field.type[i] <- rJava::.jcall(res@md, "S", "getColumnTypeName", 
#         i)
#     ct <- rJava::.jcall(res@md, "I", "getColumnType", i)
#     df$data.type[i] <- jdbcToRType(ct)
# }
n
# Browse[2]> 
# debug: df$field.name[i] <- rJava::.jcall(res@md, "S", "getColumnName", 
#     i)
# Browse[2]> 
# Error in `$<-.data.frame`(`*tmp*`, "field.name", value = "a") : 
#   replacement has 1 row, data has 0
# Tracing dbColumnInfo(res) on exit 
# Browse[3]> 
rJava::.jcall(res@md, "S", "getColumnName", i)
# [1] "a"
# Browse[3]> 
i
# [1] 1
# Browse[3]> 

If I don't run the for loop and instead run this code, I get a feasible data.frame:

df <- do.call("rbind", lapply(seq_len(cols), function(i) {
    list(field.name = rJava::.jcall(res@md, "S", "getColumnName", i),
         field.type = rJava::.jcall(res@md, "S", "getColumnTypeName", i),
         data.type = jdbcToRType(rJava::.jcall(res@md, "I", "getColumnType", i)))
}))
df
#      field.name field.type data.type
# [1,] "a"        "int"      "integer"
# [2,] "b"        "int"      "integer"

which gives what I think the data.frame is supposed to be. (So the problem is with a data.frame of 0 rows, not with the java connection or database stuff, I think.)

[r2evans]

Suggested patch:

--- dbi-methods.R.orig  2016-08-22 16:22:07.659563100 -0700
+++ dbi-methods.R       2016-08-22 16:24:48.669131700 -0700
@@ -485,21 +485,24 @@


 #' @rdname SQLServerResult-class
-#' @importFrom dplyr data_frame
+#' @importFrom dplyr bind_rows data_frame
 #' @export
 setMethod("dbColumnInfo", "SQLServerResult", def = function (res, ...) {
   # Inspired by RJDBC method for JDBCResult
   # https://github.com/s-u/RJDBC/blob/1b7ccd4677ea49a93d909d476acf34330275b9ad/R/class.R
   cols <- rJava::.jcall(res@md, "I", "getColumnCount")
+  if (cols < 1) {
   df <- data_frame(field.name = character(),
     field.type = character(),
-    data.type = character())
-  if (cols < 1) return(df)
-  for (i in 1:cols) {
-    df$field.name[i] <- rJava::.jcall(res@md, "S", "getColumnName", i)
-    df$field.type[i] <- rJava::.jcall(res@md, "S", "getColumnTypeName", i)
-    ct <- rJava::.jcall(res@md, "I", "getColumnType", i)
-    df$data.type[i] <- jdbcToRType(ct)
+                     data.type = character()))
+  } else {
+    df <- dplyr::bind_rows(
+      lapply(seq_len(cols), function(i) {
+        list(field.name = rJava::.jcall(res@md, "S", "getColumnName", i),
+             field.type = rJava::.jcall(res@md, "S", "getColumnTypeName", i),
+             data.type = jdbcToRType(rJava::.jcall(res@md, "I", "getColumnType", i)))
+      })
+    )
   }
   df
 })

Is there a vectorized version of "getColumnName" and friends? Seems like this should be able to be resolved in 3 calls to java and not 3*cols, such as:

  i <- seq_len(cols)
  data_frame(field.name = rJava::.jcall(res@md, "S+", "getColumnName", i),
             field.type = rJava::.jcall(res@md, "S+", "getColumnTypeName", i),
             field.type = jdbcToRType(rJava::.jcall(res@md, "S+", "getColumnType", i)))

(The "S+" is not right, and from some research it looks like returning vectors with rJava is not straight-forward, but that's a placeholder for identifying the returnSig.)