Set dbGetQuery to return integer64 as integer

Question

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?

Fanchon

Why not just convert to integer after calling dbGetQuery() instead of doing it at that stage? – Puerility

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). 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. – Schoolman

"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 – Fanchon

Answer 1

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

Rudolfrudolfo

thank you - this worked for me and saved me!!! – Exploit

Answer 2

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

Hygrostat