Параметризованный SQL-запрос в R с предложением IN

Я пытаюсь получить данные через пакет RODBC из БД Vertica. В настоящее время у меня есть SQL-запрос, подобный приведенному ниже.

library(rodbc) channel = odbcconnect("VerticaDB") query = paste
(
       SELECT *
       FROM   item_history
       WHERE  item_exp_date BETWEEN ",x," AND    ",y,"
       AND    item_code IN ('A1',
                            'A2',
                            'B1',
                            'B2')",sep="")result = (sqlQuery(channel,query)
)

Мне удалось параметризовать данные, переданные в предложении «МЕЖДУ». Есть ли способ параметризовать данные, передаваемые в предложении «IN»?

Кроме того, количество элементов данных, передаваемых в предложении IN, очень велико (более 100 различных элементов).

Есть ли способ передать его из внешнего вектора или файла?


r sql rodbc parameterized-query
person Srivathsan V    schedule 24.08.2016    source источник
comment
Для OP и будущих читателей: имейте в виду, что ни попытка OP, ни принятый ответ не являются истинным параметризованным SQL-запросом. @Benjamin - лучшая попытка, тогда как другие просто объединяют динамическую строку SQL.   -  person Parfait    schedule 25.08.2016

Ответы (2)


arrow_upward
1
arrow_downward

Чтобы сделать это с помощью манипуляций со строками, как в вопросе:

x <- "2000-01-01"
y <- "2001-01-01"
Item_Code <- c('A1','A2','B1','B2')

query <- sprintf("select * from Item_History
                  where Item_Exp_Date between '%s' and '%s'
                        and Item_Code in (%s)", x, y, toString(shQuote(Item_Code, 'sh')))

В качестве альтернативы мы могли бы использовать fn$ из пакета gsubfn для интерполяции строк:

library(gsubfn)
query2 <- fn$identity("select * from Item_History
              where Item_Exp_Date between '$x' and '$y'
              and Item_Code in ( `toString(shQuote(Item_Code, 'sh'))` )")
person G. Grothendieck    schedule 24.08.2016

arrow_upward
2
arrow_downward

У вас есть SQL-инъекция, а не параметризованный запрос. Вы можете взглянуть на пакет RODBCext и его винье.

Чтобы правильно параметризовать запрос, вы можете сделать

library(RODBC)
library(RODBCext)
channel = odbcConnect("VerticaDB")
query = paste0("select * from Item_History ",
               "where Item_Exp_Date between ? and ? ",
               "and Item_Code = ?")
item <- c("A1", "A2", "B1", "B2")
x <- 3
y <- 10 # I don't actually know what your x and y are, but hopefully you get the idea
sqlExecute(
  channel = channel,
  query = query,
  data = list(x = rep(x, length(item)),
              y = rep(y, length(item)),
              item = item),
  fetch = TRUE,
  stringsAsFactors = FALSE
)

Однако это имеет большой недостаток, поскольку sqlExecute будет выполнять запрос для каждой строки в аргументе data (список будет приведен к фрейму данных). Если у вас есть сотни элементов в вашем векторе item, вы будете выполнять сотни запросов к вашему экземпляру SQL, что может быть не особенно эффективным.

Менее очевидный способ сделать это — написать хранимую процедуру для создания запроса.

Ваша хранимая процедура в SQL может выглядеть так

CREATE PROCEDURE schema.specialQuery 
  @x int;
  @y int;
  @in varchar(2000);
AS
BEGIN
  DECLARE @query = varchar(8000);
  SET @query =  'select * from Item_History ' + 
                'where Item_Exp_Date between ' + convert(@x, varchar(10)) + 
                        ' and ' + convert(@y, varchar(10)) + 
                        ' and Item_Code IN (' + @in ')'
  EXEC @query
END
GO

Возможно, вам придется повозиться с функциями convert и некоторыми кавычками, но это будет работать с

sqlExecute(
  channel = channel,
  query = "EXECUTE schema.specialQuery @x = ?, @y = ?, @in = ?",
  data = list(x = x,
              y = y,
              in = sprintf("'%s'", paste0(item, collapse = "', '"))),
  fetch = TRUE,
  stringsAsFactors = FALSE
)

К сожалению, этот подход по-прежнему подвержен проблемам с плохо отформатированными символьными строками, передаваемыми через item, но он, вероятно, быстрее, чем выполнение сотен запросов в первом показанном мной подходе.

person Benjamin    schedule 24.08.2016