Shiny反应环境中Redshift查询出现DBI语句执行中错误求助

错误原因 #

报错nanodbc/nanodbc.cpp:1509: 00000: [RStudio][Amazon Redshift] (140) Error occurred while trying to run statement: a statement is already in progress，本质是复用了同一个Redshift ODBC连接，且前一次查询的语句/结果集未被正确释放，导致新查询无法在该连接上执行（ODBC连接默认一次只能处理一个活跃语句）。

解决方案：每次查询创建独立连接并自动关闭 #

不要复用全局数据库连接，而是在每次触发查询时创建新连接，查询完成后立即关闭，确保连接资源被正确释放。

1. 封装连接函数 #

先把Redshift连接逻辑封装成可复用的函数：

get_redshift_conn <- function() {
  # 替换为你的Redshift连接参数
  conn <- DBI::dbConnect(
    odbc::odbc(),
    Driver = "Amazon Redshift",
    Server = "your-redshift-endpoint.amazonaws.com",
    Database = "your-database-name",
    UID = "your-username",
    PWD = Sys.getenv("REDSHIFT_PWD"), # 建议用环境变量存密码，不要硬编码
    Port = 5439
  )
  conn
}

2. 修改反应式查询逻辑 #

在eventReactive中，每次触发查询时创建连接，用on.exit()确保无论查询成功还是失败，连接都会被关闭：

server <- function(input, output) {
  # 按地址查询的反应式数据框
  address_result <- eventReactive(input$go_cad, {
    req(input$cad) # 确保输入不为空
    
    # 创建新连接
    conn <- get_redshift_conn()
    # 注册退出时关闭连接的逻辑
    on.exit(DBI::dbDisconnect(conn), add = TRUE)
    
    # 推荐用参数化查询避免SQL注入，不要直接拼接字符串
    query <- "
      SELECT c.id, a.address 
      FROM customer_id c
      JOIN customer_address a ON c.id = a.customer_id
      WHERE a.address LIKE ?
    "
    # 执行参数化查询
    result <- DBI::dbGetQuery(
      conn, 
      query, 
      params = list(paste0("%", input$cad, "%"))
    )
    
    result
  })
  
  # 按ID查询的逻辑同理
  id_result <- eventReactive(input$go_cid, {
    req(input$cid)
    
    conn <- get_redshift_conn()
    on.exit(DBI::dbDisconnect(conn), add = TRUE)
    
    query <- "
      SELECT c.id, a.address 
      FROM customer_id c
      JOIN customer_address a ON c.id = a.customer_id
      WHERE c.id = ?
    "
    result <- DBI::dbGetQuery(conn, query, params = list(input$cid))
    
    result
  })
  
  # 输出结果的逻辑（示例）
  output$customer_table <- renderTable({
    if(input$go_cad > 0) {
      address_result()
    } else if(input$go_cid > 0) {
      id_result()
    } else {
      data.frame()
    }
  })
}

关键注意点 #

• 绝对不要直接拼接用户输入到SQL语句中，必须用参数化查询（dbGetQuery的params参数），防止SQL注入攻击。
• 用on.exit()确保连接一定会被关闭，避免连接泄漏。
• 如果你的应用有高并发需求，可以考虑用连接池（比如pool包），但对于普通场景，每次创建新连接已经足够稳定。

为什么之前的方法无效？ #

如果把连接放在反应环境中（比如reactiveVal或全局变量），本质还是复用同一个连接。Shiny的反应式环境是异步执行的，前一次查询的结果集可能还未被完全处理，此时用同一个连接发起新查询就会触发语句冲突的错误。

内容的提问来源于stack exchange，提问作者user49017