How to do a "Select * from" query using odbc

Question

I'm using the odbc package in R, and I have an SQL Server database with a table that has a Name column that is nvarchar(max) and a PublishStatus column that is an integer.

This code doesn't work:

library(odbc)
library(DBI)
library(tidyverse)
con_string="Driver=ODBC Driver 11 for SQL Server;Server=myServer; Database=MyDatabase; trusted_connection=yes"
con=dbConnect(odbc::odbc(), .connection_string =con_string)
query="select * from MyTable"
result=NULL
result=dbSendQuery(con,query) %>% dbFetch
head(result)

It just produces the error message

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

If I attempt to query again, I get a different error message and as best I can tell there is no way to recover without closing R and reopening:

Error: 'select PublishStatus,Name from MyTable' nanodbc/nanodbc.cpp:1587: HY000: [Microsoft][ODBC Driver 11 for SQL Server]Connection is busy with results for another command

Because both R and R's odbc are terribly named, it's hard to google errors in this package. In this SO it appears that the order of the columns matter, and it requires integer columns to be specified first in the query.

So this works

query="select PublishStatus,Name from MyTable"
result=NULL
result=dbSendQuery(con,query) %>% dbFetch
head(result)

but this does not:

query="select Name,PublishStatus from MyTable"
result=NULL
result=dbSendQuery(con,query) %>% dbFetch
head(result)

and the select * query does not.

So my questions are:

1. Is there a way to make it so I can do select * queries?
2. When I do get the Invalid Descriptor Index error, is there a way to recover without restarting R?

If not, this seems like an awfully bizarre deal breaker for this package.

Edit: using the older RODBC library, it doesn't have this flaw, even with the same ODBC driver. This works fine with select * from queries, and doesn't care about the order of the columns

library(RODBC)
con=odbcDriverConnect(ConnectionString_Hemonc)  
result=sqlQuery(con,query,stringsAsFactors=FALSE)
close(con)
head(result)

I had previously abandoned RODBC because it is unable (in practice) to write data to a database, as I discovered here.

It looks like what happened is they built odbc to read data faster, and a side effect of that is it's now very picky about the order in which data is read. Unfortunately, this demolishes my use case--I can't ask folks who only know basic SQL to use a tool that treats perfectly valid SQL as invalid.

I guess it's RODBC for reading data, odbc for writing data. Yikes.

Edit 2: I tried ODBC Driver 13 for SQL Server in the connection string instead of ODBC Driver 11 and it didn't fix the problem, but hey at least it was measurably faster.

Answer 1

I can hope that the below code and the article will help you. Getting Data From Excel and SQL Server using ODBC

set-psdebug -strict
$ErrorActionPreference = "stop" 

$ExcelFilePath='MyPath\pubs.xlsx' #the full path of the excel workbook
if (!(Test-Path $ExcelFilePath))
 {
 Write-Error "Can't find '$($ExcelFilePath)'. Sorry, can't proceed because of this"
 exit
 }

try {
$Connection = New-Object system.data.odbc.odbcconnection
$Connection.ConnectionString = 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ='+$ExcelFilePath+'; Extended Properties="Mode=ReadWrite;ReadOnly=false; HDR=YES"' 
$Connection.Open()
}
catch
{
 $ex = $_.Exception
 Write-Error "whilst opening connection to $ExcelFilePath : Sorry, can't proceed because of this"
 exit
}

try { 
$Query = New-Object system.data.odbc.odbccommand
$Query.Connection = $connection
$Query.CommandText = @'
SELECT title, SUM(qty) AS sales,
 COUNT(*) AS orders
 FROM [titles$] t
 INNER JOIN [sales$] s ON t.title_id=s.title_id
 WHERE title like '%?'
 GROUP BY title
 ORDER BY SUM(qty) DESC
'@ 

$Reader = $Query.ExecuteReader([System.Data.CommandBehavior]::SequentialAccess) #get the datareader and just get the result in one gulp
}
catch
{
 $ex = $_.Exception
 Write-Error "whilst executing the query '$($Query.CommandText)' $ex.Message Sorry, but we can't proceed because of this!"
 $Reader.Close()
 $Connection.Close()
 Exit;
}

Try
{
$Counter = $Reader.FieldCount #get it just once
$result=@() #initialise the empty array of rows
 while ($Reader.Read()) {
 $Tuple = New-Object -TypeName 'System.Management.Automation.PSObject'
 foreach ($i in (0..($Counter - 1))) {
 Add-Member `
 -InputObject $Tuple `
 -MemberType NoteProperty `
 -Name $Reader.GetName($i) `
 -Value $Reader.GetValue($i).ToString()
 }
 $Result+=$Tuple
 }
 $result | Format-Table 
 }
catch
{
 $ex = $_.Exceptio
 Write-Error "whilst reading the data from the datatable. $ex.Message"
}
$Reader.Close()
$Connection.Close()