0

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.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Mislav
  • 1,533
  • 16
  • 37

1 Answers1

1

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')
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Unfortunately, this doesn't help. I have already tried this before. – Mislav Oct 01 '17 at 16:04
  • What does `SELECT HEX...` give you? (See the link for more discussion of this debugging technique.) – Rick James Oct 01 '17 at 17:52
  • What you exactly mean by "check that the connection during reading is UTF-8". How should I check this. I have changed the coalition of target column to utf8mb4_croatian_ci and incorporate your code but nothing has happened. – Mislav Oct 03 '17 at 14:30
  • @Mislav - point me at the R documentation for connections and for character sets. Maybe I can interpret it for you. (I am not familiar with R.) – Rick James Oct 07 '17 at 01:49