search

r-dbi / odbc

Connect to ODBC databases (using the DBI interface)
https://odbc.r-dbi.org/
Other
392 stars 107 forks source link

R 4.3.1 character columns return every other character #610

Closed eroten closed 11 months ago

eroten commented 1 year ago

Issue Description and Expected Result

In R version 4.3.1, odbc version 1.3.5, columns of type character are only reading in every other character (dropping even-indexed characters). This is the same behavior as noted previously in #283

Database

Microsoft SQL Server Version: 13.00.6419

Examples

Pay attention to the PARK_UNIT_NAME column.

Reprex 1 (R 4.3.1)

library(DBI)
library(odbc)

con <- DBI::dbConnect(
  odbc::odbc(),
  DSN = "CD_RESEARCH_WEB",
  Uid = "",
  Pwd = ""
)

con
#> <OdbcConnection> AZDBSQLCL11\AZDBSQLCL11
#>   Database: CD_RESEARCH_WEB
#>   Microsoft SQL Server Version: 13.00.6419

DBI::dbGetQuery(con, "SELECT * FROM PARK_UNIT")[1:10,1:4]
#>    PARK_UNIT_SK PARK_UNIT_ID PARK_UNIT_ID_CURRENT      PARK_UNIT_NAME
#> 1           811            0                  094        Lc ieRgoa ri
#> 2           812            0                  095 Rvrt ie rewyRgoa ri
#> 3           813            0                  096       Dkt alRgoa ri
#> 4           814            0                  097      BsetCekRgoa ri
#> 5           815            0                  098       TotBokRgoa ri
#> 6           816            0                  099       Tn cmd einlPr
#> 7           817            1                  100    NrhatDaoa einlTa
#> 8           818            1                  101    NrhatDaoa einlTa
#> 9           819            1                  102      CdrLk amRgoa a
#> 10          820            1                  103      SigeCekRgoa ri

