odbc icon indicating copy to clipboard operation
odbc copied to clipboard

Published 20 hours ago • verified publisher icon r-dbi

Error in result_fetch(res@ptr, n) : wstring_convert::to_bytes with odbc 1.2.3 +

Open smmurphy opened this issue 4 years ago • 11 comments

Issue Description and Expected Result

When querying tables with long text entries I get the error Error in result_fetch(res@ptr, n) : wstring_convert::to_bytes when querying the table with select *, using odbc version 1.2.3 or above. Omitting such columns from the query returns results as expected. Switching back to odbc 1.2.2 also returns results as expected on such tables using select *.

Database

PostgreSQL Version: 11.0.8

Session Info 
devtools::session_info()
#> output

setting value
version R version 4.0.2 (2020-06-22) os Ubuntu 18.04.5 LTS
system x86_64, linux-gnu
ui RStudio
language (EN)
collate C.UTF-8
ctype C.UTF-8
tz Etc/UTC
date 2020-12-29

─ Packages ──────────────────────────────────────────────────────────────────────────────── package * version date lib source
assertthat 0.2.1 2019-03-21 [1] RSPM (R 4.0.2) backports 1.1.8 2020-06-17 [1] RSPM (R 4.0.2) bit 4.0.4 2020-08-04 [1] RSPM (R 4.0.2) bit64 4.0.2 2020-07-30 [1] RSPM (R 4.0.2) blob 1.2.1 2020-01-20 [1] RSPM (R 4.0.2) callr 3.4.3 2020-03-28 [1] RSPM (R 4.0.2) cli 2.0.2 2020-02-28 [1] RSPM (R 4.0.2) config 0.3 2018-03-27 [1] RSPM (R 4.0.2) crayon 1.3.4 2017-09-16 [1] RSPM (R 4.0.2) curl 4.3 2019-12-02 [1] RSPM (R 4.0.2) data.table 1.13.0 2020-07-24 [1] RSPM (R 4.0.2) DBI * 1.1.0 2019-12-15 [1] RSPM (R 4.0.2) desc 1.2.0 2018-05-01 [1] RSPM (R 4.0.2) devtools 2.3.1 2020-07-21 [1] RSPM (R 4.0.2) digest 0.6.25 2020-02-23 [1] RSPM (R 4.0.2) ellipsis 0.3.1 2020-05-15 [1] RSPM (R 4.0.2) fansi 0.4.1 2020-01-08 [1] RSPM (R 4.0.2) fs 1.5.0 2020-07-31 [1] RSPM (R 4.0.2) glue 1.4.1 2020-05-13 [1] RSPM (R 4.0.2) hms 0.5.3 2020-01-08 [1] RSPM (R 4.0.2) httr 1.4.2 2020-07-20 [1] RSPM (R 4.0.2) jsonlite 1.7.0 2020-06-25 [1] RSPM (R 4.0.2) magrittr 1.5 2014-11-22 [1] RSPM (R 4.0.2) memoise 1.1.0 2017-04-21 [1] RSPM (R 4.0.2) odbc * 1.2.3 2020-06-18 [1] RSPM (R 4.0.2) paws 0.1.9 2020-08-04 [1] RSPM (R 4.0.2) paws.common 0.3.3 2020-07-19 [1] RSPM (R 4.0.2) paws.security.identity 0.1.9 2020-08-03 [1] RSPM (R 4.0.2) pkgbuild 1.1.0 2020-07-13 [1] RSPM (R 4.0.2) pkgconfig 2.0.3 2019-09-22 [1] RSPM (R 4.0.2) pkgload 1.1.0 2020-05-29 [1] RSPM (R 4.0.2) prettyunits 1.1.1 2020-01-24 [1] RSPM (R 4.0.2) processx 3.4.3 2020-07-05 [1] RSPM (R 4.0.2) ps 1.3.4 2020-08-11 [1] RSPM (R 4.0.2) R6 2.4.1 2019-11-12 [1] RSPM (R 4.0.2) Rcpp 1.0.5 2020-07-06 [1] RSPM (R 4.0.2) remotes 2.2.0 2020-07-21 [1] RSPM (R 4.0.2) rlang 0.4.7 2020-07-09 [1] RSPM (R 4.0.2) rprojroot 1.3-2 2018-01-03 [1] RSPM (R 4.0.2) rstudioapi 0.11 2020-02-07 [1] RSPM (R 4.0.2) sessioninfo 1.1.1 2018-11-05 [1] RSPM (R 4.0.2) testthat 2.3.2 2020-03-02 [1] RSPM (R 4.0.2) usethis 1.6.1 2020-04-29 [1] RSPM (R 4.0.2) vctrs 0.3.2 2020-07-15 [1] RSPM (R 4.0.2) vibrantR * 0.1.2.0.0.0.1600885364 2020-11-03 [1] RSPM (R 4.0.2) withr 2.2.0 2020-04-20 [1] RSPM (R 4.0.2) yaml 2.2.1 2020-02-01 [1] RSPM (R 4.0.2)

