Error when nvarchar(MAX) and/or varbinary(MAX) are in the SELECT statement while using bind parameters with multiple sets of values

[buitthien]

library(DBI)
library(odbc)

mycon <- dbConnect(odbc::odbc(), Driver="ODBC Driver 17 for SQL Server", Server = 'mydbserver', UID = 'myuid', PWD = 'mypasswd', database = 'mydb')

createTable <- dbExecute(mycon, "CREATE TABLE epi.dbo.People (id int NOT NULL, longid varbinary(MAX), name char(10) NOT NULL, comments nvarchar(MAX))")
insertRows <- dbExecute(mycon, "INSERT INTO epi.dbo.People (id, longid, name, comments) VALUES (1,1,'First','1st person'), (2,2,'Second','2nd person'), (3,3,'Third','3rd person')")

### Using dbGetQuery
### Works with one single set of values
mydf <- dbGetQuery(mycon,"select * from epi.dbo.People where id = ? and name = ?",params=list(2,'Second'))

### Does not work with two or more sets of values, i.e. batch of values
mydf <- dbGetQuery(mycon,"select * from epi.dbo.People where id = ? and name = ?",params=list(c(1,2),c('First','Second')))

### Two or more sets of values: works without nvarchar(MAX) or varbinary(MAX)
mydf <- dbGetQuery(mycon,"select id, name from epi.dbo.People where id = ? and name = ?",params=list(c(1,2),c('First','Second')))

### Two or more sets of values: does not work with nvarchar(MAX)
mydf <- dbGetQuery(mycon,"select id, name, comments from epi.dbo.People where id = ? and name = ?",params=list(c(1,2),c('First','Second')))

### Two or more sets of values: does not work with varbinary(MAX)
mydf <- dbGetQuery(mycon,"select id, name, longid from epi.dbo.People where id = ? and name = ?",params=list(c(1,2),c('First','Second')))


### Similar issue with dbSendQuery/dbBind/dbFetch 
myres <- dbSendQuery(mycon,"select * from epi.dbo.People where id = ? and name = ?")

### Single set of values works
dbBind(myres,params=list(c(2),c('Second')))
mydf <- dbFetch(myres)

### Two or more sets of values does not work
dbBind(myres,params=list(c(1,2),c('First','Second')))
mydf <- dbFetch(myres)

When it does not work, this is the error:
Error in result_fetch(res@ptr, n) :
nanodbc/nanodbc.cpp:3280: HY109: [Microsoft][ODBC Driver 17 for SQL Server]Invalid cursor position

I think the error is caused by having a nvarchar(MAX) and/or varbinary(MAX) in the SELECT statement while using bind parameters with multiple sets of values.