Problems with RODBC sqlSave

Question

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.

Bezoar

Answer 1

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

Selfpity

Any Idea why this should make a difference? – Zero

The fast = F, just means that it inserts the data row by row. I have no idea why doing it in batch (fast=T) causes an issue., I dont use sqlSave if I have a to do this any more. I general just use sqlQuery and for each row use paste to make a string. – Selfpity

This helped me put dataframe in Django table – Mapp

Answer 2

different errors can lead to the same error msg. So it is not clear whether this helps you: however if "tablename='wellAnalysedDataTablealready' is already created in the mySql database as an empty table you will get this error. Try to remove it and let the sqlsave(...) function take care of it

Encephalic

Answer 3

I got the same error and the problem was the destination table data types. Even it seems there were possible data conversion.
The solution steps were:

1. use an unused table name (new table);
2. set append=False and get the "create table" showed at error msg;
3. run that create table at database;
4. the "saveSql" now works either with append=False or append=True.

It is useful setting "verbose=T" to check exactly where the problem occurs.

Noami

Answer 4

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

Addendum

Answer 5

> str(data)
'data.frame':   3 obs. of  4 variables:
 $ analysedDataID  : num  1 2 3
 $ plateWell       : Factor w/ 3 levels "a","b","c": 1 2 3
 $ screenPlateOrder: num  1 2 3
 $ wellData        : Factor w/ 3 levels "A","B","C": 1 2 3

Have you tried making your numerics integers and your factors characters?

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

Opuntia

Hi gsk3. Thanks for your reply. I tried it with your suggested changes but still couldn't get it to work. I keep getting another error message that says: "unable to append to table 'wellanalyseddatatable'". I noticed that the tablename variable seems to convert the input character into all lower case, which means it doesn't match the table I want to insert the data frame into. Do you have any idea how I could make it case sensitive? I tried looking at the documentation for RODBC but it doesn't seem to mention it. – Bezoar

If you type odbcUpdate you will get the entire function definition. From there you can see that channel has an attribute called "case", which is set to either upper or lower. Not sure why there's no "preserve" option, but you could likely overwrite odbcUpdate with your own version of the function. Relevant line is cnames <- switch(attr(channel, "case"), nochange = cnames, toupper = toupper(cnames), tolower = tolower(cnames)) – Opuntia

Answer 6

If the issue is in fact with case-sensitivity, then it looks like there's an easy fix without changing any functions by using the "preserve" option, which appears in the line noted by Ari B. Friedman: "nochange=cnames". So, when you initially create your RODBC channel, you can simply specify that as the case option:

my_sql_channel <<- odbcConnect("myOdbc", case="nochange")

Scone