0

Code:

dbWriteTable(con, name=symbol, value=df, row.names=FALSE, append=TRUE)

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 MariaDB server version for the right syntax to use near 'SSL ( Date text, Open double, High double, Low double, Close double, Volum' at line 1)

The issue is the ticker symbol "SSL" is also a MySQL keyword. It seems RMySQL is not escaping the table name?

Just to add, same problem with tickers "ALL" and "KEY", also reserved words. So I just need to figure out how to properly escape the table name with RMySQL, but so far am coming up empty.

Edit to add: I realize it is possible to use dbSendQuery but really want to make use of dbWriteTable instead. I am really surprised that the table name is not properly backticked automatically?

Edit to add more: I also tried name=paste0(dbname,".",symbol) for example, which works with MySQL to get past the reserved words, but what I found is that the dbWriteTable function uses the dbExistsTable() function built-in, and that function does not work with the change.

I could rewrite the dbExistsTable function to overwrite built-in as a workaround, but I do not know how to get the dbname from the dbConnect() function to become inherited. Still looking into this...

ctrlbrk
  • 1,174
  • 2
  • 17
  • 27
  • Have you tried enclosing "SSL" inside backticks? `paste0("\`", 'SSL', "\`")` – IRTFM Nov 07 '13 at 22:20
  • Yes I tried it, and it will not work with the function dbWriteTable due to the way the rest of the SQL query is structured within that function (not escaping the other variables results in a SQL error). – ctrlbrk Nov 08 '13 at 04:22
  • I emailed Jeffrey Horner (maintainer) to see if there is a work-around, or if a bug fix is needed. In the meantime I will have to re-write the function myself. – ctrlbrk Nov 10 '13 at 13:31

0 Answers0