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

[smmurphy]

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]

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

[detule]

@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.

[smmurphy]

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.

[detule]

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.

[smmurphy]

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.

[gitnirajky]

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

[nickv9]

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

[RoelVerbelen]

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.

[heath-posit]

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")

[bradybray]

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?

[harrismcgehee]

I get this result sometimes as well. However, I have found that if I immediately resubmit the query, it can work. Or sometimes it takes 3 resubmissions. We had a theory that the Postgres server we were querying was being updated during the query.

For us, the fact that the exact same query with the same connection arguments and drivers will both work and not work makes us think it isn't related to odbc package directly.