Connection problems in Windows 7?

[florisvdh]

While I wanted to test the development version of the watina package on my Windows 7 partition, connect_watina() was not successful because of the following problem on this Windows 7 installation with connect_inbo_dbase():

library(inbodb)
con <- connect_inbo_dbase("W0002_00_Watina")
#> Error: nanodbc/nanodbc.cpp:1021: IM002: [Microsoft][ODBC-stuurprogrammabeheer] De naam van de gegevensbron is niet gevonden en er is geen standaardstuurprogramma opgegeven

^{Created on 2021-01-12 by the reprex package (v0.3.0)}

Session info

devtools::session_info()
#> - Session info ---------------------------------------------------------------
#>  setting  value                       
#>  version  R version 4.0.3 (2020-10-10)
#>  os       Windows 7 x64 SP 1          
#>  system   i386, mingw32               
#>  ui       RTerm                       
#>  language (EN)                        
#>  collate  Dutch_Belgium.1252          
#>  ctype    Dutch_Belgium.1252          
#>  tz       Europe/Paris                
#>  date     2021-01-12                  
#> 
#> - Packages -------------------------------------------------------------------
#>  package     * version    date       lib source                      
#>  assertthat    0.2.1      2019-03-21 [1] CRAN (R 4.0.3)              
#>  bit           4.0.4      2020-08-04 [1] CRAN (R 4.0.3)              
#>  bit64         4.0.5      2020-08-30 [1] CRAN (R 4.0.3)              
#>  blob          1.2.1      2020-01-20 [1] CRAN (R 4.0.3)              
#>  callr         3.5.1      2020-10-13 [1] CRAN (R 4.0.3)              
#>  cli           2.2.0      2020-11-20 [1] CRAN (R 4.0.3)              
#>  crayon        1.3.4      2017-09-16 [1] CRAN (R 4.0.3)              
#>  DBI           1.1.0      2019-12-15 [1] CRAN (R 4.0.3)              
#>  desc          1.2.0      2018-05-01 [1] CRAN (R 4.0.3)              
#>  devtools      2.3.2      2020-09-18 [1] CRAN (R 4.0.3)              
#>  digest        0.6.27     2020-10-24 [1] CRAN (R 4.0.3)              
#>  dplyr         1.0.2      2020-08-18 [1] CRAN (R 4.0.3)              
#>  ellipsis      0.3.1      2020-05-15 [1] CRAN (R 4.0.3)              
#>  evaluate      0.14       2019-05-28 [1] CRAN (R 4.0.3)              
#>  fansi         0.4.1      2020-01-08 [1] CRAN (R 4.0.3)              
#>  fs            1.5.0      2020-07-31 [1] CRAN (R 4.0.3)              
#>  generics      0.1.0      2020-10-31 [1] CRAN (R 4.0.3)              
#>  glue          1.4.2      2020-08-27 [1] CRAN (R 4.0.3)              
#>  highr         0.8        2019-03-20 [1] CRAN (R 4.0.3)              
#>  hms           0.5.3      2020-01-08 [1] CRAN (R 4.0.3)              
#>  htmltools     0.5.0      2020-06-16 [1] CRAN (R 4.0.3)              
#>  inbodb      * 0.0.0.9000 2021-01-12 [1] Github (inbo/inbodb@c831bf7)
#>  knitr         1.30       2020-09-22 [1] CRAN (R 4.0.3)              
#>  lifecycle     0.2.0      2020-03-06 [1] CRAN (R 4.0.3)              
#>  magrittr      2.0.1      2020-11-17 [1] CRAN (R 4.0.3)              
#>  memoise       1.1.0      2017-04-21 [1] CRAN (R 4.0.3)              
#>  odbc          1.3.0      2020-10-27 [1] CRAN (R 4.0.3)              
#>  pillar        1.4.7      2020-11-20 [1] CRAN (R 4.0.3)              
#>  pkgbuild      1.2.0      2020-12-15 [1] CRAN (R 4.0.3)              
#>  pkgconfig     2.0.3      2019-09-22 [1] CRAN (R 4.0.3)              
#>  pkgload       1.1.0      2020-05-29 [1] CRAN (R 4.0.3)              
#>  prettyunits   1.1.1      2020-01-24 [1] CRAN (R 4.0.3)              
#>  processx      3.4.5      2020-11-30 [1] CRAN (R 4.0.3)              
#>  ps            1.5.0      2020-12-05 [1] CRAN (R 4.0.3)              
#>  purrr         0.3.4      2020-04-17 [1] CRAN (R 4.0.3)              
#>  R6            2.5.0      2020-10-28 [1] CRAN (R 4.0.3)              
#>  Rcpp          1.0.5      2020-07-06 [1] CRAN (R 4.0.3)              
#>  remotes       2.2.0      2020-07-21 [1] CRAN (R 4.0.3)              
#>  rlang         0.4.10     2020-12-30 [1] CRAN (R 4.0.3)              
#>  rmarkdown     2.6        2020-12-14 [1] CRAN (R 4.0.3)              
#>  rprojroot     2.0.2      2020-11-15 [1] CRAN (R 4.0.3)              
#>  sessioninfo   1.1.1      2018-11-05 [1] CRAN (R 4.0.3)              
#>  stringi       1.5.3      2020-09-09 [1] CRAN (R 4.0.3)              
#>  stringr       1.4.0      2019-02-10 [1] CRAN (R 4.0.3)              
#>  testthat      3.0.1      2020-12-17 [1] CRAN (R 4.0.3)              
#>  tibble        3.0.4      2020-10-12 [1] CRAN (R 4.0.3)              
#>  tidyselect    1.1.0      2020-05-11 [1] CRAN (R 4.0.3)              
#>  usethis       2.0.0      2020-12-10 [1] CRAN (R 4.0.3)              
#>  vctrs         0.3.6      2020-12-17 [1] CRAN (R 4.0.3)              
#>  withr         2.3.0      2020-09-22 [1] CRAN (R 4.0.3)              
#>  xfun          0.19       2020-10-30 [1] CRAN (R 4.0.3)              
#>  yaml          2.2.1      2020-02-01 [1] CRAN (R 4.0.3)              
#> 
#> [1] C:/R/library
#> [2] C:/Program Files/R/R-4.0.3/library

