[R] RpgSQL row names

[Gabor Grothendieck]

On Mon, Nov 7, 2011 at 5:34 PM, Ben quant <ccquant at gmail.com> wrote:
> Hello,
>
> Using the RpgSQL package, there must be a way to get the row names into the
> table automatically. In the example below, I'm trying to get rid of the
> cbind line, yet have the row names of the data frame populate a column.
>
>> bentest = matrix(1:4,2,2)
>> dimnames(bentest) = list(c('ra','rb'),c('ca','cb'))
>> bentest
>   ca cb
> ra  1  3
> rb  2  4
>> bentest = cbind(item_name=rownames(bentest),bentest)
>> dbWriteTable(con, "r.bentest", bentest)
> [1] TRUE
>> dbGetQuery(con, "SELECT * FROM r.bentest")
>  item_name ca cb
> 1        ra  1  3
> 2        rb  2  4
>
>

The RJDBC based drivers currently don't support that. You can create a
higher level function that does it.

dbGetQuery2 <- function(...) {
  out <- dbGetQuery(...)
  i <- match("row_names", names(out), nomatch = 0)
  if (i > 0) {
    rownames(out) <- out[[i]]
    out <- out[-1]
  }
  out
}

rownames(BOD) <- letters[1:nrow(BOD)]
dbWriteTable(con, "BOD", cbind(row_names = rownames(BOD), BOD))
dbGetQuery2(con, "select * from BOD")

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