SQL Server uses wrong data types with DBI::dbSendStatement() and DBI::dbBind()

[sn0001]

We have a table [adj].[Assortment Snapshot] in our SQL Server database with the follwing data types:

[Customer UUID] [uniqueidentifier] NOT NULL,
[Branch Number] [int] NOT NULL,
[Article Number] [int] NOT NULL,
[Valid On] [date] NOT NULL,
[Comment] [nvarchar](max) NULL

I send a query to this table with dbSendStatement()...

<OdbcResult>
  SQL  SELECT [Customer UUID] AS [Customer],
[Branch Number] AS [Branch],
[Valid On] AS [Date],
[Article Number] AS [Article] FROM adj.[Assortment Snapshot] WHERE [Customer UUID] = ? AND [Branch Number] IN (?) AND [Valid On] >= ? AND [Valid On] <= ?
  ROWS Fetched: 0 [complete]
       Changed: 0

...followed by dbBind() to bind the following parameters:

> print(lstParams)
$param_1_crit_1
[1] "10fb9494-8bc0-11eb-8dcd-0242ac130003"

$param_2_crit_2
[1] 100

$param_3_crit_3
[1] "2021-03-24"

$param_4_crit_4
[1] "2021-04-06"

The parameters are of the following classes:

> sapply(lstParams, class)
param_1_crit_1 param_2_crit_2 param_3_crit_3 param_4_crit_4 
   "character"      "integer"         "Date"         "Date"

Checking the server logs, this is the query that I get:

declare @p1 int
set @p1=12
exec sp_prepexec @p1 output,

N'@P1 uniqueidentifier,@P2 int,@P3 nvarchar(10),@P4 nvarchar(10)',

N'SELECT [Customer UUID] AS [Customer],
[Branch Number] AS [Branch],
[Valid On] AS [Date],
[Article Number] AS [Article] 

FROM adj.[Assortment Snapshot] WHERE [Customer UUID] = @P1 AND [Branch Number] IN (@P2) AND [Valid On] >= @P3 AND [Valid On] <= @P4','ED5FADA7-90E9-409F-B210-85E61528E120',3038,N'2021-03-24',N'2021-04-06'
select @p1

While the parameter [Customer UUID] is correctly recognized as uniqueidentifier despite having been passed as character by dbBind(), the dates are turned into nvarchar(10). Queries involving table-valued SQL functions even result in all parameters being turned into varchar(255).

These conversions mean a huge performance problem: Executing the above SQL query in SQL Server Management Studio returns results almost immediately. The same query executed via dbSendStatement() and dbBind() takes several seconds to minutes.

How can the conversions described above be avoided?

---

• SQL Server version: 15.00.4102
• R version: Microsoft R Open 4.0.2
• DBI package version: 1.1.1

[krlmlr]

Thanks. What happens if you omit the date filter in the query? What happens if you omit all filters?

[sn0001]

Thanks for your quick reply. Omitting date filters does not change the data type of the other filters:

declare @p1 int
set @p1=13
exec sp_prepexec @p1 output,N'@P1 uniqueidentifier,@P2 int',
N'SELECT [Customer UUID] AS [Customer],
[Branch Number] AS [Branch],
[Valid On] AS [Date],
[Article Number] AS [Article] FROM adj.[Assortment Snapshot] WHERE [Customer UUID] = @P1 AND [Branch Number] IN (@P2)','10fb9494-8bc0-11eb-8dcd-0242ac130003',100
select @p1

Omitting any filters also omits any @PX variables in the query, so there is no question of what data type is used.

NB: All queries return valid results. They are just very slow due to the strings in the WHERE clause.

[simonpcouch]

Reproducible example:

library(DBI)
library(odbc)
con <- dbConnect(odbc(), dsn = "MicrosoftSQLServer", uid = "SA",
                 pwd = Sys.getenv("sqlServerPass"))

con
#> <OdbcConnection> dbo@sql2
#>   Database: master
#>   Microsoft SQL Server Version: 16.00.4095

# some setup:
dbExecute(con, "CREATE SCHEMA adj")
#> [1] 0
dbExecute(
  con,
  "CREATE TABLE [adj].[Assortment Snapshot] (
    CustomerUUID uniqueidentifier NOT NULL,
    BranchNumber int NOT NULL,
    ArticleNumber int NOT NULL,
    ValidOn date NOT NULL,
    Comment nvarchar(max) NULL
   );"
)
#> [1] 0
# add some example rows:
dbExecute(
  con,
  "INSERT INTO [adj].[Assortment Snapshot] (CustomerUUID, BranchNumber, ArticleNumber, ValidOn, Comment)
VALUES
    ('F47AC10B-58CC-4372-A567-0E02B2C3D479', 1, 1001, '2021-03-06', 'Sample comment 1'),
    ('6F9619FF-8B86-D011-B42D-00C04FC964FF', 2, 1002, '2021-03-27', 'Sample comment 2'),
    ('6F9619FF-8B86-D011-B42D-00C04FC964FF', 3, 1003, '2021-04-28', NULL),
    ('F47AC10B-58CC-4372-A567-0E02B2C3D479', 1, 1004, '2021-02-26', 'Sample comment 3');"
)
#> [1] 4

# params -- note that this matches the second row
params <- list("6F9619FF-8B86-D011-B42D-00C04FC964FF", 2L, as.Date("2021-03-24"), as.Date("2021-04-06"))

sapply(params, class)
#> [1] "character" "integer"   "Date"      "Date"

res <- dbSendStatement(
  con,
  "SELECT [CustomerUUID] AS [Customer],
   [BranchNumber] AS [Branch],
   [ValidOn] AS [Date],
   [ArticleNumber] AS [Article] FROM adj.[Assortment Snapshot] WHERE [CustomerUUID] = ? AND [BranchNumber] IN (?) AND [ValidOn] >= ? AND [ValidOn] <= ?"
)

res <- dbBind(res, params)

dbFetch(res)
#>                               Customer Branch       Date Article
#> 1 6F9619FF-8B86-D011-B42D-00C04FC964FF      2 2021-03-27    1002

dbClearResult(res)

^{Created on 2024-03-04 with reprex v2.1.0}

[detule]

Hey @simonpcouch

This issue may be OBE. Using the OEM driver, I see the third and fourth variable correctly bound as DATEs:

(@P1 uniqueidentifier,@P2 int,@P3 date,@P4 date)
SELECT [CustomerUUID] AS [Customer],
  [BranchNumber] AS [Branch],
  [ValidOn] AS [Date],
  [ArticleNumber] AS [Article] FROM [deleteme].[testschema].tbl WHERE [CustomerUUID] = @P1 AND [BranchNumber] IN (@P2) AND [ValidOn] >= @P3 AND [ValidOn] <= @P4

I suggest we close this; if the user comes back and provides information on the driver they are using and an up-to-date failure report we can re-open.

[simonpcouch]

I'm for it. Thanks @detule!