smmurphy avatar Dec 29 '20 03:12 smmurphy

Additional note on this. While longer text columns are returned with odbc 1.2.2 they are truncated to 255 characters.

smmurphy avatar Jan 02 '21 18:01 smmurphy

@smmurphy:

  • On the first issue (to_bytes) - can you verify which version of GCC you are using? gcc --version perhaps.
  • On the second (truncated columns) can you verify which ODBC driver for PostgreSQL you are using. For example, after you establish a connection:
> con@info$drivername
[1] "psqlodbcw.so"
> con@info$driver.version
[1] "12.02.0000"
  • Also if you could create a small example we could use to reproduce the issue you are seeing, that would be very helpful.

detule avatar Jan 02 '21 18:01 detule

Thanks for the quick response @detule!

  • gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0
  • RStudio PostgreSQL ODBC Driver
  • 1.4.3.1000
  • I'll see what I can do about a reprex, or two.

smmurphy avatar Jan 03 '21 01:01 smmurphy

Thanks - I don't have access to those drivers but looks like there are some config options (MaxVarchar, perhaps) that might help with the truncation issue.

detule avatar Jan 03 '21 03:01 detule

We have tried MaxVarchar both in the connection directly and through the ini file without any luck thus far. I'll loop RStudio support in on this, since their professional drivers are in the mix here, and see if there is anything they might suggest.

smmurphy avatar Jan 04 '21 05:01 smmurphy

Hi, What version of R you have with RStudio?

