RMySQL dbWriteTable with field.types

Question

I have a data frame, called df, that looks like this:

dte, val
2012-01-01, 23.2323
2012-01-02, 34.343

The type on the columns is date and numeric. I would like to write this to a MySQL database using an already open connection. The connection works fine as I am able to query the db fine. I try to run the following:

dbWriteTable(con, name="table_name", value=df, field.types=list("date", "double(20,10)"))

This generates the error:

Error in function (classes, fdef, mtable) : unable to find an inherited method for function "make.db.names", for signature "MySQLConnection", "NULL"

If I do not specify a field.types, and run:

dbWriteTable(con, name="table_name", value=df)

I get the error:

Error in mysqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not run statement: BLOB/TEXT column 'dte' used in key specification without a key length)

Can anyone shed some light on this?

Thanks

Answer 1

After playing with things, I realized what the problem is: field.types must be a NAMED list vector, not simply a list; additionally the row names must not be included otherwise we need three field types. For the above example, the following works fine:

dbWriteTable(con, name="table_name", value=df,
  field.types = c(dte="date", val="double(20,10)"), row.names=FALSE)