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
I'm clueless at this point, could find no answer in the help files or
on the web... for a while now.
Any help would be *greatly* appreciated.
Thanks in advance,
Jean-Christophe