R Programming - Japanese Characters Insert into MySQL

Question

I want to insert a tab-delimted file, which is conatining both japanese and english characters with special charcters. I am using RMySQL to do is. One of a solution i tried giving below error:

dbWriteTable(con, "japan_test2", d, append = T, row.names=FALSE);
Error in mysqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not run statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '˜¨å¤œã®ã‚³ãƒ³_ text)' at line 3)
In addition: Warning message:
In strsplit(msg, "\n") : input string 1 is invalid in this locale
[1] FALSE
Warning message:
In mysqlWriteTable(conn, name, value, ...) :
could not create table: aborting mysqlWriteTable

Current Locale: LC_COLLATE=English_United States.1252;LC_CTYPE=Japanese_Japan.932;LC_MONETARY=English_United States.1252;LC_NUMERIC=C;LC_TIME=English_United States.1252

Locale Tried: US, Japanese. Encoding Tried: UTF-8,16,ASCII. System: Windows7 RStudio Version 0.98.977 MySQL 5.4.27 CE

@Roman Luštrik I dint use any encoding in con. Here is my con: con <- dbConnect(MySQL(), user="uid", password="pwd", dbname="test", host="localhost"); — Vijayan
OK, you're writing d to the table then. Perhaps it would help to set Encoding of that object before passing it to a data base? — Roman Luštrik
@Roman Luštrik Can you please help me to set the encoding for the data frame d? — Vijayan
This should now work in the dev version of RMySQL: github.com/rstats-db/RMySQL. Please try it out and let me know if you have problems. — hadley

Kratos Kratos · Accepted Answer · 2019-04-11T09:45:01

Probably you aren't setting properly the encoding of the connection. You can try this:

con <- dbConnect(MySQL(), user=user, password=password,dbname=dbname, host=host, port=port)
# With the next line I try to get the right encoding (it works for Spanish keyboards)
encoding <- if(grepl(pattern = 'utf8|utf-8',x = Sys.getlocale(),ignore.case = T)) 'utf8' else 'latin1'
dbGetQuery(con,paste("SET names",encoding))
dbGetQuery(con,paste0("SET SESSION character_set_server=",encoding))
dbGetQuery(con,paste0("SET SESSION character_set_database=",encoding))
dbWriteTable( con, value = dfr, name = table, append = TRUE, row.names = FALSE )
dbDisconnect(con)

Remember that you have to use your local encoding as the right encoding of the connection. I try to get my encoding in the third line of the proposed code and then set the encoding according to my local encoding. Good luck!

R Programming - Japanese Characters Insert into MySQL

1 Answers