0

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

Vijayan
  • 21
  • 7
  • In what encoding is `con`? – Roman Luštrik Aug 05 '14 at 08:40
  • @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 Aug 05 '14 at 08:47
  • 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 Aug 05 '14 at 09:11
  • @Roman Luštrik Can you please help me to set the encoding for the data frame d? – Vijayan Aug 05 '14 at 09:20
  • I hope someone knowledgeable of MySQL will chime in if encoding is indeed the problem. You could try passing a string to a data.base and vary its encoding, for starters. – Roman Luštrik Aug 05 '14 at 12:04
  • 1
    This should now work in the dev version of RMySQL: https://github.com/rstats-db/RMySQL. Please try it out and let me know if you have problems. – hadley Feb 24 '15 at 16:24

1 Answers1

0

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!

Kratos
  • 106
  • 3