[R-sig-DB] ROracle: basic insert fails
Jean-Christophe Domenge
jc@domenge @end|ng |rom gm@||@com
Fri Jun 5 15:34:40 CEST 2009
Hi all,
I've been playing with ROracle (0.5-9) for a few days
and I can't wrap my mind around this one.
Here's a sample of my R (2.4.0) session.
my.df<-data.frame(prd_id=c(123,456),vol_factor=c(.123,.456))
> my.df
prd_id vol_factor
1 123 0.123
2 456 0.456
> library(ROracle)
Loading required package: DBI
> conn<-dbConnect("Oracle","***/***@***")
> dbGetQuery(conn,"create table mytable (prd_id number, vol_factor
number)")
> dbGetQuery(conn,"insert into mytable (prd_id,vol_factor)
values(123,.123)")
> dbGetQuery(conn,"insert into mytable (prd_id,vol_factor)
values(456,.456)")
> dbGetQuery(conn,"select * from mytable")
PRD_ID VOL_FACTOR
0 123 0.123
1 456 0.456
the above works as expected. Now let's try to insert new rows into
mytable
using a prepared statement and bind variables:
> ps<-dbPrepareStatement(conn,"insert into mytable (prd_id,vol_factor)
values (:1,:2)",bind=c(rep("numeric",2)))
> res<-dbExecStatement(ps,my.df)
> dbCommit(conn)
[1] TRUE
> dbGetQuery(conn,"select * from jdomenge_test")
PRD_ID VOL_FACTOR
0 123 0.123
1 456 0.456
2 123 NA
3 456 NA
so the 2 new rows were appended, except the values in the second
column were seemingly not read...
the same happens with dbWriteTable:
> dbWriteTable(conn,"mytable",df,append=T,row.names=F)
[1] TRUE
> dbGetQuery(conn,"select * from mytable")
PRD_ID VOL_FACTOR
0 123 0.123
1 456 0.456
2 123 NA
3 456 NA
4 123 NA
5 456 NA
It seems the only way I can insert rows in a table is to build a query
string
and pass it into dbGetQuery.
Note that SELECT queries pose no problem, only INSERT.
I'm clueless at this point, I don't know what I'm doing wrong.
I could find no answer in the help files nor on the web...
Any help would be *greatly* appreciated.
Thanks in advance,
Jean-Christophe
[[alternative HTML version deleted]]
More information about the R-sig-DB
mailing list