MS-SQL incompatibility with system ODBC

[detule]

Hi @s-u This is a follow up to both https://github.com/R-macos/recipes/issues/41 and https://github.com/R-macos/recipes/pull/46

When you closed the pull request you asked for the issue to be reproduced with the CRAN [R] build. Please see below:

library(odbc)
conn <- DBI::dbConnect(odbc::odbc(), dsn="mssql_oem_db", uid="<snip>", pwd="<snip>", Encrypt="No")
DBI::dbExecute(conn, "DROP TABLE ##og_temp")
[1] 0
DBI::dbExecute(conn, "CREATE TABLE ##og_temp ( id INT, name NVARCHAR(200) )")
[1] 0
> DBI::dbExecute(conn, "INSERT INTO ##og_temp (id, name) VALUES (1, 'abcdefghi')")
[1] 1
> DBI::dbGetQuery(conn, "SELECT * FROM ##og_temp")
  id name
1  1 aceg
> sessionInfo()
R version 4.3.2 (2023-10-31)
Platform: x86_64-apple-darwin20 (64-bit)
Running under: macOS Sonoma 14.0

Matrix products: default
BLAS:   /Library/Frameworks/R.framework/Versions/4.3-x86_64/Resources/lib/libRblas.0.dylib
LAPACK: /Library/Frameworks/R.framework/Versions/4.3-x86_64/Resources/lib/libRlapack.dylib;  LAPACK version 3.11.0

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

time zone: America/New_York
tzcode source: internal

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base

other attached packages:
[1] odbc_1.3.5

loaded via a namespace (and not attached):
 [1] bit_4.0.5       compiler_4.3.2  cli_3.6.1       hms_1.1.3
 [5] DBI_1.1.3       Rcpp_1.0.11     bit64_4.0.5     vctrs_0.6.4
 [9] blob_1.2.4      lifecycle_1.0.4 pkgconfig_2.0.3 rlang_1.1.2

Expectation is that the result from the retrieved query is abcdefghi.

I am trying not to get into debugging issues specific to package:odbc - happy to pursue those elsewhere. If you can confirm that the SQL odbc headers provided by unixODBC are not being clobbered over by iODBC on the build server, I will stop bugging you.

Thanks

[detule]

Simon,

As another data-point:

If you take a look at the CRAN install log for the package: https://www.r-project.org/nosvn/R.check/r-release-macos-x86_64/odbc-00install.html you can see numerous deprecation warnings for essentially all of the ODBC API. I think these deprecation warnings are (only) part of the SQL headers that ship with iODBC.

Appreciate the amount of time you have invested in this, hope we can work together to see this through.

[s-u]

The example above fails with

> library(odbc)
> conn <- DBI::dbConnect(odbc::odbc(), dsn="mssql_oem_db", uid="<snip>", pwd="<snip>", Encrypt="No")
Error: nanodbc/nanodbc.cpp:1135: 00000: [unixODBC][Driver Manager]Data source name not found and no default driver specified 

so it is not very helpful nor reproducible.

As noted in the PR, the issue has been fixed as far as I can tell. However, the odbc package has not been updated for months so it has not used the new library set. I have triggered a manual re-build, but given the lack of a reproducible example we can only hope that it addresses the issue.

[detule]

Thanks Simon -

The CRAN install log looks clean now / no deprecation warnings. Looks like the right headers are being picked up. Appreciate your help.