R DBI ODBC Error: nanodbc / nanodbc.cpp: 3110: 07009: [Microsoft] [ODBC Driver 13 for SQL Server] Invalid descriptor index
I keep reading DBI/ODBC
faster than RODBC
that, so I tried the following:
require(DBI);require(odbc)
con <- DBI::dbConnect(odbc::odbc(), dsn = 'SQLSERVER1', database = 'AcumaticaDB')
I can make a successful DSN connection, but the following request:
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 solutions RODBC
. Thank!
source to share
rs <- dbGetQuery(con, "SELECT * FROM inventoryitem")
dbFetch(rs)
If the table inventoryitem
contains a mix of long data / variable length columns (such as VARBINARY
, VARCHAR
) and columns of simple types (such as INT
), you cannot query them in arbitrary order via ODBC.
Applications should make sure to place long columns of data at the end of the picklist.
Long data is retrieved from the database using an ODBC API call SQLGetData
, and this must be retrieved after other data in the row has been retrieved.
These are known and documented limitations of ODBC
To retrieve long data from a column, the application first calls SQLFetchScroll or SQLFetch to navigate to a row and retrieve data for related columns. The application then calls SQLGetData.
See https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/getting-long-data
source to share
There is a workaround:
Reorder your statements SELECT
so that long data types (usually strings) are last.
If you have a complex query that is generated by yourself dbply
, get the SQL query directly through show_query()
. Copy-paste and modify the first statement SELECT
to have long data types in the list. Then it should work.
source to share