append=TRUE, overwrite=FALSE ignored in dbWriteTable

On Thu, Oct 27, 2011 at 6:05 AM, Tomoaki NISHIYAMA
<tomoakin at kenroku.kanazawa-u.ac.jp> wrote:

Hi,

Ok, back to the mailing list.

If I understand correctly, you are using RpgSQL (note this is one of the most important information)
and it appears that RpgSQL does not match to the specification of DBI.
http://cran.r-project.org/web/packages/DBI/index.html

The definition in RpgSQL is something like
setMethod("dbWriteTable", "pgSQLConnection", def=function(conn, name, value, overwrite=TRUE, ...) {
and does NOT have append argument.
In addition, the default value of overwrite differs to the DBI spec.
In DBI the spec is
?dbWriteTable(conn, name, value, row.names = T, ...,
? ? ? ? ? ? ?overwrite = F, append = F)

This discrepancy is perhaps inherited from RJDBC.
There was a similar report early this year.
https://stat.ethz.ch/pipermail/r-sig-db/2011q1/000996.html

So, I Cc:ed this mail to the maintainers of RpgSQL and RJDBC.

You can modify the respective source code
RpgSQL/R/class.R
as you like, or use RPostgreSQL.
Using RPostgreSQL on windows is not very easy, though.

Both RpgSQL and RJDBC do handle overwrite. ?Neither support append but
insert can be used instead.

library(RpgSQL)
library(gsubfn) # fn - quasi-perl style string interpolation

## assumes appropriate RpgSQL startup options have been set.
## See example in ?pgSQL
con <- dbConnect(pgSQL())

## write BOD to database. BOD comes with R
if (dbExistsTable(con, "bod")) dbSendUpdate(con, "drop table bod")
dbWriteTable(con, "bod", BOD)

## insert some new rows.
## Here we don't need to use quotes around the values but
## if the values were not numeric we would need to.
NEW <- 2*BOD
for(i in 1:nrow(NEW)) fn$dbSendUpdate(con,
? ? ? ?"insert into bod values(`NEW[i,1]`, `NEW[i,2]`)")

dbGetQuery(con, "select * from bod")

dbDisconnect(con)