Problem to data access from sql db by using r studio-CodePudding

I can able to connect DB by using following code

library(RODBC)
library(DBI)
library(odbc)
conn3 <- DBI::dbConnect(odbc :: odbc(),
                        Driver = 'SQL Server',
                        Server = 'xxx',
                        Database = 'xxx',
                        UID = 'xxx',
                        Pwd = 'xxx',
                        TrustServerCertificate='no',
                        #trusted_connection = 'yes',
                        Port = 1433
)

And i can able to see the DB data table.

But when i try to access the data by using following code

myquery <- dbSendQuery(conn3, "SELECT  * FROM mhealthpbi1.report.blood_test")
df<-dbFetch(myquery)

It showws the following error

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

and show the error message (image attached)

can anyone help me to access or extract data?

can anyone help me to access or extract data by providing code or explain process?

CodePudding user response：

Try myquery <- dbSendQuery(conn3, "SELECT * FROM mhealthpbi1.report.blood_test;") or make sure the table name is correct, remember that you have already selected the database in dbConnect.

Example for mySQL:

mysqlconnection = dbConnect(RMySQL::MySQL(),
                            dbname='cars',
                            host='localhost',
                            port=3306,
                            user='xxx',
                            password='xxx')

brand = fetch(dbSendQuery(mysqlconnection, "select brand from sale_2018"))

CodePudding user response：

In my experience, this error can be caused by a MS ODBC-driver (ahem) "feature": all long columns (ill-bounded constraint) must be at the end of the list of columns¹. I'm inferring that you have an odbc version older than 1.3.1, since it was sufficiently mitigated in that release.

Two options:

See if/which columns are "large", often created as nvarchar(max) or blob:
```
fields <-
  DBI::dbGetQuery(conn3,
    "select TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
     from information_schema.columns
     where TABLE_NAME='blood_test'")
```
Look at fields, and if character_maximum_length is over -1 (max) or possibly a high number over 255 or so, then that column should be at the end. In this case, you'll need to change from select * to specific columns. (Many SQL gurus discourage select * anyway, for reasons relating to defensive programming and unsupervised/automated queries.)

If you really "need" select *, then I suggest you write a sql VIEW that orders the columns correctly, and then allows one to select *. Crafting this is rather simple assuming that the user permissions in the database allows it. If so, then it may be as simple as
```
CREATE VIEW myview AS
select column1, column3, column5,
  bigcolumn2, bigcolumn4
from mhealthpbi1.report.blood_test
```
and then subsequently use select * from myview.
Update your version of odbc-1.3.1 or newer (1.3.3 is current as of this writing), as it should resolve the issue.

Notes:

https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/getting-long-data