devtools::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#>  setting  value
#>  version  R version 4.3.1 (2023-06-16)
#>  os       macOS Ventura 13.6
#>  system   x86_64, darwin20
#>  ui       X11
#>  language (EN)
#>  collate  en_US.UTF-8
#>  ctype    en_US.UTF-8
#>  tz       America/Chicago
#>  date     2023-10-04
#>  pandoc   3.1.6.1 @ /usr/local/bin/ (via rmarkdown)
#> 
#> ─ Packages ───────────────────────────────────────────────────────────────────
#>  package     * version    date (UTC) lib source
#>  assertthat    0.2.1      2019-03-21 [1] CRAN (R 4.3.0)
#>  bit           4.0.5      2022-11-15 [1] CRAN (R 4.3.0)
#>  bit64         4.0.5      2020-08-30 [1] CRAN (R 4.3.0)
#>  blob          1.2.4      2023-03-17 [1] CRAN (R 4.3.0)
#>  cachem        1.0.8      2023-05-01 [1] CRAN (R 4.3.0)
#>  callr         3.7.3      2022-11-02 [1] CRAN (R 4.3.0)
#>  cli           3.6.1      2023-03-23 [1] CRAN (R 4.3.0)
#>  crayon        1.5.2      2022-09-29 [1] CRAN (R 4.3.0)
#>  DBI         * 1.1.3      2022-06-18 [1] CRAN (R 4.3.0)
#>  devtools      2.4.5      2022-10-11 [1] CRAN (R 4.3.0)
#>  digest        0.6.33     2023-07-07 [1] CRAN (R 4.3.0)
#>  ellipsis      0.3.2      2021-04-29 [1] CRAN (R 4.3.0)
#>  evaluate      0.22       2023-09-29 [1] CRAN (R 4.3.0)
#>  fastmap       1.1.1      2023-02-24 [1] CRAN (R 4.3.0)
#>  fs            1.6.3      2023-07-20 [1] CRAN (R 4.3.0)
#>  glue          1.6.2      2022-02-24 [1] CRAN (R 4.3.0)
#>  hms           1.1.3      2023-03-21 [1] CRAN (R 4.3.0)
#>  htmltools     0.5.6      2023-08-10 [1] CRAN (R 4.3.0)
#>  htmlwidgets   1.6.2      2023-03-17 [1] CRAN (R 4.3.0)
#>  httpuv        1.6.11     2023-05-11 [1] CRAN (R 4.3.0)
#>  keyring       1.3.1      2022-10-27 [1] CRAN (R 4.3.0)
#>  knitr         1.44       2023-09-11 [1] CRAN (R 4.3.0)
#>  later         1.3.1      2023-05-02 [1] CRAN (R 4.3.0)
#>  lifecycle     1.0.3      2022-10-07 [1] CRAN (R 4.3.0)
#>  magrittr      2.0.3      2022-03-30 [1] CRAN (R 4.3.0)
#>  memoise       2.0.1      2021-11-26 [1] CRAN (R 4.3.0)
#>  mime          0.12       2021-09-28 [1] CRAN (R 4.3.0)
#>  miniUI        0.1.1.1    2018-05-18 [1] CRAN (R 4.3.0)
#>  odbc        * 1.3.5      2023-06-29 [1] CRAN (R 4.3.0)
#>  pkgbuild      1.4.2      2023-06-26 [1] CRAN (R 4.3.0)
#>  pkgconfig     2.0.3      2019-09-22 [1] CRAN (R 4.3.0)
#>  pkgload       1.3.3      2023-09-22 [1] CRAN (R 4.3.0)
#>  prettyunits   1.2.0      2023-09-24 [1] CRAN (R 4.3.0)
#>  processx      3.8.2      2023-06-30 [1] CRAN (R 4.3.0)
#>  profvis       0.3.8      2023-05-02 [1] CRAN (R 4.3.0)
#>  promises      1.2.1      2023-08-10 [1] CRAN (R 4.3.0)
#>  ps            1.7.5      2023-04-18 [1] CRAN (R 4.3.0)
#>  purrr         1.0.2      2023-08-10 [1] CRAN (R 4.3.0)
#>  R.cache       0.16.0     2022-07-21 [1] CRAN (R 4.3.0)
#>  R.methodsS3   1.8.2      2022-06-13 [1] CRAN (R 4.3.0)
#>  R.oo          1.25.0     2022-06-12 [1] CRAN (R 4.3.0)
#>  R.utils       2.12.2     2022-11-11 [1] CRAN (R 4.3.0)
#>  R6            2.5.1      2021-08-19 [1] CRAN (R 4.3.0)
#>  Rcpp          1.0.11     2023-07-06 [1] CRAN (R 4.3.0)
#>  remotes       2.4.2.1    2023-07-18 [1] CRAN (R 4.3.0)
#>  reprex        2.0.2      2022-08-17 [1] CRAN (R 4.3.0)
#>  rlang         1.1.1      2023-04-28 [1] CRAN (R 4.3.0)
#>  rmarkdown     2.25       2023-09-18 [1] CRAN (R 4.3.0)
#>  rstudioapi    0.15.0     2023-07-07 [1] CRAN (R 4.3.0)
#>  sessioninfo   1.2.2      2021-12-06 [1] CRAN (R 4.3.0)
#>  shiny         1.7.5      2023-08-12 [1] CRAN (R 4.3.0)
#>  stringi       1.7.12     2023-01-11 [1] CRAN (R 4.3.0)
#>  stringr       1.5.0      2022-12-02 [1] CRAN (R 4.3.0)
#>  styler        1.10.2     2023-08-29 [1] CRAN (R 4.3.0)
#>  urlchecker    1.0.1      2021-11-30 [1] CRAN (R 4.3.0)
#>  usethis       2.2.2.9000 2023-09-27 [1] Github (r-lib/usethis@467ff57)
#>  vctrs         0.6.3      2023-06-14 [1] CRAN (R 4.3.0)
#>  withr         2.5.1      2023-09-26 [1] CRAN (R 4.3.0)
#>  xfun          0.40       2023-08-09 [1] CRAN (R 4.3.0)
#>  xtable        1.8-4      2019-04-21 [1] CRAN (R 4.3.0)
#>  yaml          2.3.7      2023-01-23 [1] CRAN (R 4.3.0)
#> 
#>  [1] /Library/Frameworks/R.framework/Versions/4.3-x86_64/Resources/library
#> 
#> ──────────────────────────────────────────────────────────────────────────────

