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

[Gabor Grothendieck]

On Thu, Oct 27, 2011 at 7:11 AM, Gabor Grothendieck
<ggrothendieck using gmail.com> wrote:
> 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)
>

Here is a second alternative.  Instead of inserting the new records
one by one this one writes the new records all at once to a temporary
table, tmp, and then inserts them en masse into the existing bod table
and finally drops the temporary table at the end.

library(RpgSQL)

con <- dbConnect(pgSQL())

## write BOD into bod table
if (dbExistsTable(con, "bod")) dbSendUpdate(con, "drop table bod")
dbWriteTable(con, "bod", BOD)

## insert NEW into tmp table, insert that into bod and drop tmp
NEW <- 2*BOD
if (dbExistsTable(con, "tmp")) dbSendUpdate(con, "drop table tmp")
dbWriteTable(con, "tmp", NEW)
dbSendUpdate(con, "insert into bod select * from tmp")
dbSendUpdate(con, "drop table tmp")

## show result
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