how to encode R strings so that they are read into MySQL without encoding errors

Question

Consider this:

> scr<-paste("INSERT INTO ques2_log (freeze_time) value(",sQuote(now()),")")

> scr

#> "INSERT INTO ques2_log (freeze_time) value( ‘2017-06-13 23:46:16’ )"

If we feed this simple SQL script into a MySQL DB as follows:

dbExecute(db,scr1)

The MySQL DB throws the following error:Error in .local(conn, statement, ...) : 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 '��2017-06-13 23:44:13’ )' at line 1

I have tested the SQL script by typing by hand and it works.

It is also clear that the single quote is the unexpected character.

I looked up some online articles on character encoding and tried enc2utf8(scr) before feeding to the DB through RMySQL commands. No effect. Same error.

I also read this and ran

ALTER DATABASE ques2_log CHARACTER SET utf8 COLLATE utf8_general_ci;

But the error remains.

Is there a reason you're using sQuote rather than just plain old single '? Typically sQuote is used to format output to be displayed on screen, which isn't what you're doing here. — joran
– joran, Commented Jun 13, 2017 at 18:37
I am using sQuote to surround the output of the function now() by single quotes. It is difficult to surround the output of a function in single quotes otherwise, I guess. — Lazarus Thurston
– Lazarus Thurston, Commented Jun 13, 2017 at 18:50
Yes! that worked. I never imagined that sQuote() and ' would generate two different character codes for the same single output character! Please convert this as an answer @joran and I will accept it. Thanks. — Lazarus Thurston
– Lazarus Thurston, Commented Jun 13, 2017 at 19:02
Btw. sQuote(date(), q = FALSE) would also work but it isn't the best tool for the job. Also I would not recommend using paste() in this context. You are better off using DBI::dbQuoteLiteral() or glue::glue_sql() for e.g. correct NA handling. — ismirsehregal
– ismirsehregal, Commented Jan 5, 2023 at 8:58

joran · Accepted Answer · 2017-06-13 19:21:59Z

1

Just use regular single quotes, as in:

paste0("'",date(),"'")

sQuote produces distinct left and right "smart" quotes, as documented in ?sQuote:

Single or double quote text by combining with appropriate single or double left and right quotation marks.

...and additionally, the documentation makes clear that the intended purpose of this function is for formatting text for displaying user facing messages on screen:

The purpose of the functions is to provide a simple means of markup for quoting text to be used in the R output, e.g., in warnings or error messages.

So it generally shouldn't be used for processing text to be used programmatically.

answered Jun 13, 2017 at 19:21

joran

174k34 gold badges439 silver badges484 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to encode R strings so that they are read into MySQL without encoding errors

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related