Created on 2023-10-04 with reprex v2.0.2

Reprex 2 (R 4.2.3)

library(DBI)
library(odbc)

con <- DBI::dbConnect(
  odbc::odbc(),
  DSN = "CD_RESEARCH_WEB",
  Uid = "",
  Pwd = ""
)

con
#> <OdbcConnection> AZDBSQLCL11\AZDBSQLCL11
#>   Database: CD_RESEARCH_WEB
#>   Microsoft SQL Server Version: 13.00.6419

DBI::dbGetQuery(con, "SELECT * FROM PARK_UNIT")[1:10,1:4]
#>    PARK_UNIT_SK PARK_UNIT_ID PARK_UNIT_ID_CURRENT
#> 1           811          094                  094
#> 2           812          095                  095
#> 3           813          096                  096
#> 4           814          097                  097
#> 5           815          098                  098
#> 6           816          099                  099
#> 7           817          100                  100
#> 8           818          101                  101
#> 9           819          102                  102
#> 10          820          103                  103
#>                            PARK_UNIT_NAME
#> 1                Luce Line Regional Trail
#> 2  River to River Greenway Regional Trail
#> 3              Dakota Rail Regional Trail
#> 4            Bassett Creek Regional Trail
#> 5              Trout Brook Regional Trail
#> 6              Tony Schmidt Regional Park
#> 7       Northeast Diagonal Regional Trail
#> 8       Northeast Diagonal Regional Trail
#> 9           Cedar Lake Farm Regional Park
#> 10           Shingle Creek Regional Trail

