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

Caulk

I rather doubt your implication that the dte column is of class "Date". It is printing as if it were of class "character". You should post instead the results of str() on head(name-object). – Depressant

the date column is of class date, i just wrote up the example by hand above. i explicitly do as.Date() on it to make sure of this. – Caulk

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)

Caulk

Thanks @Brooky for the tip with the field.types object class change – Aniela

Can´t you add the field for a list of columns instead of doing it one by one? – Sabin

What does "double(20,10)" mean? I assume one is the length argument. – Kotick

Answer 2

thats because of table name. do not use capital letters for mysql table name.

Graft

Where does the OP mention a table name with capital letters? – Primula