Using dbplyr with MS SQL (using DBI/odbc), I have queries that return integer64s, e.g.,
> str(encounters)
Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 139879 obs. of 1 variable:
$ ENCOUNTER:integer64 1802 8380 8713 16726 17210 24539 34136 34827 ...
I write this data to a database table:
DBI::dbWriteTable(dbconnectionABC, "xyz.abcdef.encounter", encounters)
MS SQL Server Management Studio reports this data type:
ENCOUNTER(varchar(255), null)
How did an integer64 become a varchar? Is this a bug, or expected behavior I don't understand?
Oddly, when displaying the tibble, the first three integer64s are shown as strings. Is this related?
> head(encounters)
# A tibble: 6 x 1
ENCOUNTER
<S3: integer64>
1 " 1802"
2 " 8380"
3 " 8713"
4 16726
5 17210
Here's the sessionInfo if relevant:
> sessionInfo()
R version 3.5.0 (2018-04-23)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows >= 8 x64 (build 9200)
Matrix products: default
locale:
[1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United States.1252
[3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C
[5] LC_TIME=English_United States.1252
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] bindrcpp_0.2.2 ggridges_0.5.1 writexl_1.1 readxl_1.1.0 kableExtra_0.9.0 dbplyr_1.2.2
[7] odbc_1.1.6 DBI_1.0.0 forcats_0.3.0 stringr_1.3.1 dplyr_0.7.7 purrr_0.2.5
[13] readr_1.1.1 tidyr_0.8.1 tibble_1.4.2 ggplot2_3.1.0 tidyverse_1.2.1
loaded via a namespace (and not attached):
[1] tidyselect_0.2.5 haven_1.1.2 lattice_0.20-35 colorspace_1.3-2 viridisLite_0.3.0 htmltools_0.3.6
[7] mgcv_1.8-24 yaml_2.2.0 base64enc_0.1-3 utf8_1.1.4 blob_1.1.1 rlang_0.3.0.1
[13] pillar_1.3.0 glue_1.3.0 withr_2.1.2 bit64_0.9-7 modelr_0.1.2 bindr_0.1.1
[19] plyr_1.8.4 munsell_0.5.0 gtable_0.2.0 cellranger_1.1.0 rvest_0.3.2 evaluate_0.12
[25] labeling_0.3 knitr_1.20 fansi_0.4.0 highr_0.7 broom_0.5.0 Rcpp_0.12.19
[31] scales_1.0.0 backports_1.1.2 jsonlite_1.5 bit_1.1-14 hms_0.4.2 digest_0.6.18
[37] stringi_1.2.4 grid_3.5.0 rprojroot_1.3-2 cli_1.0.1 tools_3.5.0 magrittr_1.5
[43] lazyeval_0.2.1 crayon_1.3.4 pkgconfig_2.0.2 Matrix_1.2-14 xml2_1.2.0 lubridate_1.7.4
[49] assertthat_0.2.0 rmarkdown_1.10 httr_1.3.1 rstudioapi_0.8 R6_2.3.0 nlme_3.1-137
[55] compiler_3.5.0
>````