The setting
I run a local instance of MariaDB 5.5 where I created a database which has two tables. I have to fill these tables with data that is stored in a few thousand .RData files. Each file stores data.frames that have between a few thousand and more than a million rows.
Using RMySQL and the function dbWriteTable seems to be the most straightforward solution. I use RevolutionR's 3.1.1 build (8.0 beta). The operating system is Win7 x64.
The table in MariaDB has two columns: ID and VALUE, its name is TAB. The data to be stored are in the object data.tab.
What I do
I proceed as follows:
data.tab <- data.frame(ID = c(1:3), VAL = c(7:9))
db.con <- dbConnect(drv = MySQL(), user = "root", password = "myPasswrd", dbname="myDBname")
dbWriteTable(conn = db.con, name = "TAB", value = data.tab, append=TRUE, row.names=FALSE, field.types=character(ID="INT",VAL="FLOAT"))
Or equivalently, via a temporary csv file that I create:
dbWriteTable(conn = db.con, name = "TAB", value = 'tmp.csv', append=TRUE, row.names=FALSE, field.types=character(ID="INT",VAL="INT"))
The problem
Calling the second line in the above snippet causes the following error to be thrown:
Error in .local(conn, statement, ...) :
sdbi114c7f07039' not found (Errcode: 22)tempRtmpE9YwdP
Calling the third line (via temporary csv file) results in a similar error, where the path is printed with backslashes stripped out. This turns out to be related to path expansion. Debugging and printing statement yields:
"LOAD DATA LOCAL INFILE 'C:\\TEMP\\Rtemp\\RtmpE9YwdP\\rsdbi114c7f07039' INTO TABLE TAB LINES TERMINATED BY '\n' (ID, VALUE);"
Thus it seems that the escapes of backslashes (\\) and \r seem to be the problem. I don't unfortunately know how to deal with this.
Traceback (in call order):
1: dbWriteTable(conn = db.con, name = "t_ftr", value = fut.tbl, append = TRUE, row.names = FALSE,
2: .valueClassTest({standardGeneric("dbWriteTable")}, "logical", "dbWriteTable")
3: is(object, Cl)
4: is(object, Cl)
5: .local(conn, name, value, ...)
6: dbGetQuery(conn, sql)
7: dbGetQuery(conn, sql)
8: dbSendQuery(conn, statement, ...)
9: .valueClassTest(standardGeneric("dbSendQuery"), "DBIResult", "dbSendQuery")
10: is(object, Cl)
11: is(object, Cl)
12: .local(conn, statement, ...)
13: .Call(RS_MySQL_exec, conn@Id, as.character(statement))
EDIT
If in the interactive debugger at step 11 in statement I replace \\ with / in the path, the data is written to the database correctly.