While debugging, it appears that I only have SQL Server in the list of ODBC drivers. Output of the driversvec object:

> driversvec
 [1] "SQL Server"                                            
 [2] "Microsoft ODBC for Oracle"                             
 [3] "Microsoft Access Driver (*.mdb)"                       
 [4] "Microsoft Access-Treiber (*.mdb)"                      
 [5] "Driver do Microsoft Access (*.mdb)"                    
 [6] "Microsoft dBase Driver (*.dbf)"                        
 [7] "Microsoft dBase-Treiber (*.dbf)"                       
 [8] "Driver do Microsoft dBase (*.dbf)"                     
 [9] "Microsoft Excel Driver (*.xls)"                        
[10] "Microsoft Excel-Treiber (*.xls)"                       
[11] "Driver do Microsoft Excel(*.xls)"                      
[12] "Microsoft Paradox Driver (*.db )"                      
[13] "Microsoft Paradox-Treiber (*.db )"                     
[14] "Driver do Microsoft Paradox (*.db )"                   
[15] "Microsoft Text Driver (*.txt; *.csv)"                  
[16] "Microsoft Text-Treiber (*.txt; *.csv)"                 
[17] "Driver da Microsoft para arquivos texto (*.txt; *.csv)"
[18] "Microsoft Visual FoxPro Driver"                        
[19] "Microsoft FoxPro VFP Driver (*.dbf)"                   
[20] "Microsoft dBase VFP Driver (*.dbf)"                    
[21] "Microsoft Visual FoxPro-Treiber"                       
[22] "Driver para o Microsoft Visual FoxPro"                 
[23] "Microsoft Access Driver (*.mdb, *.accdb)"              
[24] "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"
[25] "Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)"   
[26] "Microsoft Access Text Driver (*.txt, *.csv)"

But in PR #15 the search string has been limited to ODBC Driver:

inbodb/R/connect_inbo_dbase.R

Lines 50 to 55 in c831bf7

	# look up most recent ODBC Driver for SQL Server
	driversvec <- unique(odbcListDrivers()$name)
	drivers_sql <- driversvec[grepl("SQL Server", driversvec)]
	drivers_sql_odbc <-
	drivers_sql[grepl("ODBC Driver", drivers_sql)]
	sql_driver <- tail(sort(drivers_sql_odbc), 1)

State of the following objects, then, becomes:

Browse[2]> drivers_sql
[1] "SQL Server"
Browse[2]> sql_driver
character(0)

When overriding this, and running the remainder of the function, the result is fine.

Browse[2]> sql_driver <- drivers_sql
Browse[2]> sql_driver
[1] "SQL Server"
Browse[2]> f
exiting from: connect_inbo_dbase("W0002_00_Watina")
> con
<OdbcConnection> INBO\<<<loginname>>>@<<<SQLSERVER08-I masked its name>>>\DWH
  Database: W0002_00_Watina
  Microsoft SQL Server Version: 13.00.5820

So I guess the simplification in #15 seems to have lost support for specific cases.

