search

r-spatial / sf

Simple Features for R
https://r-spatial.github.io/sf/
Other
1.33k stars 294 forks source link

MSSQL Driver Failing in R 4.2.2 #2088

Closed tomroh closed 1 year ago

tomroh commented 1 year ago

R version 4.2.2 for the sf package windows binaries no longer support SQL Server drivers.

sf:::guess_driver_can_write('MSSQL:nc')

Results in this error:

Error: MSSQLSpatial driver not available in supported drivers, see `st_drivers()'

Writing to SQL server with st_write worked for windows sf binary packages for R <= 4.1

image
edzer commented 1 year ago

Taking the relevant sections from src/Makevars.win (crt) and src/Makevars.ucrt (ucrt, R >= 4.2.0), I see

crt = "-lgdal -lsqlite3 -lspatialite -lproj -lgeos_c -lgeos -ljson-c -lnetcdf -lmariadbclient -lpq -lpgport -lpgcommon -lwebp -lcurl -lssh2 -lssl -lhdf5_hl -lhdf5 -lexpat -lfreexl -lcfitsio -lmfhdf -lhdf -lxdr -lpcre -lopenjp2 -ljasper -lpng -ljpeg -ltiff -lgeotiff -lgif -lxml2 -llzma -lz -lzstd -lodbc32 -lodbccp32 -liconv -lpsapi -lwldap32 -lsecur32 -lgdi32 -lnormaliz -lcrypto -lcrypt32 -lws2_32 -lshlwapi -lbcrypt"
ucrt = "-lgdal -larmadillo -lopenblas -lgfortran -lquadmath -lpq -lpgcommon -lpgport -lodbc32 -lodbccp32 -lblosc -lkea -lhdf5_cpp -lhdf5 -lpoppler -llcms2 -lfreetype -lharfbuzz -lfreetype -llz4 -lpcre2-8 -lxml2 -lopenjp2 -lnetcdf -lmysqlclient -lspatialite -lgeos_c -lgeos -lminizip -lgeos -ljson-c -lgta -lfreexl -lexpat -lssl -lpsapi -lgif -lmfhdf -lhdf5_hl -lcrypto -lportablexdr -ldf -lhdf5 -lsz -lpng16 -lpng -lpoppler -llcms2 -lfreetype -lharfbuzz -lfreetype -llz4 -lpcre2-8 -lpcre -lcurl -lbcrypt -lrtmp -lssl -lssh2 -lidn2 -lunistring -liconv -lgcrypt -lcrypto -lgpg-error -lws2_32 -ltiff -llzma -ljpeg -lz -lcfitsio -lzstd -lwebpdecoder -lwebp -lsbml-static -lgeotiff -lproj -lsqlite3 -lbz2 -lcrypt32 -lwldap32 -lsecur32"

(crt_s = strsplit(crt, " ")[[1]])
#  [1] "-lgdal"          "-lsqlite3"       "-lspatialite"    "-lproj"         
#  [5] "-lgeos_c"        "-lgeos"          "-ljson-c"        "-lnetcdf"       
#  [9] "-lmariadbclient" "-lpq"            "-lpgport"        "-lpgcommon"     
# [13] "-lwebp"          "-lcurl"          "-lssh2"          "-lssl"          
# [17] "-lhdf5_hl"       "-lhdf5"          "-lexpat"         "-lfreexl"       
# [21] "-lcfitsio"       "-lmfhdf"         "-lhdf"           "-lxdr"          
# [25] "-lpcre"          "-lopenjp2"       "-ljasper"        "-lpng"          
# [29] "-ljpeg"          "-ltiff"          "-lgeotiff"       "-lgif"          
# [33] "-lxml2"          "-llzma"          "-lz"             "-lzstd"         
# [37] "-lodbc32"        "-lodbccp32"      "-liconv"         "-lpsapi"        
# [41] "-lwldap32"       "-lsecur32"       "-lgdi32"         "-lnormaliz"     
# [45] "-lcrypto"        "-lcrypt32"       "-lws2_32"        "-lshlwapi"      
# [49] "-lbcrypt"       
(ucrt_s = strsplit(ucrt, " ")[[1]])
#  [1] "-lgdal"        "-larmadillo"   "-lopenblas"    "-lgfortran"   
#  [5] "-lquadmath"    "-lpq"          "-lpgcommon"    "-lpgport"     
#  [9] "-lodbc32"      "-lodbccp32"    "-lblosc"       "-lkea"        
# [13] "-lhdf5_cpp"    "-lhdf5"        "-lpoppler"     "-llcms2"      
# [17] "-lfreetype"    "-lharfbuzz"    "-lfreetype"    "-llz4"        
# [21] "-lpcre2-8"     "-lxml2"        "-lopenjp2"     "-lnetcdf"     
# [25] "-lmysqlclient" "-lspatialite"  "-lgeos_c"      "-lgeos"       
# [29] "-lminizip"     "-lgeos"        "-ljson-c"      "-lgta"        
# [33] "-lfreexl"      "-lexpat"       "-lssl"         "-lpsapi"      
# [37] "-lgif"         "-lmfhdf"       "-lhdf5_hl"     "-lcrypto"     
# [41] "-lportablexdr" "-ldf"          "-lhdf5"        "-lsz"         
# [45] "-lpng16"       "-lpng"         "-lpoppler"     "-llcms2"      
# [49] "-lfreetype"    "-lharfbuzz"    "-lfreetype"    "-llz4"        
# [53] "-lpcre2-8"     "-lpcre"        "-lcurl"        "-lbcrypt"     
# [57] "-lrtmp"        "-lssl"         "-lssh2"        "-lidn2"       
# [61] "-lunistring"   "-liconv"       "-lgcrypt"      "-lcrypto"     
# [65] "-lgpg-error"   "-lws2_32"      "-ltiff"        "-llzma"       
# [69] "-ljpeg"        "-lz"           "-lcfitsio"     "-lzstd"       
# [73] "-lwebpdecoder" "-lwebp"        "-lsbml-static" "-lgeotiff"    
# [77] "-lproj"        "-lsqlite3"     "-lbz2"         "-lcrypt32"    
# [81] "-lwldap32"     "-lsecur32"    

setdiff(crt_s, ucrt_s)
# [1] "-lmariadbclient" "-lhdf"           "-lxdr"           "-ljasper"       
# [5] "-lgdi32"         "-lnormaliz"      "-lshlwapi"      
setdiff(ucrt_s, crt_s)
#  [1] "-larmadillo"   "-lopenblas"    "-lgfortran"    "-lquadmath"   
#  [5] "-lblosc"       "-lkea"         "-lhdf5_cpp"    "-lpoppler"    
#  [9] "-llcms2"       "-lfreetype"    "-lharfbuzz"    "-llz4"        
# [13] "-lpcre2-8"     "-lmysqlclient" "-lminizip"     "-lgta"        
# [17] "-lportablexdr" "-ldf"          "-lsz"          "-lpng16"      
# [21] "-lrtmp"        "-lidn2"        "-lunistring"   "-lgcrypt"     
# [25] "-lgpg-error"   "-lwebpdecoder" "-lsbml-static" "-lbz2"

but I can't tell which library causes the mssql driver to drop out. @rsbivand or @rhijmans do you have an idea?

rsbivand commented 1 year ago

It should be odbc, but including the link when gdal.a wasn't built with odbc will not complain.

edzer commented 1 year ago

Thanks; I looked at GH main branch, looking at the CRAN release Makevars.ucrt, we see odbc is missing in ucrt:

crt = "-lgdal -lsqlite3 -lspatialite -lproj -lgeos_c -lgeos -ljson-c -lnetcdf -lmariadbclient -lpq -lpgport -lpgcommon -lwebp -lcurl -lssh2 -lssl -lhdf5_hl -lhdf5 -lexpat -lfreexl -lcfitsio -lmfhdf -lhdf -lxdr -lpcre -lopenjp2 -ljasper -lpng -ljpeg -ltiff -lgeotiff -lgif -lxml2 -llzma -lz -lzstd -lodbc32 -lodbccp32 -liconv -lpsapi -lwldap32 -lsecur32 -lgdi32 -lnormaliz -lcrypto -lcrypt32 -lws2_32 -lshlwapi -lbcrypt"
ucrt = "-lgdal -lblosc -lkea -lhdf5_cpp -lhdf5 -lpoppler -llcms2 -lfreetype -lharfbuzz -lfreetype -llz4 -lpcre2-8 -lxml2 -lopenjp2 -lnetcdf -lmysqlclient -lspatialite -lgeos_c -lgeos -lminizip -lgeos -ljson-c -lgta -lfreexl -lexpat -lssl -lpsapi -lgif -lmfhdf -lhdf5_hl -lcrypto -lportablexdr -ldf -lhdf5 -lsz -lpng16 -lpng -lpoppler -llcms2 -lfreetype -lharfbuzz -lfreetype -llz4 -lpcre2-8 -lpcre -lcurl -lrtmp -lssl -lssh2 -lidn2 -lunistring -liconv -lgcrypt -lcrypto -lgpg-error -lws2_32 -ltiff -llzma -ljpeg -lz -lcfitsio -lzstd -lwebpdecoder -lwebp -lsbml-static -lgeotiff -lproj -lsqlite3 -lbz2 -lcrypt32 -lwldap32 -lsecur32"

(crt_s = strsplit(crt, " ")[[1]])
#  [1] "-lgdal"          "-lsqlite3"       "-lspatialite"    "-lproj"         
#  [5] "-lgeos_c"        "-lgeos"          "-ljson-c"        "-lnetcdf"       
#  [9] "-lmariadbclient" "-lpq"            "-lpgport"        "-lpgcommon"     
# [13] "-lwebp"          "-lcurl"          "-lssh2"          "-lssl"          
# [17] "-lhdf5_hl"       "-lhdf5"          "-lexpat"         "-lfreexl"       
# [21] "-lcfitsio"       "-lmfhdf"         "-lhdf"           "-lxdr"          
# [25] "-lpcre"          "-lopenjp2"       "-ljasper"        "-lpng"          
# [29] "-ljpeg"          "-ltiff"          "-lgeotiff"       "-lgif"          
# [33] "-lxml2"          "-llzma"          "-lz"             "-lzstd"         
# [37] "-lodbc32"        "-lodbccp32"      "-liconv"         "-lpsapi"        
# [41] "-lwldap32"       "-lsecur32"       "-lgdi32"         "-lnormaliz"     
# [45] "-lcrypto"        "-lcrypt32"       "-lws2_32"        "-lshlwapi"      
# [49] "-lbcrypt"       
(ucrt_s = strsplit(ucrt, " ")[[1]])
#  [1] "-lgdal"        "-lblosc"       "-lkea"         "-lhdf5_cpp"   
#  [5] "-lhdf5"        "-lpoppler"     "-llcms2"       "-lfreetype"   
#  [9] "-lharfbuzz"    "-lfreetype"    "-llz4"         "-lpcre2-8"    
# [13] "-lxml2"        "-lopenjp2"     "-lnetcdf"      "-lmysqlclient"
# [17] "-lspatialite"  "-lgeos_c"      "-lgeos"        "-lminizip"    
# [21] "-lgeos"        "-ljson-c"      "-lgta"         "-lfreexl"     
# [25] "-lexpat"       "-lssl"         "-lpsapi"       "-lgif"        
# [29] "-lmfhdf"       "-lhdf5_hl"     "-lcrypto"      "-lportablexdr"
# [33] "-ldf"          "-lhdf5"        "-lsz"          "-lpng16"      
# [37] "-lpng"         "-lpoppler"     "-llcms2"       "-lfreetype"   
# [41] "-lharfbuzz"    "-lfreetype"    "-llz4"         "-lpcre2-8"    
# [45] "-lpcre"        "-lcurl"        "-lrtmp"        "-lssl"        
# [49] "-lssh2"        "-lidn2"        "-lunistring"   "-liconv"      
# [53] "-lgcrypt"      "-lcrypto"      "-lgpg-error"   "-lws2_32"     
# [57] "-ltiff"        "-llzma"        "-ljpeg"        "-lz"          
# [61] "-lcfitsio"     "-lzstd"        "-lwebpdecoder" "-lwebp"       
# [65] "-lsbml-static" "-lgeotiff"     "-lproj"        "-lsqlite3"    
# [69] "-lbz2"         "-lcrypt32"     "-lwldap32"     "-lsecur32"    

setdiff(crt_s, ucrt_s)
#  [1] "-lmariadbclient" "-lpq"            "-lpgport"        "-lpgcommon"     
#  [5] "-lhdf"           "-lxdr"           "-ljasper"        "-lodbc32"       
#  [9] "-lodbccp32"      "-lgdi32"         "-lnormaliz"      "-lshlwapi"      
# [13] "-lbcrypt"       
setdiff(ucrt_s, crt_s)
#  [1] "-lblosc"       "-lkea"         "-lhdf5_cpp"    "-lpoppler"    
#  [5] "-llcms2"       "-lfreetype"    "-lharfbuzz"    "-llz4"        
#  [9] "-lpcre2-8"     "-lmysqlclient" "-lminizip"     "-lgta"        
# [13] "-lportablexdr" "-ldf"          "-lsz"          "-lpng16"      
# [17] "-lrtmp"        "-lidn2"        "-lunistring"   "-lgcrypt"     
# [21] "-lgpg-error"   "-lwebpdecoder" "-lsbml-static" "-lbz2"

@tomroh and @cmundy have you tried windows binaries from r-universe?

rsbivand commented 1 year ago

Please do not bother with r-universe. Rather install R-devel for Windows (to be 4.3), and sf/terra built with Rtools43 for R 4.3 (coming in April). I see "MSSQLSpatial" and "ODBC" there, which is why -lodbc* are already in Makevars.ucrt. I do not think that there is a viable DBI connection route to MSSQL, @etiennebr do you know whether a DBI connection could work (for Windows 4.2 users - Rtools43 upgrades will not be backported to Rtools42)?

4.2.2:

> sf::st_drivers()$name |> sort()
 [1] "AmigoCloud"     "AVCBin"         "AVCE00"         "BAG"           
 [5] "CAD"            "Carto"          "CSV"            "CSW"           
 [9] "DGN"            "DXF"            "EDIGEO"         "EEDA"          
[13] "Elasticsearch"  "ESRI Shapefile" "ESRIC"          "ESRIJSON"      
[17] "FlatGeobuf"     "Geoconcept"     "GeoJSON"        "GeoJSONSeq"    
[21] "GeoRSS"         "GML"            "GPKG"           "GPSBabel"      
[25] "GPX"            "HTTP"           "Idrisi"         "JML"           
[29] "JP2OpenJPEG"    "KML"            "LVBAG"          "MapInfo File"  
[33] "MapML"          "MBTiles"        "Memory"         "MVT"           
[37] "MySQL"          "netCDF"         "NGW"            "OAPIF"         
[41] "ODS"            "OGCAPI"         "OGR_GMT"        "OGR_PDS"       
[45] "OGR_SDTS"       "OGR_VRT"        "OpenFileGDB"    "OSM"           
[49] "PDF"            "PDS4"           "PGDUMP"         "PLSCENES"      
[53] "S57"            "Selafin"        "SQLite"         "SVG"           
[57] "SXF"            "TIGER"          "TopoJSON"       "UK .NTF"       
[61] "VDV"            "VFK"            "VICAR"          "WAsP"          
[65] "WFS"            "XLS"            "XLSX"

4.3 devel:

> sf::st_drivers()$name |> sort()
 [1] "AmigoCloud"     "AVCBin"         "AVCE00"         "BAG"           
 [5] "CAD"            "Carto"          "CSV"            "CSW"           
 [9] "DGN"            "DXF"            "EDIGEO"         "EEDA"          
[13] "Elasticsearch"  "ESRI Shapefile" "ESRIJSON"       "FlatGeobuf"    
[17] "Geoconcept"     "GeoJSON"        "GeoJSONSeq"     "GeoRSS"        
[21] "GML"            "GPKG"           "GPSBabel"       "GPX"           
[25] "HTTP"           "Idrisi"         "JML"            "JP2OpenJPEG"   
[29] "KML"            "LVBAG"          "MapInfo File"   "MapML"         
[33] "MBTiles"        "Memory"         "MSSQLSpatial"   "MVT"           
[37] "MySQL"          "netCDF"         "NGW"            "OAPIF"         
[41] "ODBC"           "ODS"            "OGCAPI"         "OGR_GMT"       
[45] "OGR_PDS"        "OGR_SDTS"       "OGR_VRT"        "OpenFileGDB"   
[49] "OSM"            "PCIDSK"         "PDF"            "PDS4"          
[53] "PGDUMP"         "PGeo"           "PLSCENES"       "PostgreSQL"    
[57] "S57"            "Selafin"        "SQLite"         "SVG"           
[61] "SXF"            "TIGER"          "TopoJSON"       "UK .NTF"       
[65] "VDV"            "VFK"            "VICAR"          "WAsP"          
[69] "WFS"            "XLS"            "XLSX"

For completeness in 4.3-devel:

> sf:::guess_driver_can_write('MSSQL:nc')
         mssql 
"MSSQLSpatial"
cmundy commented 1 year ago

Hi Roger,

I have installed the development version of r. All working ok, and can successfully read from mssql and write back to mssql using sf (very happy).

Many thanks

Craig

sessionInfo() R Under development (unstable) (2023-02-14 r83833 ucrt) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows 10 x64 (build 19045)

Matrix products: default

locale: [1] LC_COLLATE=English_Australia.utf8 [2] LC_CTYPE=English_Australia.utf8 [3] LC_MONETARY=English_Australia.utf8 [4] LC_NUMERIC=C [5] LC_TIME=English_Australia.utf8

time zone: Australia/Hobart tzcode source: internal

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

loaded via a namespace (and not attached): [1] compiler_4.3.0 DBI_1.1.3 tools_4.3.0

library(sf) Linking to GEOS 3.9.4, GDAL 3.6.0, PROJ 9.1.1; sf_use_s2() is TRUE

library(terra) terra 1.7.3

sf_layer <- st_read(dsn=dsn_srs, layer=lyr08) Reading layer kud2008_90_v4' from data source MSSQL:server=abtrack-db.its.utas.edu.au;database=AbTrack_Analysis;' using driver `MSSQLSpatial' Simple feature collection with 1823 features and 24 fields Geometry type: MULTIPOLYGON Dimension: XY Bounding box: xmin: 274826.3 ymin: 5169204 xmax: 613435 ymax: 5661249 Projected CRS: GDA94 / MGA zone 55

