R DBI ODBC错误:nanodbc/nanodbc.cpp:3110:07009:[Microsoft] [用于SQL Server的ODBC驱动程序13]无效的描述符索引 [英] R DBI ODBC error: nanodbc/nanodbc.cpp:3110: 07009: [Microsoft][ODBC Driver 13 for SQL Server]Invalid Descriptor Index

问题描述

我继续阅读DBI/ODBC比RODBC快，所以我尝试如下:

require(DBI);require(odbc)
con <- DBI::dbConnect(odbc::odbc(), dsn = 'SQLSERVER1', database = 'AcumaticaDB')

我可以成功连接到DSN，但是需要执行以下查询:

rs <- dbGetQuery(con, "SELECT * FROM inventoryitem")
dbFetch(rs)

给我以下错误:

result_fetch(res @ ptr，n，...)中的错误: nanodbc/nanodbc.cpp:3110:07009:[Microsoft] [用于SQL Server的ODBC驱动程序13]无效的描述符索引

我在做什么错? 请，没有RODBC解决方案. 谢谢！

解决方案

几个月来我也一直在努力解决这个问题.但是，我遇到了一种可能对您也有帮助的解决方案.

简而言之，当某些文本列没有出现在整数/数字列之后时，就会发生此问题.如果查询中的列未正确对齐，则会抛出错误invalid index，并且您的连接可能会冻结. 然后的问题是，我怎么知道在查询末尾要输入什么内容?

要确定这一点，通常可以使用class()或typeof()检查列.要从数据库检查此类信息，可以使用查询，例如:

dbColumnInfo(dbSendQuery(con, "SELECT * from schema.table")) # You may not require the schema part...

这将为感兴趣的数据集中的每一列返回一个带有类型字段的表.然后，您可以将此表用作对select()语句进行排序的索引.我的特别困难是表中的type字段是所有数字！但是，我注意到，每条带有负数的列都放置在select语句的末尾，可以解决查询问题，并且可以很好地拉动整个表.例如，我的完整解决方案:

# Create my index of column types (ref to the current order)
index <- dbColumnInfo(dbSendQuery(con, "SELECT * from schema.table"))
index$type <- as.integer(index$type) # B/c they are + and - numbers!

# Create the ref to the table
mySQLTbl <- tbl(con, in_schema("schema", "tablename"))

# Use the select statement to put all the + numbered columns first!
mySQLTbl %>%
  select(c(which(index$type>=0),
                 which(index$type<0)))

关于发生这种情况的原因，我不确定，并且我没有数据访问权限可以在用例中进行更深入的研究

I continue to read the DBI/ODBC is faster than RODBC, so I tried as follows:

require(DBI);require(odbc)
con <- DBI::dbConnect(odbc::odbc(), dsn = 'SQLSERVER1', database = 'AcumaticaDB')

I can make a successful connection to the DSN, but the following query:

rs <- dbGetQuery(con, "SELECT * FROM inventoryitem")
dbFetch(rs)

gives me the following error:

Error in result_fetch(res@ptr, n, ...) : nanodbc/nanodbc.cpp:3110: 07009: [Microsoft][ODBC Driver 13 for SQL Server]Invalid Descriptor Index

What am I doing wrong ? Please, no RODBC solutions. Thanks!

解决方案

I have also been struggling with this issue for several months. However, I have come across a solution that may help you as well.

In a nutshell, the issue occurs when certain text columns do not appear after integer/numeric columns. When the columns are not aligned properly in the query, an error of invalid index is thrown and your connection may freeze. The issue then is, how do I know what to put at the end of my query?

To determine this, one could typically examine a column using class() or typeof(). To examine such information from the database, you can use a query such as:

dbColumnInfo(dbSendQuery(con, "SELECT * from schema.table")) # You may not require the schema part...

This will return a table with a type field for every column in the data-set of interest. You can then use this table as an index to sort the select() statement. My particular difficulty is that the type field in the table was all numbers! However, I noticed that every column with a negative number, when placed at the end of the select statement, fixed my query and I could pull the whole table just fine. For example, my full solution:

# Create my index of column types (ref to the current order)
index <- dbColumnInfo(dbSendQuery(con, "SELECT * from schema.table"))
index$type <- as.integer(index$type) # B/c they are + and - numbers!

# Create the ref to the table
mySQLTbl <- tbl(con, in_schema("schema", "tablename"))

# Use the select statement to put all the + numbered columns first!
mySQLTbl %>%
  select(c(which(index$type>=0),
                 which(index$type<0)))

As for reason for why this occurs, I am not sure and I do not have the data access privileges to dig much deeper in my use-case

这篇关于R DBI ODBC错误:nanodbc/nanodbc.cpp:3110:07009:[Microsoft] [用于SQL Server的ODBC驱动程序13]无效的描述符索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！