RODBC sqlSave() and mapping column names

Question

I've a question about using sqlSave. How does R map RODBC data in the data frame to the database table columns?

If I've a table with columns X and Y and a data frame with columns X and Y, RODBC puts X into X and Y into Y (I found out by trail-and-error). But can I explicitly tell R how to map data.frame columns to database table columns, like put A in X and B in Y.

I'm rather new to R and think the RODBC manual is a bit cryptic. Nor can I find an example on the internet.

Solution 1

I'm now doing it this way (maybe that's also what you meant):

colnames(dat) <- c("A", "B")
sqlSave(channel, dat, tablename = "tblTest", rownames=FALSE, append=TRUE)

It works for me. Thanks for your help.

Solution 2

You should find the fine R manuals of great help as you start to explore R, and its help facilities are very good too.

If you start with

  help(sqlSave)

you will see the colNames argument. Supplying a vector c("A", "B") would put your first data.frame column into a table column A etc.

Solution 3

I'm having massive problems using sqlSave with an IBM DB2 databank. I'm trying to avoid it by using sqlQuery instead to create the table with the correct formatting and then use sqlSave with append=T to force my R table into the database table. This resolve a lot of problems such as date formats and floating point numbers (instead of doubles).

Comments

[waanders]

What do you mean with "its help facilities"? I use cran.r-project.org/web/packages/RODBC/RODBC.pdf. But that document speaks about the "colnames" argument as "logical: save column names as the first row of table?", not as a vector suppling columnNAMES

[Dirk Eddelbuettel]

That alters the object before saving it which, while getting the job done, is not what you asked for.

[waanders]

Still the "colnames" argument is a logical

[waanders]

Sure, but the altered object is only temporarily, so it's no problem

[r2evans]

It should be noted that as of RODBC-1.3.13, the only reference to colnames in the source of sqlSave is in a single comparison: ` if (is.logical(colnames) && colnames)` (and the subsequent block within that if statement), suggesting that a character vector won't work (anymore?). (Perhaps the behavior has changed over the years, *shrug*.)