3

I successfully connected to MYSQL DB and tried to write my result back to the database: dbWriteTable(con,"predicted min",forecast$min) where forecast$min is just a vector of doubles.

I got this error message:

Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘dbWriteTable’ for signature ‘"MySQLConnection", "character", "numeric"’

I guess I need a dataframe object for my third parameter, so then I tried this: dbWriteTable(con,"predicted min",data.frame(min=forecast0.1$min))

But got this 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 MariaDB server version for the right syntax to use near '( row_names text, min double )' at line 2

I'm using SQLyog. I don't know if that's related to this problem. Any suggestions? Thanks in advance.

Sophie

Sophie
  • 97
  • 1
  • 2
  • 7

1 Answers1

3

SQLyog is not related to this issue. If your table only has one column (min), then you need not write the row names of the dataframe to the table. Try:

dbWriteTable(con,"predicted min",data.frame(min=forecast0.1$min), row.names = FALSE, append = TRUE)

Or else you're inserting a tuple with with two values into a table with one column. If the table already exists and you don't want to overwrite the table, you should also have append = TRUE.

edit: The table name should not have a space in it.

Josh W.
  • 1,123
  • 1
  • 10
  • 17
  • Hi @Josh , it still gives me the second error message, error in SQL syntax. – Sophie May 14 '15 at 05:49
  • You can always just put together your own INSERT statement. I.e., `query = sprintf("INSERT INTO %s (min) VALUES (%d)", tablename, forecast0.1$min)` . Then `sqlQuery(channel, query)`. And do that for each row. This assumes the table exists already. Also are you sure your table name has a space in it? Not sure that would work. – Josh W. May 14 '15 at 16:08
  • Thanks for reminding me. The table name (there should be no space) gives me the error. Thank you, @Josh – Sophie May 15 '15 at 06:25
  • OK, glad to help @Sophie . I edited the original answer to reflect that it was the table name. Please accept the answer if it works. – Josh W. May 15 '15 at 21:22