I had this issue with most recent version R 4.0.5 (with latest R studio) and connecting to SQL Server via odbc. It was failing for a row where query (in dbGetQuery()) returns a column value Unicode (NVARCHAR(MAX) with special character (<U+FFFD>) stored in a SQL server table. I had to revert back to older version of R (3.6.3) and reinstall odbc package (latest version 1.3.2) for it to work. I still have to test it with R 4.0.4 though.

I hope this helps.

The error I had: Error in result_fetch(res@ptr, n) : wstring_convert::to_bytes

gitnirajky avatar May 25 '21 10:05 gitnirajky

I am also getting this and I've isolated it to a column that contains non-ANSI characters. I am collecting the data from a Microsoft SQL Server Database.

sessionInfo() R version 4.1.1 (2021-08-10) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows 10 x64 (build 19042)

Matrix products: default

locale: [1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United States.1252 LC_MONETARY=English_United States.1252 [4] LC_NUMERIC=C LC_TIME=English_United States.1252

attached base packages: [1] stats graphics grDevices utils datasets methods base

other attached packages: [1] dbplyr_2.1.1 forcats_0.5.1 stringr_1.4.0 dplyr_1.0.7 purrr_0.3.4 readr_2.0.2 tidyr_1.1.4 tibble_3.1.5
[9] ggplot2_3.3.5 tidyverse_1.3.1

loaded via a namespace (and not attached): [1] tidyselect_1.1.1 xfun_0.26 haven_2.4.3 colorspace_2.0-2 vctrs_0.3.8 generics_0.1.0 htmltools_0.5.2
[8] yaml_2.2.1 utf8_1.2.2 blob_1.2.2 rlang_0.4.11 pillar_1.6.3 glue_1.4.2 withr_2.4.2
[15] DBI_1.1.1 bit64_4.0.5 sessioninfo_1.1.1 modelr_0.1.8 readxl_1.3.1 lifecycle_1.0.1 munsell_0.5.0
[22] gtable_0.3.0 cellranger_1.1.0 rvest_1.0.1 evaluate_0.14 knitr_1.36 fastmap_1.1.0 tzdb_0.1.2
[29] fansi_0.5.0 broom_0.7.9 Rcpp_1.0.7 scales_1.1.1 backports_1.2.1 jsonlite_1.7.2 fs_1.5.0
[36] bit_4.0.4 digest_0.6.28 hms_1.1.1 stringi_1.7.5 grid_4.1.1 cli_3.0.1 tools_4.1.1
[43] odbc_1.3.2 magrittr_2.0.1 crayon_1.4.1 pkgconfig_2.0.3 ellipsis_0.3.2 ctxsdb_0.1.0 xml2_1.3.2
[50] reprex_2.0.1 lubridate_1.8.0 assertthat_0.2.1 rmarkdown_2.11 httr_1.4.2 rstudioapi_0.13 R6_2.5.1
[57] compiler_4.1.1

nickv9 avatar Oct 11 '21 19:10 nickv9

I am faced with a similar error issue that only occurs on shinyapps.io, but not locally.

Error in value[[3L]](cond) : wstring_convert::to_bytes

I am using a Postgres (version 11.11) database that has a text column with entry values over 255 characters. We don’t have a column with non-ANSI characters or anything else out of the ordinary. The issue occurs after succesfully connecting to the database and when reading in the SQL views.

Strangely, it's not fully reproducible. At times the shiny app on shinyapps.io works perfectly normal, at others it fails to start up (failing to read in the views) and yield this error. The data being pulled is always the same. Locally the same odbc R code/shiny app never fails.

I believe it might be a combination of the odbc R package version and the Postgres driver version.

  • Locally I'm using driver PSQLODBC35W.DLL version 09.06.0500
  • shinyapps.io is using RStudio PostgreSQL ODBC Driver version 1.4.32.1000

(as returned by con@info$drivername and con@info$driver.version) .

I did more some testing to resolve my issue:

  • R version 4.0.3 and odbc 1.3.0: works locally, sometimes fails on shinyapps.io
  • R version 4.0.3 and odbc 1.3.2: works locally, sometimes fails on shinyapps.io (upgrading R package odbc to latest version)
  • R version 4.0.3 and odbc 1.2.2: works locally and on shinyapps.io (downgrading R package odbc as suggested in this github issue)

Hence I now locked in the odbc package to version at 1.2.2. With this setup the text fields are being returned in full length locally, but truncated at 255 characters on shinyapps.io.

RoelVerbelen avatar Nov 18 '21 08:11 RoelVerbelen

We received the traceback below from a ticket submitted to Posit Support for what we thought might have been a Professional Drivers issue. Does the following help? Or is there any other output that may help from the user:

Traceback output:

11: stop(structure(list(message = "wstring_convert::to_bytes", call = NULL, cppstack = NULL), class = c("std::range_error", "C++Error", "error", "condition"))) 10: result_fetch(res@ptr, n) 9: dbFetch(rs, n = n, ...) 8: dbFetch(rs, n = n, ...) 7: .local(conn, statement, ...) 6: dbGetQuery(conn, paste0("SELECT * FROM ", sql_name)) 5: dbGetQuery(conn, paste0("SELECT * FROM ", sql_name)) 4: .local(conn, name, ...) 3: DBI::dbReadTable(con, name) 2: DBI::dbReadTable(con, name) at #20 1: retry_postgres(name = "hmt")

heath-r avatar Oct 10 '23 20:10 heath-r

I’ve found the following resources which seem related to the issue here (perhaps).

I noticed in the nanodbc.cpp file that it contains 4 different calls to std::wstring_convert. There is a comment in the file that this function is provided as a workaround to the following documented bug: https://social.msdn.microsoft.com/Forums/en-US/8f40dcd8-c67f-4eba-9134-a19b9178e481/vs-2015-rc-linker-stdcodecvt-error?forum=vcgeneral

This is the same function call which is failing. Is there a connection here?

bradybray avatar Oct 30 '23 17:10 bradybray