找到你要的答案

Q:Problems with RODBC sqlSave

Q:与rodbc sqlsave问题

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.

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)

当我尝试执行这个我得到以下错误信息:

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

这里是我试图插入数据的具体表:

+------------------+-------------+------+-----+---------+-------+
| 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    |       | 
+------------------+-------------+------+-----+---------+-------+

我只是很困惑在这一点上,因为我已经阅读了一遍又一遍的文件,但只是不能得到这工作。任何帮助将不胜感激!

干杯!


谢谢!我添加了你所建议的变化,而且似乎不再是问题了。但是,我始终得到以下错误信息:

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

我正在执行的语句生成这个:

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

这没有任何意义我列已经同colnames数据帧。

answer1: 回答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

很老的问题,我知道,但我有(和解决我的实例)同样的问题刚才,得到这个错误

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

使用

sqlSave(

在rodbc包。

我通过设置解决它

,fast = F

如果任何其他人从第一谷歌结果降落在这里,希望这有助于

answer2: 回答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

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

answer3: 回答3:
> 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)
> 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

你想让你的工作和你的因素整数字符?

 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)
answer4: 回答4:

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")

如果问题是事实与案件的敏感性,那么它看起来像有一个简单的修复没有用“保存”选项更改任何功能,它的出现在线Ari B. Friedman指出:“改变= cnames”。所以,当你开始创建你的rodbc通道,你可以简单地指定为案例的选择:

my_sql_channel <<- odbcConnect("myOdbc", case="nochange")
answer5: 回答5:

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.

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.

它是有用的设置“verbose = T”检查问题在哪里发生。

mysql  r  rodbc