3

When using ROracle in R, I want to bind some parameters to data, so I do this:

> dbh <- dbConnect('Oracle', 'user/pass@host.com:port/sid')
> st <- dbPrepareStatement(dbh, statement="SELECT x FROM mytab WHERE id=:1",
                           bind="character")
> st <- dbExecStatement(st, data.frame(id=c("9ae", "1f3"), stringsAsFactors=F))
> fetch(st)
    x
0 FOO

What's unexpected is that it only uses the first row of the data frame to do the SELECT (if it were using both rows, I'd get 2 output rows in this case, not 1), contrary to the ROracle documentation:

The object that dbPrepareStatement produces is then used together with a data.frame (which should agree with the bound specification) in calls to dbExecStatement to be executed for each row of the data.frame.

Am I doing this wrong, or does the above verbiage perhaps only apply to INSERT/UPDATE statements?

Ken Williams
  • 22,756
  • 10
  • 85
  • 147

1 Answers1

-1

I think you are misunderstanding.

Essentially, your SQL has one bind variable, but you are attempting to bind two values. The second value gets thrown away. Perhaps you can do something similar using an IN operator in your SQL.

Adam Hawkes
  • 7,218
  • 30
  • 57
  • What I'm saying is to do one select with the first value, then another select with the second value. It's a common use case to run one statement with multiple (thousands, perhaps) bound values. – Ken Williams Apr 11 '12 at 03:52
  • I understood your intent, but thought you expressed it incorrectly. Sorry, I failed to notice you specified the tag `id` in your data frame. Yes, your data frame has two rows and according to the docs it should execute for each row. Perhaps there is a problem because you didn't specify a `row.names` value in the data frame? Not sure you're going to get this fixed on such an old version of the ROracle package, though. – Adam Hawkes Apr 11 '12 at 14:19
  • I agree - but it's been 1.5 years since I asked this, and I'm no longer at the company with the Oracle db, so it's mostly an academic question at this point. =) – Ken Williams Apr 12 '12 at 02:13