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.
Answer
Very old question I know, but I had (and solved for my instance) the same problem just now, getting this error
Error in odbcUpdate(channel, query, mydata, coldata[m, ], test = test, :
missing columns in 'data'
from using
sqlSave(
In the rodbc package.
I solved it by setting
,fast = F
If anyone else from the first google results lands here, hope this helps
EDIT: So I since have looked at this more. When setting the flag to "F" as I recommend below you insert the rows, row by row into the database. While it was "T" it would fail if one row was incorrect, but now that row fails and you don't know about it. So the data base may be missing data that you thought was in there. Check the count of rows in the data base vs the number of rows you expect to be inserted from your dataframe. You probably have a single value that is incompatible. What I have taken to doing now is using "paste" function in a loop to create a string like "insert into tableName values("eg1","eg2") " this way I can get error messages back about why it did not go into the database