[R-sig-DB] append=TRUE, overwrite=FALSE ignored in dbWriteTable

[Gabor Grothendieck]

On Thu, Oct 27, 2011 at 6:05 AM, Tomoaki NISHIYAMA
<tomoakin using 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)


Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com