I am trying to run a simple query using R which is listed below. I am connected to odbc and it works fine with other queries.
MyQuery <- (dbSendQuery(conn, "Select count(ABC) FROM TABLE_1 GROUP BY YEAR(DATE_TIME)"))
When I run the above mentioned query in R environment it shows the below following result. The same query works fine in SQL Management Studio and return me the counted values.
<OdbcResult>
Rows Fetched:0 [incomplete]
Changed: 0
When I view it is shows
Name Type Value
MyQuery S4(odbc:odbcResult) S4 Object of Class OdbcResult
Does this returns me the value location instead of actual values ?
CodePudding user response:
You're using dbSendQuery
which, as its name suggests, sends the query. If you want to send and get the results in one command use dbGetQuery
. dbSendQuery
is good if, either, you don't care about the results or if you know/suspect the results are going to be really big and you just want to fetch them in defined sized groups using dbFetch
and then clearing the results using dbClearResult
In your example you could do:
MyQuery <- (dbSendQuery(conn, "Select count(ABC) FROM TABLE_1 GROUP BY YEAR(DATE_TIME)"))
querytable <- dbFetch(MyQuery, n=100) #assuming 100 or fewer results
dbClearResult(MyQuery)
or just
querytable <- (dbGetQuery(conn, "Select count(ABC) FROM TABLE_1 GROUP BY YEAR(DATE_TIME)"))