How should we proceed further @ElsLommelen? Should e0a1925, e6a553f and/or d784f5d be reverted (or revisited)? Or can I apply a local trick to solve this?

FYI, the next release of watina will now fully rely on connect_inbo_dbase()'s capabilities for handling UTF-8 👍 , so it will drop its earlier functions convertdf_enc() and collect() (currently in the connect branch, soon in dev_nextrelease branch).

[florisvdh]

Works fine with abe8b48:

library(inbodb)
watina <- connect_inbo_dbase("W0002_00_Watina")
watina
#> <OdbcConnection> INBO\<<<loginname>>>@<<<SQLSERVER08-I masked its name>>>\DWH
#>   Database: W0002_00_Watina
#>   Microsoft SQL Server Version: 13.00.5820

^{Created on 2021-01-12 by the reprex package (v0.3.0)}

Session info

devtools::session_info()
#> - Session info ---------------------------------------------------------------
#>  setting  value                       
#>  version  R version 4.0.3 (2020-10-10)
#>  os       Windows 7 x64 SP 1          
#>  system   i386, mingw32               
#>  ui       RTerm                       
#>  language (EN)                        
#>  collate  Dutch_Belgium.1252          
#>  ctype    Dutch_Belgium.1252          
#>  tz       Europe/Paris                
#>  date     2021-01-12                  
#> 
#> - Packages -------------------------------------------------------------------
#>  package     * version    date       lib source                      
#>  assertthat    0.2.1      2019-03-21 [1] CRAN (R 4.0.3)              
#>  bit           4.0.4      2020-08-04 [1] CRAN (R 4.0.3)              
#>  bit64         4.0.5      2020-08-30 [1] CRAN (R 4.0.3)              
#>  blob          1.2.1      2020-01-20 [1] CRAN (R 4.0.3)              
#>  callr         3.5.1      2020-10-13 [1] CRAN (R 4.0.3)              
#>  cli           2.2.0      2020-11-20 [1] CRAN (R 4.0.3)              
#>  crayon        1.3.4      2017-09-16 [1] CRAN (R 4.0.3)              
#>  DBI           1.1.0      2019-12-15 [1] CRAN (R 4.0.3)              
#>  desc          1.2.0      2018-05-01 [1] CRAN (R 4.0.3)              
#>  devtools      2.3.2      2020-09-18 [1] CRAN (R 4.0.3)              
#>  digest        0.6.27     2020-10-24 [1] CRAN (R 4.0.3)              
#>  dplyr         1.0.2      2020-08-18 [1] CRAN (R 4.0.3)              
#>  ellipsis      0.3.1      2020-05-15 [1] CRAN (R 4.0.3)              
#>  evaluate      0.14       2019-05-28 [1] CRAN (R 4.0.3)              
#>  fansi         0.4.1      2020-01-08 [1] CRAN (R 4.0.3)              
#>  fs            1.5.0      2020-07-31 [1] CRAN (R 4.0.3)              
#>  generics      0.1.0      2020-10-31 [1] CRAN (R 4.0.3)              
#>  glue          1.4.2      2020-08-27 [1] CRAN (R 4.0.3)              
#>  highr         0.8        2019-03-20 [1] CRAN (R 4.0.3)              
#>  hms           0.5.3      2020-01-08 [1] CRAN (R 4.0.3)              
#>  htmltools     0.5.0      2020-06-16 [1] CRAN (R 4.0.3)              
#>  inbodb      * 0.0.0.9000 2021-01-12 [1] Github (inbo/inbodb@abe8b48)
#>  knitr         1.30       2020-09-22 [1] CRAN (R 4.0.3)              
#>  lifecycle     0.2.0      2020-03-06 [1] CRAN (R 4.0.3)              
#>  magrittr      2.0.1      2020-11-17 [1] CRAN (R 4.0.3)              
#>  memoise       1.1.0      2017-04-21 [1] CRAN (R 4.0.3)              
#>  odbc          1.3.0      2020-10-27 [1] CRAN (R 4.0.3)              
#>  pillar        1.4.7      2020-11-20 [1] CRAN (R 4.0.3)              
#>  pkgbuild      1.2.0      2020-12-15 [1] CRAN (R 4.0.3)              
#>  pkgconfig     2.0.3      2019-09-22 [1] CRAN (R 4.0.3)              
#>  pkgload       1.1.0      2020-05-29 [1] CRAN (R 4.0.3)              
#>  prettyunits   1.1.1      2020-01-24 [1] CRAN (R 4.0.3)              
#>  processx      3.4.5      2020-11-30 [1] CRAN (R 4.0.3)              
#>  ps            1.5.0      2020-12-05 [1] CRAN (R 4.0.3)              
#>  purrr         0.3.4      2020-04-17 [1] CRAN (R 4.0.3)              
#>  R6            2.5.0      2020-10-28 [1] CRAN (R 4.0.3)              
#>  Rcpp          1.0.5      2020-07-06 [1] CRAN (R 4.0.3)              
#>  remotes       2.2.0      2020-07-21 [1] CRAN (R 4.0.3)              
#>  rlang         0.4.10     2020-12-30 [1] CRAN (R 4.0.3)              
#>  rmarkdown     2.6        2020-12-14 [1] CRAN (R 4.0.3)              
#>  rprojroot     2.0.2      2020-11-15 [1] CRAN (R 4.0.3)              
#>  sessioninfo   1.1.1      2018-11-05 [1] CRAN (R 4.0.3)              
#>  stringi       1.5.3      2020-09-09 [1] CRAN (R 4.0.3)              
#>  stringr       1.4.0      2019-02-10 [1] CRAN (R 4.0.3)              
#>  testthat      3.0.1      2020-12-17 [1] CRAN (R 4.0.3)              
#>  tibble        3.0.4      2020-10-12 [1] CRAN (R 4.0.3)              
#>  tidyselect    1.1.0      2020-05-11 [1] CRAN (R 4.0.3)              
#>  usethis       2.0.0      2020-12-10 [1] CRAN (R 4.0.3)              
#>  vctrs         0.3.6      2020-12-17 [1] CRAN (R 4.0.3)              
#>  withr         2.3.0      2020-09-22 [1] CRAN (R 4.0.3)              
#>  xfun          0.19       2020-10-30 [1] CRAN (R 4.0.3)              
#>  yaml          2.2.1      2020-02-01 [1] CRAN (R 4.0.3)              
#> 
#> [1] C:/R/library
#> [2] C:/Program Files/R/R-4.0.3/library