st_write(sf_layer, dsn=dsn_srs, layer = "writebysf_3", driver="MSSQLSpatial") Writing layer writebysf_3' to data source MSSQL:server=abtrack-db.its.utas.edu.au;database=AbTrack_Analysis;' using driver `MSSQLSpatial' Writing 1823 features with 24 fields and geometry type Multi Polygon.

From: Roger Bivand @.> Sent: Tuesday, February 14, 2023 11:34 PM To: r-spatial/sf @.> Cc: Craig Mundy @.>; Mention @.> Subject: Re: [r-spatial/sf] MSSQL Driver Failing in R 4.2.2 (Issue #2088)

Please do not bother with r-universe. Rather install R-devel for Windows (to be 4.3), and sf/terra built with Rtools43 for R 4.3 (coming in April). I see "MSSQLSpatial" there, which is why -lodbc* are already in Makevars.ucrt. — Reply to this email directly, https://github.com/r-spatial/sf/issues/2088#issuecomment-1429678351, or https://github.com/notifications/unsubscribe-auth/ACRUTC3RXN72UZTD7EC6GOLWXN3URANCNFSM6AAAAAAUGW5G34. You are receiving this because you were mentioned.Message ID: @.***>

This email is confidential, and is for the intended recipient only. Access, disclosure, copying, distribution, or reliance on any of it by anyone outside the intended recipient organisation is prohibited and may be a criminal offence. Please delete if obtained in error and email confirmation to the sender. The views expressed in this email are not necessarily the views of the University of Tasmania, unless clearly intended otherwise.

tomroh commented 1 year ago

Thank you as well!