2

I can use sqlSave to create a new table and to append data to that table, but I want the tablet to have some additional columns (for instance, an "ID" autoincrementing column - I am manually adding these columns after creating the table and testing that I can save to it and append to it). As soon as I try to use sqlSave after adding those columns I get an error when trying to use sqlSave to append more data

Error in odbcUpdate... missing columns in 'data'

So I added an ID column to my data frame (so its columns would match my database table) and tried setting it to "NULL", NULL, and "". I keep getting the same error.

Any ideas?

Thanks, Aerik

P.S. I'm using RODBC with MySQL OOBC driver version 5.1

Aerik
  • 2,307
  • 1
  • 27
  • 39
  • Isn't this a SQL issue? I'm pretty sure the only way to add a new column to an existing table is with a standalone alter table query... – joran Feb 14 '13 at 19:50
  • Yes, the sequence of events is this: create table using sqlSave, append some table using sqlSave, manually add ID column using "ALTER TABLE", try to append more data using sqlSave (try several different ways) – Aerik Feb 14 '13 at 19:55
  • Ok, it wasn't clear from your question whether you were adding the columns only in the R data frame, or in the db table itself. – joran Feb 14 '13 at 19:55

2 Answers2

1

Ah, I got it. The sqlSave function seems to lowercase everything. I'm not sure what checks it's doing behind the scenes, but if I make an "id" column it works, but an "ID" column does not.

Aerik
  • 2,307
  • 1
  • 27
  • 39
0

Try the case="nochange" argument in odbcConnect. I'm using RODBC (1.3-10) with MySQL ODBC driver version 5.2 and it works for me.

André le Blond
  • 368
  • 3
  • 8