devtools::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#>  setting  value
#>  version  R version 4.2.3 Patched (2023-03-29 r84123)
#>  os       macOS Big Sur ... 10.16
#>  system   x86_64, darwin17.0
#>  ui       X11
#>  language (EN)
#>  collate  en_US.UTF-8
#>  ctype    en_US.UTF-8
#>  tz       America/Chicago
#>  date     2023-10-04
#>  pandoc   3.1.6.1 @ /usr/local/bin/ (via rmarkdown)
#> 
#> ─ Packages ───────────────────────────────────────────────────────────────────
#>  package     * version date (UTC) lib source
#>  assertthat    0.2.1   2019-03-21 [1] CRAN (R 4.2.0)
#>  bit           4.0.5   2022-11-15 [1] CRAN (R 4.2.1)
#>  bit64         4.0.5   2020-08-30 [1] CRAN (R 4.2.0)
#>  blob          1.2.4   2023-03-17 [1] CRAN (R 4.2.2)
#>  cachem        1.0.8   2023-05-01 [1] CRAN (R 4.2.0)
#>  callr         3.7.3   2022-11-02 [1] CRAN (R 4.2.0)
#>  cli           3.6.1   2023-03-23 [1] CRAN (R 4.2.0)
#>  crayon        1.5.2   2022-09-29 [1] CRAN (R 4.2.0)
#>  DBI         * 1.1.3   2022-06-18 [1] CRAN (R 4.2.0)
#>  devtools      2.4.5   2022-10-11 [1] CRAN (R 4.2.0)
#>  digest        0.6.31  2022-12-11 [1] CRAN (R 4.2.0)
#>  ellipsis      0.3.2   2021-04-29 [1] CRAN (R 4.2.0)
#>  evaluate      0.21    2023-05-05 [1] CRAN (R 4.2.0)
#>  fastmap       1.1.1   2023-02-24 [1] CRAN (R 4.2.0)
#>  fs            1.6.2   2023-04-25 [1] CRAN (R 4.2.0)
#>  glue          1.6.2   2022-02-24 [1] CRAN (R 4.2.0)
#>  hms           1.1.3   2023-03-21 [1] CRAN (R 4.2.0)
#>  htmltools     0.5.5   2023-03-23 [1] CRAN (R 4.2.0)
#>  htmlwidgets   1.6.2   2023-03-17 [1] CRAN (R 4.2.0)
#>  httpuv        1.6.11  2023-05-11 [1] CRAN (R 4.2.0)
#>  keyring       1.3.1   2022-10-27 [1] CRAN (R 4.2.0)
#>  knitr         1.42    2023-01-25 [1] CRAN (R 4.2.0)
#>  later         1.3.1   2023-05-02 [1] CRAN (R 4.2.0)
#>  lifecycle     1.0.3   2022-10-07 [1] CRAN (R 4.2.0)
#>  magrittr      2.0.3   2022-03-30 [1] CRAN (R 4.2.0)
#>  memoise       2.0.1   2021-11-26 [1] CRAN (R 4.2.0)
#>  mime          0.12    2021-09-28 [1] CRAN (R 4.2.0)
#>  miniUI        0.1.1.1 2018-05-18 [1] CRAN (R 4.2.1)
#>  odbc        * 1.3.5   2023-06-29 [1] CRAN (R 4.2.0)
#>  pkgbuild      1.4.0   2022-11-27 [1] CRAN (R 4.2.0)
#>  pkgconfig     2.0.3   2019-09-22 [1] CRAN (R 4.2.0)
#>  pkgload       1.3.2   2022-11-16 [1] CRAN (R 4.2.1)
#>  prettyunits   1.1.1   2020-01-24 [1] CRAN (R 4.2.0)
#>  processx      3.8.1   2023-04-18 [1] CRAN (R 4.2.0)
#>  profvis       0.3.8   2023-05-02 [1] CRAN (R 4.2.0)
#>  promises      1.2.0.1 2021-02-11 [1] CRAN (R 4.2.0)
#>  ps            1.7.5   2023-04-18 [1] CRAN (R 4.2.0)
#>  purrr         1.0.1   2023-01-10 [1] CRAN (R 4.2.0)
#>  R.cache       0.16.0  2022-07-21 [1] CRAN (R 4.2.0)
#>  R.methodsS3   1.8.2   2022-06-13 [1] CRAN (R 4.2.0)
#>  R.oo          1.25.0  2022-06-12 [1] CRAN (R 4.2.0)
#>  R.utils       2.12.2  2022-11-11 [1] CRAN (R 4.2.0)
#>  R6            2.5.1   2021-08-19 [1] CRAN (R 4.2.0)
#>  Rcpp          1.0.10  2023-01-22 [1] CRAN (R 4.2.2)
#>  remotes       2.4.2   2021-11-30 [1] CRAN (R 4.2.0)
#>  reprex        2.0.2   2022-08-17 [1] CRAN (R 4.2.0)
#>  rlang         1.1.1   2023-04-28 [1] CRAN (R 4.2.0)
#>  rmarkdown     2.21    2023-03-26 [1] CRAN (R 4.2.0)
#>  rstudioapi    0.14    2022-08-22 [1] CRAN (R 4.2.0)
#>  sessioninfo   1.2.2   2021-12-06 [1] CRAN (R 4.2.0)
#>  shiny         1.7.4   2022-12-15 [1] CRAN (R 4.2.0)
#>  stringi       1.7.12  2023-01-11 [1] CRAN (R 4.2.0)
#>  stringr       1.5.0   2022-12-02 [1] CRAN (R 4.2.0)
#>  styler        1.9.1   2023-03-04 [1] CRAN (R 4.2.0)
#>  urlchecker    1.0.1   2021-11-30 [1] CRAN (R 4.2.0)
#>  usethis       2.1.6   2022-05-25 [1] CRAN (R 4.2.0)
#>  vctrs         0.6.2   2023-04-19 [1] CRAN (R 4.2.0)
#>  withr         2.5.0   2022-03-03 [1] CRAN (R 4.2.0)
#>  xfun          0.39    2023-04-20 [1] CRAN (R 4.2.0)
#>  xtable        1.8-4   2019-04-21 [1] CRAN (R 4.2.0)
#>  yaml          2.3.7   2023-01-23 [1] CRAN (R 4.2.0)
#> 
#>  [1] /Library/Frameworks/R.framework/Versions/4.2/Resources/library
#> 
#> ──────────────────────────────────────────────────────────────────────────────

Created on 2023-10-04 with reprex v2.0.2

Workarounds tried

Thanks to @RaSieb for finding the 4.2.3 workaround https://github.com/r-dbi/odbc/issues/283#issuecomment-1732967651

