R help - Nov 2011 - RpgSQL row names

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


Thanks,
Ben

	[[alternative HTML version deleted]]

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