[florisvdh]

Probably it's because I still have an older ODBC driver in Windows (version 13 vs. 17). @ElsLommelen I could likely add backward compatibility to take the last element of drivers_sql in case length(drivers_sql_odbc) == 0. Shall I do that?

EDIT: I don't even have the ODBC Driver for SQL Server it seems; just driver SQL Server. (Forget about version number 13 - see further.)

[ElsLommelen]

@florisvdh It was advised here (at the very end of the issue) to use an ODBC Driver xx for SQL Server (with xx a version) instead of the driver SQL Server to establish a odbc connection (the latter caused problems in my case). This driver is installed by default at INBO (I checked this with IT), so your problem can only occur in the few cases where the INBO installation default is not applied... Maybe the best option is to drop an error if the ODBC driver is not installed (to motivate users to install an appropriate driver and avoid problems with it)?

I'm not sure an old odbc driver is causing the problem, I tested ODBC Driver 13 for SQL Server as well before I changed the selection of the driver (which was some months ago, after the new odbc version was released). The driver version is not important, only the fact that it is an odbc driver.

[florisvdh]

Thanks for explaining the difference. (I erroneously cited version 13 from the printed connection output, however this seems independent of driver version, that might be the SQL Server (database) version).

Maybe the best option is to drop an error if the ODBC driver is not installed

Yes, if it would say e.g.: 'Please contact your system administrator: you are missing the ODBC Driver for SQL Server.' that could be convenient to occasional Windows users. But there's no hurry, and I leave decision to you anyway.

[damianooldoni]

As I wrote in #22, what do you think about this two ideas:

1. search for SQL Native driver if search for ODBC fails and use it
2. arise a warning instead of an error maybe suggesting to install ODBC drivers in the future or contacting IT helpdesk of your organization

For the most of the users SQL Native driver is enough as pointed out by @Jo-Loos.

Thanks a lot in advance!

[ElsLommelen]

@Jo-Loos Is it the SQL Server Native Client that should be used in this case? Can we be sure this one is installed for all INBO users? (It was told to me that the ODBC driver was installed by default at INBO, but apparently there are exceptions for this.)

[florisvdh]

@ElsLommelen what's the relation with your earlier suggestion and the linked odbc discussion there: #17 (comment)?

It is important that behaviour is consistent between users, and that the results are optimal. (I'm not familiar with the potential problems) If that means all users better have the same driver installed, notably the ODBC Driver xx for SQL Server, then I feel we should go that way: install it. I understood that was the reason why connect_inbo_dbase() now throws an error if you don't have that driver. (BTW in Linux it is the ODBC driver which is available)

[florisvdh]

Especially this comment of the odbc discussion, would mean that SQL Server is not the recommendable driver for use with odbc package. Anyway, the odbc developers seem to only take into account the ODBC driver.