RODBC与Microsoft SQL Server：截断长字符串

Question

我正在尝试使用R/RODBC从Microsoft SQL Server数据库查询变量。RODBC将字符字符串截断为8000个字符。

原始代码：按照RODBC文档，截断为255个字符 library(RODBC) con_string <- odbcConnect("DSN") query_string <- "SELECT text_var FROM table_name" dat <- sqlQuery(con_string, query_string, stringsAsFactors=FALSE)

部分解决方案：修改查询字符串，在7999个字符后截断文本。 library(RODBC) con_string <- odbcConnect("DSN") query_string <- "SELECT [text_var]=CAST(text_var AS VARCHAR(8000)) FROM table_name" dat <- sqlQuery(con_string, query_string, stringsAsFactors=FALSE)

表格/变量包含长达250,000个字符的文本字符串。我真的想在R中处理所有文本。这是可能的吗？

@BrianRipley在以下文档的第18页讨论了问题（但没有解决方案）： https://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf

@nutterb在GitHub上使用RODBCext包讨论了类似的问题： https://github.com/zozlak/RODBCext/issues/6

在SO上看到类似的讨论，但没有使用RODBC与VARCHAR>8000的解决方案。

RODBC sqlQuery()返回varchar(255)，而应返回varchar(MAX)

RODBC字符串被截断

注意：

• R 3.3.2
• Microsoft SQL Server 2012
• Linux RHEL 7.1
• Microsoft ODBC Driver for SQL Server

• R 3.3.2
• Microsoft SQL Server 2012
• Linux RHEL 7.1
• Microsoft ODBC驱动程序的SQL Server

- Chris

---

关于部分解决方案 - 您可能会想知道为什么这个截断在7999而不是8000。这是由于RODBC中的一个错误，已经在RODBCext包中得到修复 - 特别参见https://github.com/zozlak/RODBCext/issues/8。此外，在RODBCext中，zozlak已经做出了设计选择，如果从具有VARCHAR（max）类型的列中提取，则默认允许65,535个字符。因此，对于未来的读者，我建议采用类似@Benjamin下面的方法 - 但是循环增量为65,535而不是8,000。 - logworthy

可能是RODBC sqlQuery()返回varchar(255)，而应该返回varchar(MAX)的重复问题。 - Ray

Answer 1

由于这是由微软提供的ODBC驱动程序的限制，除非他们对驱动程序进行更改，否则很少有什么办法可以解决。在您链接的GitHub问题中，@zozlak解释了原因。

当我需要时，我倾向于使用存储过程来解决这个问题，但通常需要为每个特定实例编写一个存储过程。在某些情况下，我可能会想出一种更通用的存储过程方法来解决这个问题，但我发现在存储过程中构建查询的过程很繁琐和令人沮丧。

因此，我刚刚花了一些时间构建了一个函数，可以进行涉及VARCHAR（MAX）变量的有限查询。这是一种蛮力方法，对于一个17000字符的变量，它将导出三个变量并将它们粘合在一起。这很粗略，可能不是很有效率，但这是我想出的最好的解决方案。

另一个限制是它不允许你在查询中重命名变量。您将被困在数据库中变量的命名方式上。如果您只涉及几个表，那可能不是问题。在非常复杂的数据库中，这可能会有问题。但是，至少通过这种方式，您可以仅查询具有少数必要ID的VARCHAR（MAX）变量，然后在R中执行合并。

如在GitHub问题中所讨论的那样，尽可能避免使用VARCHAR(MAX)。如果确实需要未知长度，VARBINARY(MAX)更容易查询。

示例

