Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

encoding parameter in dbConnect #153

Closed
etienne-s opened this issue Feb 8, 2018 · 2 comments
Closed

encoding parameter in dbConnect #153

etienne-s opened this issue Feb 8, 2018 · 2 comments

Comments

@etienne-s
Copy link

@etienne-s etienne-s commented Feb 8, 2018

In ?odbc::dbConnect one can read the following about the encoding parameter (emphasis mine):

The text encoding used on the Database. If the database is the same as your local encoding set to "". See iconvlist() for a complete list of available encodings on your system. Note strings are always returned UTF-8 encoded.

However, my local encoding is "windows-1252" and when I connect to an SQL Server database I observe that:

  • If I use encoding = "", I get windows-1252 encoded strings decoded as UTF-8 (so I get \xe9 instead of é).
  • If I use encoding = "windows-1252", I get UTF-8 encoded strings properly decoded as UTF-8.

So maybe the docs need a change.

Session Info
> devtools::session_info()
Session info -------------------------------------------------------------------------------------------------------
 setting  value                       
 version  R version 3.4.3 (2017-11-30)
 system   x86_64, mingw32             
 ui       RStudio (1.1.423)           
 language en                          
 collate  French_France.1252          
 tz       Europe/Paris                
 date     2018-02-08                  

Packages -----------------------------------------------------------------------------------------------------------
 package   * version date       source        
 base      * 3.4.3   2017-11-30 local         
 bit         1.1-12  2014-04-09 CRAN (R 3.4.0)
 bit64       0.9-7   2017-05-08 CRAN (R 3.4.0)
 blob        1.1.0   2017-06-17 CRAN (R 3.4.0)
 compiler    3.4.3   2017-11-30 local         
 datasets  * 3.4.3   2017-11-30 local         
 DBI       * 0.7     2017-06-18 CRAN (R 3.4.0)
 devtools    1.13.4  2017-11-09 CRAN (R 3.4.2)
 digest      0.6.15  2018-01-28 CRAN (R 3.4.3)
 graphics  * 3.4.3   2017-11-30 local         
 grDevices * 3.4.3   2017-11-30 local         
 hms         0.4.1   2018-01-24 CRAN (R 3.4.3)
 memoise     1.1.0   2017-04-21 CRAN (R 3.4.1)
 methods   * 3.4.3   2017-11-30 local         
 odbc        1.1.5   2018-01-23 CRAN (R 3.4.3)
 pillar      1.1.0   2018-01-14 CRAN (R 3.4.3)
 pkgconfig   2.0.1   2017-03-21 CRAN (R 3.4.1)
 Rcpp        0.12.15 2018-01-20 CRAN (R 3.4.3)
 rlang       0.1.6   2017-12-21 CRAN (R 3.4.3)
 stats     * 3.4.3   2017-11-30 local         
 tibble      1.4.2   2018-01-22 CRAN (R 3.4.3)
 tools       3.4.3   2017-11-30 local         
 utils     * 3.4.3   2017-11-30 local         
 withr       2.1.1   2017-12-19 CRAN (R 3.4.3)
 yaml        2.1.16  2017-12-12 CRAN (R 3.4.3)
@skYeYe85
Copy link

@skYeYe85 skYeYe85 commented Jun 28, 2021

@etienne-s thanks for the comment, it really helped me on my Win7-Machine. But we switched to Win10, now it seems like, the encoding-Setting is not helping anymore. I have issues when Inserting data with R-DBI-Package (Database: ORACLE 19c, R-DBI Version 1.1.1, R-odbc Version 1.3.2) - when Importing Data to Database, the special characters in German (Umlaute 'ÄÖÜ') is imported like '¿¿¿¿¿¿¿¿'. I tried several things, like using iconv, enc2utf8 on the data-frame etc, or different encoding-settings in DBI::dbConnect(odbc::odbc(), ... encoding = 'latin1' or 'ISO8859-1' etc..., but it didn't work... Any advice?

I also should have mentioned, that I was running R 3.6.1 on Win7-Machine, while I'm running R 4.0.4 on Win10-Machine.
Note: the way I'm importing the data is the following:

  rs <- DBI::dbSendStatement(
    con,
    paste0(
      "INSERT INTO SCHEMA_NAME.TABLE_NAME ",
      paste0("(", paste0(toupper(names(
        df
      )), collapse = ","), ")"),
      " values (",
      paste0(rep("?", length(df)), collapse = ", "),
      ")"
    )
  )
  tmp <- DBI::dbBind(res = rs, params = df)
  tmp <- DBI::dbClearResult(res = rs)

An old-fashioned INSERT INTO statement how ever does work:

      sql <- paste0("INSERT INTO SCHEMA_NAME.TABLE_NAME (PERSON_NAME) VALUES ('Jürgen')")
      ret <- DBI::dbExecute(con, sql)

Now I also found out, when I switch to the 32-bit-Version, it works, so I can use the dbSendStatement/dbBind/dbClearResult, when Inserting and dbGetQuery when selecting Data from the DB? (But I'm not sure, why it does not work with the 64-bit-Version)

@skYeYe85
Copy link

@skYeYe85 skYeYe85 commented Jul 27, 2021

The above described problem occured because the charset for the installed odbc-driver has not been set. So now it works with 32-bit-odbc-driver and 64-bit-odbc-driver. Just wanted to mention this, if somebody else has similar issues -> in that case, contact your database-admin, and tell them to set the right charset in the odbc-driver!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants