Comments (10)
jimhester
commented on May 29, 2024
1
You probably need to compile the freetds driver with a higher TDS version then configure --with-tdsver=7.3 is what was used on my system and the drivers work fine.
from odbc.
harrismcgehee
commented on May 29, 2024
PDF gets chopped off.
repro
Harris McGehee
2/24/2017
# remove.packages("DBI")
# install.packages("DBI")
con <- DBI::dbConnect(odbc::odbc()
,DSN="MODBRESEARCH"
,Database="u_mcgehehs"
,Uid=yaml::yaml.load_file("~/odbc.yaml")$HMM$user
,Pwd=yaml::yaml.load_file("~/odbc.yaml")$HMM$password)DBI::dbGetInfo(con)## $dbname
## [1] "u_mcgehehs"
##
## $dbms.name
## [1] "Microsoft SQL Server"
##
## $db.version
## [1] "13.00.1601"
##
## $username
## [1] ""
##
## $host
## [1] ""
##
## $port
## [1] ""
##
## $sourcename
## [1] "MODBRESEARCH"
##
## $servername
## [1] "MODBRESEARCH"
##
## $drivername
## [1] "libtdsodbc.so"
##
## $odbc.version
## [1] "03.52"
##
## $driver.version
## [1] "00.95.0081"
##
## $odbcdriver.version
## [1] "03.50"
data <- DBI::dbWriteTable(con, "mtcars", mtcars, overwrite=TRUE)## Error in result_insert_dataframe(rs@ptr, values): nanodbc.cpp:1743: IM001: [unixODBC][Driver Manager]Driver does not support this function
data <- odbc::dbWriteTable(con, "mtcars", mtcars, overwrite=TRUE)## Error in result_insert_dataframe(rs@ptr, values): nanodbc.cpp:1743: IM001: [unixODBC][Driver Manager]Driver does not support this function
DBI::dbRemoveTable(con,"mtcars")## [1] TRUE
DBI::dbDisconnect(con)## [1] TRUE
devtools::session_info()## Session info --------------------------------------------------------------
## setting value
## version R version 3.3.2 (2016-10-31)
## system x86_64, linux-gnu
## ui X11
## language (EN)
## collate en_US.UTF-8
## tz America/New_York
## date 2017-02-28
## Packages ------------------------------------------------------------------
## package * version date source
## assertthat 0.1 2013-12-06 CRAN (R 3.3.2)
## backports 1.0.5 2017-01-18 CRAN (R 3.3.2)
## blob 1.0.0 2016-12-29 CRAN (R 3.3.2)
## DBI 0.5-1 2016-09-10 CRAN (R 3.3.2)
## devtools 1.12.0 2016-12-05 CRAN (R 3.3.2)
## digest 0.6.12 2017-01-27 CRAN (R 3.3.2)
## evaluate 0.10 2016-10-11 CRAN (R 3.3.2)
## htmltools 0.3.5 2016-03-21 CRAN (R 3.3.2)
## knitr 1.15.1 2016-11-22 CRAN (R 3.3.2)
## magrittr 1.5 2014-11-22 CRAN (R 3.3.2)
## memoise 1.0.0 2016-01-29 CRAN (R 3.3.2)
## odbc 1.0.1 2017-02-24 Github (rstats-db/odbc@7582276)
## Rcpp 0.12.9 2017-01-14 CRAN (R 3.3.2)
## rmarkdown 1.3 2016-12-21 CRAN (R 3.3.2)
## rprojroot 1.2 2017-01-16 CRAN (R 3.3.2)
## stringi 1.1.2 2016-10-01 CRAN (R 3.3.2)
## stringr 1.1.0 2016-08-19 CRAN (R 3.3.2)
## tibble 1.2 2016-08-26 CRAN (R 3.3.2)
## withr 1.0.2 2016-06-20 CRAN (R 3.3.2)
## yaml 2.1.14 2016-11-12 CRAN (R 3.3.2)
I am overwriting the TDS version in the local DNS to 7.0.
That is working because I wasn't able to connect at all without setting the TDS Version.
tsql -C ## Compile-time settings (established with the "configure" script)
## Version: freetds v0.95.81
## freetds.conf directory: /etc
## MS db-lib source compatibility: yes
## Sybase binary compatibility: yes
## Thread safety: yes
## iconv library: yes
## TDS version: 4.2
## iODBC: no
## unixodbc: yes
## SSPI "trusted" logins: no
## Kerberos: yes
## OpenSSL: no
## GnuTLS: yes
from odbc.
jimhester
commented on May 29, 2024
prepared statements are not supported by TDS version 4.2, so that won't work. You likely need to set TDS version to 7.0 in your odbc.ini file or connection arguments.
TDS_Version = 7.0
See http://www.freetds.org/userguide/odbcconnattr.htm and http://www.unixodbc.org/doc/FreeTDS.html.
from odbc.
harrismcgehee
commented on May 29, 2024
I am setting the version to 7.0 in my odbc.ini file. The reason I know is that when I do not specify it, I get this on dbConnect.
Error in odbc_connect(connection_string, timezone = timezone) : nanodbc.cpp:950: 08001: [unixODBC][FreeTDS][SQL Server]Unable to connect to data source
Any other ideas?
from odbc.
harrismcgehee
commented on May 29, 2024
I'll try that next and let you know. Thanks!
from odbc.
harrismcgehee
commented on May 29, 2024
tsql -C ## Compile-time settings (established with the "configure" script)
## Version: freetds v1.00.27
## freetds.conf directory: /usr/local/etc
## MS db-lib source compatibility: no
## Sybase binary compatibility: no
## Thread safety: yes
## iconv library: yes
## TDS version: 7.0
## iODBC: no
## unixodbc: yes
## SSPI "trusted" logins: no
## Kerberos: no
## OpenSSL: yes
## GnuTLS: no
## MARS: no
but I get the same response from the dbWriteTable.
data <- odbc::dbWriteTable(con, "mtcars", mtcars, overwrite=TRUE)## Error in result_insert_dataframe(rs@ptr, values): nanodbc.cpp:1743: IM001: [unixODBC][Driver Manager]Driver does not support this function
Any other diagnostics? Is there a way to report the TDS version being used from DBI or odbc?
from odbc.
jimhester
commented on May 29, 2024
Could you show the output of DBI::dbGetInfo(con) as well?
from odbc.
harrismcgehee
commented on May 29, 2024
Yes. I changed the sourcename to MODBRESEARCH#; where # is the TDS VERSION 7.# in the .odbc.ini file. 7.0-7.3 all connect, and read from the database, but produce the same
Error in result_insert_dataframe(rs@ptr, values) : nanodbc.cpp:1743: IM001: [unixODBC][Driver Manager]Driver does not support this function
$dbname
[1] "u_mcgehehs"
$dbms.name
[1] "Microsoft SQL Server"
$db.version
[1] "13.00.1601"
$username
[1] ""
$host
[1] ""
$port
[1] ""
$sourcename
[1] "MODBRESEARCH3"
$servername
[1] "MODBRESEARCH"
$drivername
[1] "libtdsodbc.so"
$odbc.version
[1] "03.52"
$driver.version
[1] "00.95.0081"
$odbcdriver.version
[1] "03.50"
from odbc.
jimhester
commented on May 29, 2024
So I have been able to reproduce this as well, looking into it further it seems freeTDS has not implemented SQLDescribeParam, which is used by nanodbc when binding results to be inserted. I opened an issue FreeTDS/freetds#104.
In the meantime I would suggest using RODBCDBI for table insertion as a workaround, the API should be nearly identical to odbc.
from odbc.
harrismcgehee
commented on May 29, 2024
Can confirm that this example now works.
from odbc.
Related Issues (20)
- Release odbc 1.4.2
- revisit Impala binary type HOT 1
- DBI::dBDisconnect() with ms-access HOT 1
- Mutex error only when attempting to read/write HOT 4
- Default to arbitrary database if not specified when creating temp tables on a warehouse (Snowflake / Databricks) HOT 6
- restore docs on `ODBCSYSINI` and `ODBCINI`
- id_field maybe should use names provided by DBI::Id instead of using order? HOT 1
- Variable type not bound when using prepared queries HOT 3
- SQL Server: dbListFields fails when name is an Id
- Add support for `dsn` argument to `databricks()` HOT 1
- Include synonyms in `odbcListObjects()`, `dbListTables()`, and the Connections Pane HOT 1
- Connection from RStudio to Snowflake throws error about odbcListObjects HOT 4
- consider moving `odbc_config` check for unixODBC to `dbConnect<OdbcDriver>`
- consider testing DBs on macOS
- type check bug with `databricks(httpPath)`
- set up CI/CD for databricks
- Writing to datetime2 column in SQL Server with odbc HOT 1
- `set_odbcsysini()` doesn't do anything
- Determine DATETIME2 precision based on parameter description
- GHA failure on macOS
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from odbc.