20

By default when I use dbGetQuery() from the DBI package it returns columns of type integer64 as the integer64 class of the bit64.

I then use dplyr to try and filter and manipulate my results but come into issues as dplyr does not support objects of type integer64.

Is it possible to set dbGetQuery() to return integer64 columns as class integer?

Michael Kirchner
  • 869
  • 1
  • 7
  • 17
user1165199
  • 6,351
  • 13
  • 44
  • 60
  • Why not just convert to integer after calling `dbGetQuery()` instead of doing it at that stage? – Michael Kirchner Jul 18 '17 at 16:07
  • 2
    I think your three options will be: (a) Michael's suggest to convert to integer post-query; (b) use SQL's `CAST(... as integer32)` within the query; or (c) take it to the issues board for whichever ODBC driver you are using (perhaps [`odbc`](https://github.com/rstats-db/odbc/issues)). I think the latter is most appropriate for the long-term, but since `bit64` is still not globally used/accepted in R, it may be an uphill battle. – r2evans Jul 18 '17 at 16:22
  • 1
    "Why not just convert to integer after calling dbGetQuery() instead of doing it at that stage? " It would have been neater to do it using an argument or something within dbGetQuery, but if there is no better way of doing it then I guess I'll have to – user1165199 Jul 19 '17 at 10:14

2 Answers2

20

The DBI spec provides this functionality via the bigint argument. Support will obviously vary between drivers.

dbConnect(drv, bigint="integer", ...)

The following values behave as follows:

"integer": always return as integer, silently overflow

"numeric": always return as numeric, silently round

"character": always return the decimal representation as character

"integer64": return as a data type that can be coerced using as.integer() (with warning on overflow), as.numeric() and as.character()

Source: https://cran.r-project.org/web/packages/DBI/vignettes/spec.html#_specification_17

elmato
  • 315
  • 2
  • 4
14

Even without full support of 64-bit integers (see GitHub issue), you still can use dplyr to mutate away from integer64:

library(dplyr, warn.conflicts = FALSE)
df <- data_frame(a = bit64::as.integer64(1:3), b = 1:3, c = 1.5:4)
df
#> # A tibble: 3 x 3
#>                 a     b     c
#>   <S3: integer64> <int> <dbl>
#> 1               1     1   1.5
#> 2               2     2   2.5
#> 3               3     3   3.5
df %>% mutate_if(bit64::is.integer64, as.integer)
#> # A tibble: 3 x 3
#>       a     b     c
#>   <int> <int> <dbl>
#> 1     1     1   1.5
#> 2     2     2   2.5
#> 3     3     3   3.5
krlmlr
  • 25,056
  • 14
  • 120
  • 217