r-dbi
dbWriteTable fails without row_names column
Hi all, Just wanted to share a session that shows how dbWriteTable requires row_names table on a Windows 7 computer. Thanks!
---------------------------- MySQL Command Line Client -----------------------
show create table value;
create table ‘value’ ( ‘id’ int(11) not null auto_increment, ‘idRun’ int(11) not null, ‘timestep’ int(11) not null, ‘row’ int(11) not null, ‘col’ int(11) not null, ‘value’ double not null, ‘row_names’ text, primary key (‘id’), key ‘idrun’ (‘idRun’), constraint ‘value_ibfk_1’ foreign key (‘idrun’) references ‘run’(‘id) on delete cascade ) engine=inno_db auto_increment=742330 default charset=utf8
---------------------- RStudio ----------------------
df idRun timeStep row col value 1 4 0 14 37 1 2 4 0 15 35 1 3 4 0 15 38 3 4 4 0 15 39 3 5 4 0 15 40 2 6 4 0 15 41 1 7 4 0 15 42 1 8 4 0 16 37 2 9 4 0 16 38 3 10 4 0 16 39 20
dbWriteTable (db, ‘value’, df, append = TRUE, row_names = FALSE) TRUE
(It is working great. At this point in the MySQL Command Line Client I run ‘alter table value drop column row_names;’)
dbWriteTable (db, ‘value’, df, append = TRUE, row_names = FALSE) Error in .local(conn, statement, ...) : could not run statement: Unknown column 'row_names' in 'field list' Error in .local(conn, statement, ...) : could not run statement: Unknown column 'row_names' in 'field list' Error in .local(conn, statement, ...) : could not run statement: Unknown column 'row_names' in 'field list' . . . (At this point I hit Stop button to stop the infinite loop)
dbWriteTable (db, ‘value’, df, append = TRUE, row_names = TRUE) Error in .local(conn, statement, ...) : could not run statement: Unknown column 'row_names' in 'field list' Error in .local(conn, statement, ...) : could not run statement: Unknown column 'row_names' in 'field list' Error in .local(conn, statement, ...) : could not run statement: Unknown column 'row_names' in 'field list' . . . (At this point I hit Stop button to stop the infinite loop)
(At this point in the MySQL Command Line Client I run ‘alter table value add column column_names text)
dbWriteTable (db, ‘value’, df, append = TRUE, row_names = FALSE)
TRUE
dbWriteTable (db, ‘value’, df, append = TRUE, row_names = TRUE) TRUE
