CODEWITHSUNDEEP
mysqlrutf-8r-dbi
Mislav
Mislav

Reputation: 1563

DBI get query problems when string contain UTF-8 characters in R

I am sending this query to a MySQL database using following function:

loadDataBudget <- function(korisnik, razinaLabel) {
  lapply( dbListConnections( dbDriver( drv = "MySQL")), dbDisconnect)
  # Connect to the database
  db <- dbConnect(MySQL(), dbname = databaseBudget, host = options()$mysql$host,
                  port = options()$mysql$port, user = options()$mysql$user,
                  password = options()$mysql$password, encoding = "utf8")
  # Construct the fetching query
  query <- sprintf("SELECT positions, x201501, x201502, x201503, x201504, x201505, x201506, x201507, x201508, x201509,
                   x201510, x201511, x201512 FROM %s WHERE naziv = '%s' AND razina = '%d'",
                   tableBudget,
                   korisnik,
                   razinaLabel)
  # Submit the fetch query and disconnect
  data <- dbGetQuery(db, query)
  dbDisconnect(db)
  data
}

Function works well if the character argument korisnik contain non UTF-8 characters. But if it contains Croatian UTF-8 characters it returns empty table. I have tried with two collations in MySQL database: utf8_general_ci and utf8_croatian_ci .

I have also tried to set names utf-8 before query but it didn't help.

Upvotes: 0

Views: 1506

Answers (1)

Rick James
Rick James

Reputation: 142278

The text is truncated where there should be an accented Croatian letter?

  • The bytes to be stored are not encoded as utf8mb4. Fix this.
  • Also, check that the connection during reading is UTF-8.

If this does not suffice, see the "truncate" and debuging hints in

Trouble with UTF-8 characters; what I see is not what I stored

R may need:

Tool -> Global Options -> Code -> Saving and put UTF-8
rs <- dbSendQuery(con, 'set character set "utf8"')
rs <- dbSendQuery(con, 'SET NAMES utf8')

Upvotes: 1

Related Questions