Problems with RODBC sqlSave

user_761078 0
Viewed 11923

I'm having some difficulty inserting a data frame into a mySql database using RODBC. Below is the code I'm using:

data <- data.frame(analysedDataID=c(1,2,3), plateWell=c("a","b","c"), screenPlateOrder=c(1,2,3), wellData=c("A","B","C"))

con <- odbcConnect(DSN, uid="user_id", pwd="some_password")
query <- sqlSave(con, data, tablename = 'wellAnalysedDataTable', rownames=FALSE, append=TRUE)

When I try to execute this I get the following error message:

Error in odbcUpdate(channel, query, mydata, coldata[m, ], test = test,  : 
missing columns in 'data'

Here is the specific table I'm trying to insert the data into:

+------------------+-------------+------+-----+---------+-------+
| Field            | Type        | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| analysedDataID   | int(10)     | YES  |     | NULL    |       | 
| plateWell        | varchar(10) | YES  |     | NULL    |       | 
| screenPlateOrder | int(10)     | YES  |     | NULL    |       | 
| wellData         | varchar(30) | YES  |     | NULL    |       | 
+------------------+-------------+------+-----+---------+-------+

I am just really confused at this point as I've read the documentation over and over again but just can't get this to work. Any help would be greatly appreciated!

Cheers!

Thanks! I added the changes you suggested and there does not seem to be a problem with the case anymore. However I keep getting the following error message: 

'index column(s) analysedDataID plateWell screenPlateOrder wellData not in data frame'

The statement I'm executing that generates this is:

sqlUpdate(con, data, tablename="wellAnalysedDataTable", index=c("analysedDataID", "plateWell", "screenPlateOrder", "wellData"))

This doesn't make any sense to me as the columns are already in the data frame with the same colnames.

rodbc r mysql
6 Answers

I was in the same situation some while back and my problem was rising from inserting data frame column numbers that did not match columns in the destination table. So this what I did. I did an SQL save with an append set to false (implying creation of a new table with a name different from the problematic table). Using the table_name and column_name of the INFORMATION_SCHEMA.COLUMNS of the working database I compared the two tables to determine the missing column.

select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='table1'
and COLUMN_NAME NOT IN
(
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='table2'
)

You can use table1 and table2 interchangeably. one would be the table the sqlsave is failing on while the other would be the one created on the fly

user_60985740
Related