ROracle dbGetQuery bind parameters by name

Question

I am trying to process a large number of queries with parameters in R using ROracle. I know which parameters appear in each query, but I don't know in which order they appear. I am therefore looking for a way to submit the parameters by name in each query. Sample code:

library(ROracle)

# establish connection to DB
drv <- dbDriver("Oracle")
con <- dbConnect(drv, "User", "password", dbname = "DB")

# create table
createTab <- "create table RORACLE_TEST(num1 number, num2 number)"
dbGetQuery(con, createTab)

# insert String
insStr <- "insert into RORACLE_TEST values(:row1, :row2)"
dbGetQuery(con, insStr, data.frame(row2 = 0, row1 = 1))

# check output
dbGetQuery(con, "SELECT * FROM RORACLE_TEST")

# Output is:
#  NUM1 NUM2
#1    0    1

# Desired output should be:
#  NUM1 NUM2
#1    1    0

Any workaround for this will be appreciated except solutions of the kind

dbGetQuery(con,gsub(":row2", "0", gsub(":row1", "1", insStr)))

since this will not sanitize against sql injection (parameters will come from user input).

No, got stuck looking for a solution in the ROracle package. Thanks! This does the trick! Still strange that this is not possible with ROracle. — guzbrush, Apr 20 '18 at 09:58
With `sqlInterpolate` you will lose efficiency what bind variable usage provides. See http://www.dba-oracle.com/plsql/t_plsql_efficient.htm for reference. — Gedrox, Apr 20 '18 at 12:19

Gedrox · Accepted Answer · 2018-04-20T12:47:22.573

I have spent some time on the same question recently myself and found no perfect solution for this. In my opinion it is very wrong from syntax point of view to use named placeholders as it gives impression that argument order is insignificant.

I believe this is not the issue nor responsibility of ROracle library as this also would lead to a result which for person who doesn't know PL/SQL good enough is not expected:

DEFINE
  row1 number;
  row2 number;
BEGIN
  row1 := 1;
  row2 := 0;
  EXECUTE IMMEDIATE 'insert into RORACLE_TEST values(:row1, :row2)' USING row2, row1;
END;
/

As we migrated our applications from RODBC with RODBCext to ROracle, we kept using ? as a bind variable placeholder and replaced them with colon style in our database connectivity API. At least this shouldn't raise anyone's eyebrows:

# placeholders '?' are replaced with :1 and :2 in custom_dbGetQuery()
insStr <- "insert into RORACLE_TEST values(?, ?)"
custom_dbGetQuery(con, insStr, data.frame(row2 = 0, row1 = 1))

Edit: added suggestion to reorder data frame

You could go further by reordering the data frame yourself by checking placeholder occurrences in the query string:

custom_dbGetQuery <- function(con, insStr, data) {
    names <- names(data)

    name.pos <- sort(sapply(names, function(ph) { 
        regexp <- paste0(":", ph, "[^\\w]")
        matches <- gregexpr(regexp, insStr, perl = TRUE)
        matches <- unlist(matches)
        stopifnot(length(matches) == 1, all(matches != -1))
        matches 
    }))

    data <- data[, names(name.pos)]

    print("running query:")
    print(insStr)
    print("using data:")
    print(data)

    dbGetQuery(con, insStr, data)
}

insStr <- "insert into RORACLE_TEST values(:row1, :row2)"
custom_dbGetQuery(con, insStr, data.frame(row2 = 0, row1 = 1))

# Output:
# [1] "running query:"
# [1] "insert into RORACLE_TEST values(:row1, :row2)"
# [1] "using data:"
#   row1 row2
# 1    1    0

Agree and will go along with our approach, especially given your comment above. Disagree, however, with your assessment that the missing possibility to 'bind-by-name' is not an issue of ROracle: I am migrating from .net, whose Oracle API allows for bind-by-name, and having this functionality natively in ROracle would simplify things greatly. — guzbrush, Apr 23 '18 at 05:35

score 0 · Answer 2 · answered Oct 22 '18 at 01:09

0

A tidyverse solution inspired by sqlInterpolate (thanks @Scarabee!):

readr::read_file('query.sql') %>%
stringr::str_replace_all(., ':','?') %>%
DBI::sqlInterpolate(con, ., row2 = 0, row1 = 1) %>%
DBI::dbGetQuery(con, .)

answered Oct 22 '18 at 01:09

dmvianna

15,088
18
77
106

ROracle dbGetQuery bind parameters by name

2 Answers2