source("https://gist.githubusercontent.com/nutterb/d2e050dada608bb6213e61d0f8471b65/raw/be8717f318b3e3087e7c26c9a8f9d0a582a5daef/query_varchar_max"

channel <- odbcDriverConnect(...)

query_varchar_max(channel = channel,
                  id = c("idvar"),
                  varchar_max = c("varchar_max_var", "varchar_max_var2"),
                  from = "FROM dbo.table_name WHERE group = ?",
                  data = list(group = "A"))

函数代码

#' @name query_varchar_max
#' @title Query a VARCHAR(MAX) Variable from SQL Server
#' 
#' @description The RODBC driver to SQL Server (SQL Server Native Client 11.0)
#'   reports the lenght of a VARCHAR(MAX) variable to be zero.  This presents 
#'   difficulties in extracting long text values from the database. Often, the
#'   ODBC will assume a length of 255 characters and truncate the text to that
#'   many characters.  The approach taken here searches the VARCHAR(MAX) variables 
#'   for the longest length, and extracts the data in segments to be pasted 
#'   together in R.  
#'   
#' @param channel A valid ODBC channel to a SQL Server database.
#' @param id A character vector of ID variables that may be used to merge the 
#'   data from this query into another dataset.
#' @param varchar_max a character vector of variable names that are to be 
#'   treated as if they are VARCHAR(MAX) variables. 
#' @param from A single character string providing the remainder of the query 
#'   to be run, beginning with the \code{FROM} statement.
#' @param stringsAsFactors \code{logical(1)}. Should character strings returned 
#'   from the database be converted to factors?
#' @param ... Additional arguments to \code{sqlExecute} when running the full 
#'   query.
#'   
#' @details \code{query_varchar_max} operates by determining how many columns of up to
#'   8000 characters each are required to export a complete VARCHAR(MAX) variable.
#'   It then creates the necessary number of intermediate variables and queries the 
#'   data using the SQL Server \code{SUBSTRING} command, extracting the VARCHAR(MAX)
#'   variable in increments of 8000 characters. After completing the query, 
#'   the intemediary variables are concatenated and removed from the data.
#'   
#'   The function makes accommodation for multi-part queries as far as [TABLE].[VARIABLE]
#'   formats are concerned. It is not intended for use in [SCHEMA].[TABLE].[VARIABLE]
#'   formats. This at least allows \code{from} to include joins for more complex 
#'   queries.  Parameterized queries are also supported through \code{sqlExecute}.
#'
#' @export

query_varchar_max <- function(channel, id, varchar_max, from, 
                              stringsAsFactors = FALSE, ...)
{
  coll <- checkmate::makeAssertCollection()

  checkmate::assert_class(x = channel,
                          classes = "RODBC",
                          add = coll)

  checkmate::assert_character(x = id,
                              add = coll)

  checkmate::assert_character(x = varchar_max,
                              add = coll)

  checkmate::assert_character(x = from,
                              len = 1,
                              add = coll)

  checkmate::assert_logical(x = stringsAsFactors,
                            len = 1,
                            add = coll)

  checkmate::reportAssertions(coll)

  varchar_max_len <-
    paste0(
      sprintf("MAX(LEN(%s)) AS len_%s", 
              varchar_max,
              sub("[.]", "_", varchar_max)),
      collapse = ", "
    )

  varchar_len <- 
    unlist(
      RODBCext::sqlExecute(
        channel = channel,
        query = sprintf("SELECT %s %s",
                        varchar_max_len,
                        from),
        fetch = TRUE
      )
    )

  varchar_max_cols <- 
    unlist(
      mapply(expand_varchar_max,
             varchar_max,
             varchar_len,
             SIMPLIFY = FALSE)
    )

  Prelim <- 
    RODBCext::sqlExecute(
      channel = channel,
      query = sprintf("SELECT %s, %s %s",
                      paste0(id, collapse = ", "), 
                      paste0(varchar_max_cols, collapse = ", "),
                      from),
      fetch = TRUE,
      stringsAsFactors = stringsAsFactors,
      ...
    )

  var_stub_to_combine <-
    unique(
      sub(
        "(part)(\\d{1,3})", 
        "\\1",
        sub(".+AS ", "", varchar_max_cols)
      )
    )

  col_to_combine <- 
    lapply(var_stub_to_combine,
           grep,
           names(Prelim))

  Prelim[sub(".+[.]", "", varchar_max)] <-
    lapply(col_to_combine,
           function(col) apply(Prelim[col], 1, paste0, collapse = ""))

  Prelim[-unlist(col_to_combine)]

}


expand_varchar_max <- function(varchar_max, varchar_len)
{
  nvar <- varchar_len %/% 8000 + 1

  var_list <- vector("character", length = nvar)

  for (i in seq_along(var_list))
  {
    var_list[i] <- 
      sprintf("SUBSTRING(%s, %s, %s) AS %s_part%s",
              varchar_max,
              1 + (i - 1) * 8000,
              8000,
              paste0(sub("[.]", "_", varchar_max)),
              i)
  }
  var_list
}

- Benjamin

Answer 2

也许最近有所改变，但是通过以下查询格式可以从SQL Server将长度超过8000个字符的varchar(MAX)字段查询到R中。

query_string <- "SELECT CAST(text_var AS text) AS text_var FROM table_name"

- András