detule commented 1 year ago

Thanks for the report.

Can you also send along the table / column definitions?

You may be able to use odbcConnectionColumns.

eroten commented 1 year ago 
tibble::tribble(
                                                ~name,    ~field.type, ~table_name, ~schema_name,     ~catalog_name, ~data_type, ~column_size, ~buffer_length, ~decimal_digits, ~numeric_precision_radix,
                                       "PARK_UNIT_SK", "int identity", "PARK_UNIT",        "xxx", "CD_RESEARCH_WEB",         4L,           10,              4,              0L,                      10L,
                                       "PARK_UNIT_ID",     "nvarchar", "PARK_UNIT",        "xxx", "CD_RESEARCH_WEB",        -9L,            3,              6,              0L,                       0L,
                               "PARK_UNIT_ID_CURRENT",        "ntext", "PARK_UNIT",        "xxx", "CD_RESEARCH_WEB",       -10L,   1073741823,     2147483646,              0L,                       0L,
                                     "PARK_UNIT_NAME",     "nvarchar", "PARK_UNIT",        "xxx", "CD_RESEARCH_WEB",        -9L,          200,            400,              0L,                       0L,
                          "PARK_UNIT_HISTORICAL_NAME",     "nvarchar", "PARK_UNIT",        "xxx", "CD_RESEARCH_WEB",        -9L,          200,            400,              0L,                       0L,
                                          "PARK_TYPE",     "nvarchar", "PARK_UNIT",        "xxx", "CD_RESEARCH_WEB",        -9L,            2,              4,              0L,                       0L,
                                     "IMPL_AGENCY_ID",     "nvarchar", "PARK_UNIT",        "xxx", "CD_RESEARCH_WEB",        -9L,            2,              4,              0L,                       0L,
                             "IMPL_AGENCY_FINANCE_ID",     "nvarchar", "PARK_UNIT",        "xxx", "CD_RESEARCH_WEB",        -9L,            2,              4,              0L,                       0L,
                                          "COUNTY_ID",     "nvarchar", "PARK_UNIT",        "xxx", "CD_RESEARCH_WEB",        -9L,            3,              6,              0L,                       0L,
                                      "RECORD_STATUS",     "nvarchar", "PARK_UNIT",        "xxx", "CD_RESEARCH_WEB",        -9L,            1,              2,              0L,                       0L,
                                        "UPDATE_DATE",    "datetime2", "PARK_UNIT",        "xxx", "CD_RESEARCH_WEB",        -9L,           27,             54,              0L,                       0L,
                                          "UPDATE_BY",     "nvarchar", "PARK_UNIT",        "xxx", "CD_RESEARCH_WEB",        -9L,           30,             60,              0L,                       0L,
                                  "REASON_FOR_CHANGE",     "nvarchar", "PARK_UNIT",        "xxx", "CD_RESEARCH_WEB",        -9L,         4000,           8000,              0L,                       0L,
                                   "PARK_UNIT_STATUS",     "nvarchar", "PARK_UNIT",        "xxx", "CD_RESEARCH_WEB",        -9L,            4,              8,              0L,                       0L
                          )
detule commented 1 year ago

Hi - had a chance to look into this.

It's again an issue with the CRAN version of the package and we've opened an issue with the maintainer there. In the meantime, I was able to build locally from source with:

install.packages("odbc", type="source")

Thus installed, did not see any issues with characters being swallowed.

You said this did not work for you - can you provide a bit more detail?

Thanks again for the report.

eroten commented 1 year ago

When I use odbc after building from source, it crashes RStudio when I rundbConnect(). I don't have issues with RStudio crashing when I install odbc normally.

baderstine commented 1 year ago

Same.

detule commented 11 months ago

Hello - can you try again after reinstalling the CRAN binary/package?

The CRAN binary was rebuilt in a way, I believe, that should fix this issue.

Thanks

detule commented 11 months ago

Checked with the rebuilt CRAN package / confirmed issue fixed.

RaSieb commented 11 months ago

I can also confirm, uninstalling and re-installing the odbc package solves the issue. Thanks a lot for fixing this!

baderstine commented 11 months ago

agreed with above.