r-dbi / odbc Goto Github PK

View Code? Open in Web Editor NEW

379.0 30.0 106.0 14.33 MB

Connect to ODBC databases (using the DBI interface)

Home Page: https://odbc.r-dbi.org/

License: Other

R 23.71% C++ 74.52% Shell 0.04% C 0.76% Makefile 0.33% Dockerfile 0.11% Starlark 0.54%

r database odbc

odbc's Introduction

odbc

The goal of the odbc package is to provide a DBI-compliant interface to ODBC drivers. This makes it easy to connect databases such as SQL Server, Oracle, Databricks, and Snowflake.

The odbc package is an alternative to RODBC and RODBCDBI packages, and is typically much faster. See vignette("benchmarks") to learn more.

Overview

The odbc package is one piece of the R interface to databases with support for ODBC:

Support for a given DBMS is provided by an ODBC driver, which defines how to interact with that DBMS using the standardized syntax of ODBC and SQL. Drivers can be downloaded from the DBMS vendor or, if you’re a Posit customer, using the professional drivers.

Drivers are managed by a driver manager, which is responsible for configuring driver locations, and optionally named data sources that describe how to connect to a specific database. Windows is bundled with a driver manager, while MacOS and Linux require installation of unixODBC. Drivers often require some manual configuration; see vignette("setup") for details.

In the R interface, the DBI package provides a front-end while odbc implements a back-end to communicate with the driver manager. The odbc package is built on top of the nanodbc C++ library. To interface with DBMSs using R and odbc:

You might also use the dbplyr package to automatically generate SQL from your dplyr code.

Installation

Install the latest release of odbc from CRAN with the following code:

install.packages("odbc")

To get a bug fix or to use a feature from the development version, you can install the development version of odbc from GitHub:

# install.packages("pak")
pak::pak("r-dbi/odbc")

Usage

To use odbc, begin by creating a database connection, which might look something like this:

library(DBI)

con <- dbConnect(
  odbc::odbc(),
  driver = "SQL Server",
  server = "my-server",
  database = "my-database",
  uid = "my-username",
  pwd = rstudioapi::askForPassword("Database password")
)

(See vignette("setup") for examples of connecting to a variety of databases.)

dbListTables() is used for listing all existing tables in a database.

dbListTables(con)

dbReadTable() will read a full table into an R data.frame().

data <- dbReadTable(con, "flights")

dbWriteTable() will write an R data.frame() to an SQL table.

dbWriteTable(con, "iris", iris)

dbGetQuery() will submit a SQL query and fetch the results:

df <- dbGetQuery(
  con,
  "SELECT flight, tailnum, origin FROM flights ORDER BY origin"
)

It is also possible to submit the query and fetch separately with dbSendQuery() and dbFetch(). This allows you to use the n argument to dbFetch() to iterate over results that would otherwise be too large to fit in memory.

odbc's People

Contributors

Stargazers

Watchers

Forkers

jimhester jstudyvin therockstardba stat-r jonmcalder kevark javierluraschi jamesbtaylor calculus-ask fxcebx mnel jmcphers bquinart aryoda edgararuiz-zz cuulee dataning ruiyiz khotilov skyformat99 timknut jschelbert jameslinus saf703 colearendt lxq2537664558 krlmlr msummersgill renkun-ken mkim0710 wibeasley miraisolutions r2evans mmastand taehyokim iamsaini87 kevinushey blairj09 jd4ds tqdksx isaacarnault dpprdan vh-d ccf19881030 shrektan bechirzf sdcastillo magnuskjellman andrewsali davidchall detule gcvts ankitaguhaoakland s-fleck etiennebr davisvaughan sonali2105 thierryo takewiki edwardangelino mkirzon she3o 15914151189 but2ene meztez cstepper edgararuiz minghao2016 machow rnorberg anonopsbuilds bschamberger emgns04 slangersdk fool404 mgirlich ngandn18 jrnold mojo1ee bschwedler frank-gh korterling violethaze74 informaticacba kawillis alexkyllo odraio jannesred micahkimel alexkowa karim-mane sharon-wang aeccck schuemie exploratory-io rohankumardubey fh-afrachioni barracuda156 atheriel jamestiotio

odbc's Issues

Timezone handling

Issue Description and Expected Result

dbGetQuery() returns incorrect timestamps. When fetching date values from an Oracle database the returned dataset contains the wrong timezone information and the wrong time.

Database

Database version Oracle 12.01.0020, ODBC version 03.80.0000, Driver Version 11.02.0003, ODBC driver version 03.52

Reproducible Example

Sys.time() returns "2017-02-08 11:21:46 CET" while the fetched recordset is "2017-02-08 12:21:46 CET". Setting Sys.setenv(TZ="UTC") makes no difference despite all values are shown in UTC (Sys.time() returns
"2017-02-08 10:27:22 UTC", the sysdate select returns "2017-02-08 11:27:22 UTC").

> con <- dbConnect(odbc::odbc(), .connection_string="Driver={Oracle in OraClient11g_home1_32bit};Dbq=xxx;Uid=yyy;Pwd=zzz;")
> dbGetQuery(con, "select sessiontimezone from dual UNION ALL select dbtimezone from dual")
  SESSIONTIMEZONE
1          +01:00
2          +01:00
> Sys.time()
[1] "2017-02-08 11:21:46 CET"
> dbGetQuery(con, "select sysdate from dual")[1,1]
[1] "2017-02-08 12:21:46 CET"
dbDisconnect(con)

Session Info

> devtools::session_info()
Session info -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 setting  value                       
 version  R version 3.3.1 (2016-06-21)
 system   i386, mingw32               
 ui       Rgui                        
 language (EN)                        
 collate  German_Austria.1252         
 tz       UTC                         
 date     2017-02-08                  

Packages -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 package    * version date       source        
 assertthat   0.1     2013-12-06 CRAN (R 3.3.1)
 blob         1.0.0   2016-12-29 CRAN (R 3.3.2)
 DBI          0.5-1   2016-09-10 CRAN (R 3.3.1)
 devtools   * 1.12.0  2016-06-24 CRAN (R 3.3.1)
 digest       0.6.11  2017-01-03 CRAN (R 3.3.2)
 memoise      1.0.0   2016-01-29 CRAN (R 3.3.1)
 odbc       * 1.0.1   2017-02-07 CRAN (R 3.3.2)
 Rcpp         0.12.8  2016-11-17 CRAN (R 3.3.2)
 rstudioapi * 0.6     2016-06-27 CRAN (R 3.3.1)
 tibble       1.2     2016-08-26 CRAN (R 3.3.1)
 withr      * 1.0.2   2016-06-20 CRAN (R 3.3.1)

return datetime added 8hours

con %>% dbSendQuery("select ordernum,addtime,to_char(addtime, 'yyyy-mm-dd hh24:mi:ss') as addtime2 from TEST.V_HPME_TBL_WP_ORDER where ordernum='399937536016100321515559'") %>% dbFetch()
what i got is addtime = addtime2 + lubridate::hours(8), is there a way to handle timezone?

Encoding problem with dbWriteTable on Microsoft SQL Server 2012 with R on Windows7 client

Issue Description and Expected Result

When I write a latin1 string into a nvarchar column of a table the text written into the database table
is wrong:

> dbWriteTable(con, "encoding_test", data, append = TRUE)
> data2 <- dbReadTable(con, "encoding_test")
> data
    col1
1 façile
2   äöüß
3    aaa
> data2
      col1
1  faÃ§ile
2 Ã€Ã¶ÃŒÃ�
3      aaa
>

Directly selecting the data via SQL in SQL Server Management Studio results in the same scrambled text:

SELECT * FROM [testdb].[dbo].[encoding_test]

col1
faÃ§ile
Ã€Ã¶ÃŒÃ�
aaa

Database

Microsoft SQL Server Enterprise (64-bit)
Version 11.0.6579.0
Server Collation: Latin1_General_CI_AS

Reproducible Example

library(DBI)
library(odbc)

con <- dbConnect(odbc::odbc(), driver="{SQL Server Native Client 11.0}", server="dbserver1.domain.com\\default,1234", Trusted_Connection = "yes", database = "testdb")
data <- data.frame(col1=c("fa\xE7ile", "äöüß", "aaa"), stringsAsFactors = FALSE) # latin1
Encoding(data$col1)  # must be: [1] "latin1"  "latin1"  "unknown"
dbGetQuery(con, "CREATE TABLE [dbo].[encoding_test]([col1] [nvarchar](255) NULL)")
dbWriteTable(con, "encoding_test", data, append = TRUE)
data2 <- dbReadTable(con, "encoding_test")
dbDisconnect(con)
data
data2

R session info

> sessionInfo()
R version 3.3.2 (2016-10-31)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

locale:
[1] LC_COLLATE=German_Germany.1252  LC_CTYPE=German_Germany.1252    LC_MONETARY=German_Germany.1252
[4] LC_NUMERIC=C                    LC_TIME=German_Germany.1252    

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

other attached packages:
[1] odbc_1.0.1.9000 DBI_0.6-1      

loaded via a namespace (and not attached):
[1] bit_1.1-12     assertthat_0.1 hms_0.3        tools_3.3.2    tibble_1.2     Rcpp_0.12.10.1 bit64_0.9-5    blob_1.0.0

Other observations

Using a varchar column does not help
Let dbWriteTable create the target table (by omitting the append parameter) does not help
Converting the latin1 string into UTF-8 (via data$col1 <- iconv(data$col1, from="latin1", to="UTF-8") does not help and writes the exactly same (wrong) bytes into the database

Installation fails on macOS El Capitan, R 3.3.1

I'd like to start experimenting with this project, but I'm having some difficulty installing odbc from github on macOS. It appears to be some C++/Rcpp configuration issue on my system, but I can't quite figure out what to update.

When I run:

devtools::install_github("rstats-db/odbc")

* installing *source* package ‘odbc’ ...
Found pkg-config cflags and libs!
Using PKG_CFLAGS=-I/usr/local/include
Using PKG_LIBS=-L/usr/local/lib  -liodbc -liodbcinst
** libs
clang++ -std=c++11 -I/Library/Frameworks/R.framework/Resources/include -DNDEBUG  -I/usr/local/include -I/usr/local/include/freetype2 -I/opt/X11/include -I"/Users/joranelias/RLib/Rcpp/include" -I"/Users/joranelias/RLib/BH/include"  -I. -std=c++14 -DNANODBC_USE_BOOST_CONVERT -DNANODBC_ODBC_VERSION=SQL_OV_ODBC3  -fPIC  -Wall -mtune=core2 -g -O2 -c RcppExports.cpp -o RcppExports.o
error: invalid value 'c++14' in '-std=c++14'
make: *** [RcppExports.o] Error 1
ERROR: compilation failed for package ‘odbc’
* removing ‘/Users/joranelias/RLib/odbc’
Error: Command failed (1)

I get that error, which I presume means I need to update something on my system with regards to Rcpp, perhaps, but I'm just not sure what.

I have Rcpp 0.12.7 and BH 1.60.0-2. Do I need to recompile them, or something else from source?

Session Info

> devtools::session_info()
#> R version 3.3.1 (2016-06-21)
Platform: x86_64-apple-darwin13.4.0 (64-bit)
Running under: OS X 10.11.5 (El Capitan)

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

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

loaded via a namespace (and not attached):
[1] httr_1.2.1      R6_2.1.2        tools_3.3.1     withr_1.0.1     curl_1.1        memoise_1.0.0   git2r_0.15.0   
[8] digest_0.6.9    devtools_1.12.0

Problem retrieving bigint value from SQL Server

Problem retrieving bigint values

I was bit by a bug when testing out the odbc-package. I couldn't understand why my joins in dplyr didn't work as expected. After a while I found out that the type casting of bigint values didn't work as expected. Below is an example from our in house database, and a comparison with RSQLServer.

Database

SQL Server 2012

Non-reproducible example

library(odbc)
library(DBI)

# Using odbc
dbConnect(odbc::odbc(),"rt; UID=xxxxx; PWD=yyyyy") -> db
dbGetQuery(db,"USE RT; SELECT TOP 1 DelytaID, CAST(DelytaID AS char) AS DelytaCAST FROM dbo.vy_Delyteregister")
# Output
#     DelytaID  DelytaCAST
# 1 -603275544 81001103080                   

# Using RSQLServer
dbConnect(RSQLServer::SQLServer(),"RT") -> db
dbGetQuery(db,"USE RT; SELECT TOP 1 DelytaID, CAST(DelytaID AS char) AS DelytaCAST FROM dbo.vy_Delyteregister")
# Output
#      DelytaID  DelytaCAST
# 1 81001103080 81001103080

Session Info

devtools::session_info()
#Session info #-------------------------------------------------------------------------------------------------
#setting  value                       
# version  R version 3.3.2 (2016-10-31)
# system   x86_64, darwin13.4.0        
# ui       RStudio (1.0.136)           
# language (EN)                        
# collate  sv_SE.UTF-8                 
# tz       Europe/Stockholm            
# date     2017-03-08    

# odbc         1.0.1      2017-02-17 Github (rstats-db/odbc@8abb2ab) 
# RSQLServer   0.2.099    2016-09-28 Github (imanuelcostigan/RSQLServer@0b16383)

Unable to CREATE TABLE through dbGetQuery(), with Spark 1.6.2

Issue Description and Expected Result

We're unable to run CREATE TABLE commands through the dbGetQuery() call on our Spark 1.6.2 connection. This only seems to be affecting Spark 1.6.2 connections.

Database

Spark 1.6.2

Reproducible Example

This error is produced when running the example code below:

Error in new_result(connection@ptr, statement) : 
  <SQL> 'CREATE TABLE sample(id int, other int)'
  nanodbc.cpp:1587: HY000: [Simba][Hardy] (35) Error from server: error code: '0' error message: 'ExecuteStatement finished with operation state: ERROR_STATE'.

Example R Chunk:

> con <- dbConnect(odbc::odbc(), .connection_string = "Driver={Spark Magnitude};Host=localhost;Port=10000;SparkServerType=3;")
> dbListTables(con)
character(0)
> dbGetQuery(con, "CREATE TABLE sample(id int, other int)")

Session Info

devtools::session_info()
R version 3.3.2 (2016-10-31)
Platform: x86_64-apple-darwin13.4.0 (64-bit)
Running under: macOS Sierra 10.12.3

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

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

other attached packages:
[1] DBI_0.6             sparklyr_0.5.3-9002

loaded via a namespace (and not attached):
 [1] Rcpp_0.12.10         rstudioapi_0.6       magrittr_1.5         odbc_1.0.1.9000     
 [5] rappdirs_0.3.1       xtable_1.8-2         R6_2.2.0             rlang_0.0.0.9002    
 [9] blob_1.0.0           httr_1.2.1           dplyr_0.5.0.9000     tools_3.3.2         
[13] parallel_3.3.2       config_0.2           withr_1.0.2          htmltools_0.3.5     
[17] yaml_2.1.14          assertthat_0.1       rprojroot_1.2        digest_0.6.12       
[21] tibble_1.2           shiny_1.0.0          testPackage002_0.1.0 base64enc_0.1-3     
[25] glue_0.0.0.9000      mime_0.5             backports_1.0.5      jsonlite_1.3        
[29] httpuv_1.3.3

dbBind on SQL Server

Issue Description and Expected Result

parameterized queries using dbBind() and dbFetch() produce nanodbc error:

Error in result_fetch(res@ptr, n, ...) : 
  nanodbc.cpp:2517: 24000: [Microsoft][ODBC Driver 13 for SQL Server]Invalid cursor state

Database

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)

Reproducible Example

Example (from #19, but using SQL Server):

library(DBI)
con <- dbConnect(odbc::odbc(), "sql_server_test")
dbWriteTable(con, "iris", iris)
iris_result <- dbSendQuery(con, "SELECT * FROM iris WHERE 'Petal.Width' > ?")
dbBind(iris_result, list(2.3))
dbFetch(iris_result)
dbBind(iris_result, list(2.4))
dbFetch(iris_result)
dbClearResult(iris_result)
dbDisconnect(con)

Session Info

devtools::session_info()
Session info ---------------------------------------------------------------------------------------------------
 setting  value                       
 version  R version 3.3.1 (2016-06-21)
 system   x86_64, mingw32             
 ui       RStudio (1.0.136)           
 language (EN)                        
 collate  English_United States.1252  
 tz       America/Los_Angeles         
 date     2017-02-16                  

Packages -------------------------------------------------------------------------------------------------------
 package    * version    date       source                      
 assertthat   0.1        2013-12-06 CRAN (R 3.3.2)              
 blob         1.0.0.9000 2017-01-30 Github (hadley/blob@389065e)
 DBI        * 0.5-1      2016-09-10 CRAN (R 3.3.2)              
 devtools     1.12.0     2016-06-24 CRAN (R 3.3.2)              
 digest       0.6.12     2017-01-27 CRAN (R 3.3.2)              
 memoise      1.0.0      2016-01-29 CRAN (R 3.3.2)              
 odbc         1.0.1      2017-02-07 CRAN (R 3.3.1)              
 Rcpp         0.12.9     2017-01-14 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)

Feature request: Support for ODBC bulk insert and update operations

I know that the nanodbc lib supports bulk inserts and updates via batch sizes of n rows that are send to the server via one (or a minimum number of) server roundtrip(s):

https://github.com/lexicalunit/nanodbc/issues/242

Could you support this in the odbc package too please?

A simple test with a prepared statement and parameter binding indicates that only one row can be sent but not a whole batch of rows at once (tested with MySQL Version: 5.5.53-MariaDB and an Ubuntu client with unixODBC + DBI_0.5-1 + odbc_1.0.1):

library(DBI)
library(odbc)

con <- dbConnect(odbc::odbc(), dsn="testDB", uid = "user", pwd = "password")

dbWriteTable(con, "iris", iris)      # create and populate a table

# Test bulk inserts
insert <- dbSendQuery(con, "INSERT INTO iris(`Sepal.Length`, `Sepal.Width`, `Petal.Length`, `Petal.Width`, `Species`) VALUES (?, ?, ?, ?, ?)")
dbBind(insert, list(1, 2, 3, 4, "blue"))   # works
dbBind(insert, list(1:2, 2:3, 3:4, 4:5, c("blue", "red")))  # Error: `params` elements can only be of length 1
dbClearResult(insert)

dbDisconnect(con)

Bulk inserts and updates are the last missing link for e. g. Microsoft SQL Server since Microsoft does not provide that currently for R even though they have integrated R into the MS SQL Server 2016.

Thanks for this great new package!

Document `get_data_type()` generic and how to define a new method for it.

SQLite backend does not handle quoted strings with internal dots properly

Should return with.dot but does not. This is not a issue with the quoting, the query string is identical to that produced by RSQLite.

con <- OdbconnectConnection("SQLite")
with_dot <- dbQuoteIdentifier(con, "with.dot")
query <- paste0("SELECT 1 as ", with_dot)
dbGetQuery(con, query)
#>   dot
#> 1   1

ODBC getting datetime from SQL arriving in wrong time zone

Issue Description and Expected Result

Data imported into R from MS SQL Server shows local time zone. But wrong time. The time is off by the offset between UTC and local time zone.

Database

MS SQL Server 2008 R2
Client computer MS Windows 7 latest patches
odbc version 1.0.1

R.Version()
$platform
[1] "x86_64-w64-mingw32"
$arch
[1] "x86_64"
$os
[1] "mingw32"
$system
[1] "x86_64, mingw32"
$status
[1] ""
$major
[1] "3"
$minor
[1] "3.3"
$year
[1] "2017"
$month
[1] "03"
$day
[1] "06"
$svn rev
[1] "72310"
$language
[1] "R"
$version.string
[1] "R version 3.3.3 (2017-03-06)"
$nickname
[1] "Another Canoe"

R Studio 1.0.143.

Reproducible Example

library(tidyverse)
library(lubridate)

Sys.timezone()
[1] "America/New_York"

con <- DBI::dbConnect(odbc::odbc(),
driver = "{SQL Server Native Client 11.0}",
server = "myserver",
database = "mydatabase",
Trusted_Connection = "yes")

orders <-
con %>%
dbReadTable("my_table") %>%
head

orders$order_dt
[1] "2003-06-18 05:05:40 EDT" "2003-06-18 05:16:45 EDT"
[3] "2003-06-18 05:20:46 EDT" "2003-06-18 05:30:58 EDT"
[5] "2003-06-18 05:27:38 EDT" "2003-06-18 05:37:06 EDT"

However, these times are 4-5 hours earlier in the day then when SSMS reports the same times depending on the time of year.

I took a look at some of the code. (I;m new to R but no expert.) But, it looks like odbc may be expecting to get UTC data. However, my SQL server is giving you "America/NewYork". You are recording the value as UTC. Then when R is displaying the value, it is showing me the values in local time zone. (4-5 hours earlier)

I've tried to figure out if there is a way to have the odbc driver show time zones so that R can interpret my data more accurately. No luck I've tried

orders$order_dt_only <- with_tz(orders$order_dt_only, tz = "UTC")
But now the time is in UTC. But, the event actually occurs local time. And, I'd prefer not to re-load every variable that happens to be a data time.

Dates and time are important for the work I'm doing. If there is a workaround, that would be very helpful. Or a tidyvers type suggestion that would allow me to work with this data in an easier way.

Thanks for any insights.

Cannot add bindings to a lock environment

Latest devel version of package is giving following error when attempting to connect:

Error in assign(mname, def, where) : cannot add bindings to a locked environment

Here's the full 'Traceback':

7. assign(mname, def, where)
6. assignClassDef(Class, classDef, where)
5. setClass(info$dbms.name, contains = "OdbcConnection", package = "odbc")
4. OdbcConnection(dsn = dsn, ..., timezone = timezone, driver = driver, server = server, database = database, uid = uid, pwd = pwd, .connection_string = .connection_string)
3. .local(drv, ...)
2. dbConnect(odbc(), Driver = "Hive Driver", host = "ec2-52-35-222-37.us-west-2.compute.amazonaws.com", Schema = "default", UID = "rstudio", PWD = "rstudio", port = 10000)
1. dbConnect(odbc(), Driver = "Hive Driver", host = "ec2-52-35-222-37.us-west-2.compute.amazonaws.com", Schema = "default", UID = "rstudio", PWD = "rstudio", port = 10000)

I tried it with the CRAN version and #39 version of the package, and both worked fine.

Turn on DBItests for transactions

MySQL truncates tables that contain Inf values

con <- OdbconnectConnection("MySQL")
tbl_in <- data.frame(a = c(NA, seq(1, 3, by = 0.5), NA, -Inf, Inf, NaN),
  id = 1:10)
dbWriteTable(con, "test", tbl_in)
tbl_out <- dbReadTable(con, "test")
expect_equal(tbl_in$a, tbl_out$a[order(tbl_out$id)])
#> Error: tbl_in$a not equal to tbl_out$a[order(tbl_out$id)].
#> Lengths differ: 10 vs 7

Handle schemas better

Listing, querying dbListTables(), dbReadTable()

SQL Server via FreeTDS: [unixODBC][Driver Manager]Driver does not support this function

Issue Description and Expected Result

data <- DBI::dbWriteTable(con, "mtcars", mtcars, overwrite=TRUE) returns:
## Error in result_insert_dataframe(rs@ptr, values): nanodbc.cpp:1743: IM001: [unixODBC][Driver Manager]Driver does not support this function

Expected: TRUE

Database

Microsoft SQL Server 13.00.1601
using FreeTDS Version: freetds v0.95.81

Reproducible Example

document.pdf

Let me know what else would help!

dbGetInfo does not return Port number and Server Name does not match for MSSQL

Issue Description and Expected Result

dbGetInfo does not return Port number and Server Name does not match for MSSQL

Database

SQL Server 2012

Reproducible Example

odbcSQLSeverCon <- dbConnect(odbc(),
                             Driver              = "SQL Server Driver",
                             Server              = "sol-eng-sqlserv.cihykudhzbgw.us-west-2.rds.amazonaws.com",
                             Database            = "airontime",
                             UID                 = "rstudioadmin",
                             PWD                 = "[PRIVATE]",
                             Port                = 1433)


DBI::dbGetInfo(odbcSQLSeverCon)

Results in this:

$dbname
[1] "airontime"

$dbms.name
[1] "Microsoft SQL Server"

$db.version
[1] "12.00.4422"

$username
[1] "rstudioadmin"

$host
[1] ""

$port
[1] ""

$sourcename
[1] ""

$servername
[1] "EC2AMAZ-O5QKKM8"

$drivername
[1] "RStudio SQL Server ODBC Driver"

$odbc.version
[1] "03.52"

$driver.version
[1] "1.3.8.1031"

$odbcdriver.version
[1] "03.80"

$supports.transactions
[1] TRUE

attr(,"class")
[1] "Microsoft SQL Server" "driver_info"          "list"

Even though I'm passing port number it comes back empty in dbGetInfo

Issue: ODBC assumes Day_Time is UTC and converts to local machine Timezone

Hi,

I am unable to offer a reproducible example but what happens is when I use the odbc driver to query SQL-Server 2014 and the column has Day_Time odbc automatically assumes the Day_Time is in UTC and will convert into my local TimeZone which is AST.

If I use RODBC in the exact same pull this does not occur.

R version 3.3.3 (2017-03-06)
Platform: x86_64-apple-darwin16.4.0 (64-bit)
Running under: macOS Sierra 10.12.3

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

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

other attached packages:
 [1] RODBC_1.3-14    odbc_1.0.1      lubridate_1.6.0 ToolBox_1.05    dplyr_0.5.0     purrr_0.2.2     readr_1.0.0     tidyr_0.6.1    
 [9] tibble_1.2      ggplot2_2.2.1   tidyverse_1.1.1

loaded via a namespace (and not attached):
 [1] Rcpp_0.12.9         plyr_1.8.4          forcats_0.2.0       tools_3.3.3         jsonlite_1.3        gtable_0.2.0       
 [7] nlme_3.1-131        lattice_0.20-34     psych_1.6.12        DBI_0.6             parallel_3.3.3      haven_1.0.0        
[13] stringr_1.2.0       httr_1.2.1          xml2_1.1.1          hms_0.3             grid_3.3.3          R6_2.2.0           
[19] readxl_0.1.1        foreign_0.8-67      reshape2_1.4.2      modelr_0.1.0        blob_1.0.0.9000     magrittr_1.5       
[25] scales_0.4.1        assertthat_0.1      mnormt_1.5-5        rvest_0.3.2         colorspace_1.3-2    stringi_1.1.2      
[31] lazyeval_0.2.0.9000 munsell_0.4.3       broom_0.4.2

example_df is unmodified from the query result. AcceptedDateTime is received from SQL-Server.
AcceptedDateTime3 is the value that is stored in the SQL-Server, which is a value in Timezone "America/Los_Angles".

example_df %>% 
  mutate(AcceptedDateTime2 = with_tz(AcceptedDateTime,tzone = "UTC"),
         AcceptedDateTime3 = force_tz(AcceptedDateTime2 , tzone = "America/Los_Angeles")
  )

# A tibble: 1 × 4
     AcceptedDateTime        ID   AcceptedDateTime2   AcceptedDateTime3
               <dttm>     <int>              <dttm>              <dttm>
1 2017-03-18 14:00:39 350468174 2017-03-18 18:00:39 2017-03-18 18:00:39

Installation instructions need note about odbcinst.ini

So that you know after you've installed the driver you need to edit a file

install error on centos 6.5

unixODBC already installed

> devtools::install_github('rstats-db/odbc')
Downloading GitHub repo rstats-db/odbc@master
from URL https://api.github.com/repos/rstats-db/odbc/zipball/master
Installing odbc
'/usr/lib64/R/bin/R' --no-site-file --no-environ --no-save --no-restore --quiet CMD INSTALL  \
  '/tmp/Rtmpvlt8bV/devtools7242ba879f/rstats-db-odbc-cd91c41' --library='/home/bb/R/library' --install-tests 

* installing *source* package ‘odbc’ ...
Package odbc was not found in the pkg-config search path.
Perhaps you should add the directory containing `odbc.pc'
to the PKG_CONFIG_PATH environment variable
No package 'odbc' found
Package odbc was not found in the pkg-config search path.
Perhaps you should add the directory containing `odbc.pc'
to the PKG_CONFIG_PATH environment variable
No package 'odbc' found
Using PKG_CFLAGS=
Using PKG_LIBS=-lodbc
** libs
g++ -m64 -std=c++0x -I/usr/include/R -DNDEBUG  -I/usr/local/include -I"/usr/lib64/R/library/Rcpp/include" -I"/home/bb/R/library/BH/include"  -I. -DNANODBC_USE_BOOST_CONVERT -DNANODBC_ODBC_VERSION=SQL_OV_ODBC3 -fpic  -fpic  -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -c RcppExports.cpp -o RcppExports.o
In file included from odbc_result.h:3,
                 from odbc_types.h:5,
                 from RcppExports.cpp:4:
nanodbc.h:229: error: expected ‘;’ before ‘noexcept’
nanodbc.h:238: error: expected ‘;’ before ‘noexcept’
nanodbc.h:247: error: expected ‘;’ before ‘noexcept’
nanodbc.h:256: error: expected ‘;’ before ‘noexcept’
nanodbc.h:269: error: expected ‘;’ before ‘noexcept’
nanodbc.h:270: error: expected ‘;’ before ‘noexcept’
nanodbc.h:271: error: expected ‘;’ before ‘noexcept’
nanodbc.h:262: error: looser throw specifier for ‘virtual nanodbc::database_error::~database_error()’
/usr/lib/gcc/x86_64-redhat-linux/4.4.7/../../../../include/c++/4.4.7/stdexcept:117: error:   overriding ‘virtual std::runtime_error::~runtime_error() throw ()’
nanodbc.h:361: error: expected ‘;’ before ‘noexcept’
nanodbc.h:368: error: expected ‘;’ before ‘noexcept’
nanodbc.h:371: error: expected ‘;’ before ‘noexcept’
nanodbc.h:378: error: expected ‘;’ before ‘noexcept’
nanodbc.h:448: error: expected ‘;’ before ‘noexcept’
nanodbc.h:455: error: expected ‘;’ before ‘noexcept’
nanodbc.h:459: error: expected ‘;’ before ‘noexcept’
nanodbc.h:684: error: expected ‘;’ before ‘noexcept’
nanodbc.h:949: error: expected ‘;’ before ‘noexcept’
nanodbc.h:956: error: expected ‘;’ before ‘noexcept’
nanodbc.h:983: error: expected ‘;’ before ‘noexcept’
nanodbc.h:1134: error: expected ‘;’ before ‘noexcept’
nanodbc.h:1141: error: expected ‘;’ before ‘noexcept’
nanodbc.h:1148: error: expected ‘;’ before ‘noexcept’
nanodbc.h:1154: error: expected ‘;’ before ‘noexcept’
nanodbc.h:1161: error: expected ‘;’ before ‘noexcept’
nanodbc.h:1214: error: expected ‘;’ before ‘noexcept’
nanodbc.h:1367: error: only declarations of constructors can be ‘explicit’
In file included from odbc_result.h:7,
                 from odbc_types.h:5,
                 from RcppExports.cpp:4:
odbc_connection.h: In constructor ‘odbc::odbc_connection::odbc_connection(std::string)’:
odbc_connection.h:13: error: ‘nullptr’ was not declared in this scope
odbc_connection.h: In member function ‘void odbc::odbc_connection::rollback() const’:
odbc_connection.h:38: error: ‘class nanodbc::transaction’ has no member named ‘rollback’
odbc_connection.h: In member function ‘bool odbc::odbc_connection::has_active_result() const’:
odbc_connection.h:41: error: ‘nullptr’ was not declared in this scope
In file included from odbc_types.h:5,
                 from RcppExports.cpp:4:
odbc_result.h: At global scope:
odbc_result.h:10: error: expected initializer before ‘<’ token
odbc_result.h: In member function ‘void odbc::odbc_result::execute()’:
odbc_result.h:36: error: ‘nullptr’ was not declared in this scope
odbc_result.h: In member function ‘Rcpp::DataFrame odbc::odbc_result::fetch(int)’:
odbc_result.h:83: error: ‘nullptr’ was not declared in this scope
odbc_result.h: In destructor ‘odbc::odbc_result::~odbc_result()’:
odbc_result.h:103: error: ‘nullptr’ was not declared in this scope
make: *** [RcppExports.o] Error 1
ERROR: compilation failed for package ‘odbc’
* removing ‘/home/bb/R/library/odbc’
Error: Command failed (1)

Unable to connect to MSSQL on Ubuntu

Issue Description and Expected Result

Unable to create connection to MSSQL database from ubuntu machine. I have unixODBC and FreeTDS and have odbc.ini and odbcinst.ini configured as per the readme. Any help would be massively appreciated.

For reproducibility I'm using the rocker-org docker image ropensci although I also get the same result on a standard Ubuntu config (14.04).

Database

SQL Server 2014 (12.0.5000.0)

Example

> library(DBI)
> con <- dbConnect(odbc::odbc(),
+                  driver = "FreeTDS",
+                  database = "xxxxx",
+                  uid = "xxxxxxxxxxxx",
+                  pwd = "xxxxxxxxxxxxxxxx",
+                  host = "warehousesql.xxxxxxxxxxxxx.com")
Error in odbc_connect(connection_string) : 
  nanodbc.cpp:950: 08001: [unixODBC][FreeTDS][SQL Server]Unable to connect to data source

> library(DBI)
> con <- dbConnect(odbc::odbc(), "warehouse")
Error in odbc_connect(connection_string) : 
  nanodbc.cpp:950: 08001: [unixODBC][FreeTDS][SQL Server]Unable to connect to data source

Session info ------------------------------------------------------------------------------------------------------------------------------------
 setting  value                       
 version  R version 3.3.2 (2016-10-31)
 system   x86_64, linux-gnu           
 ui       RStudio (1.0.136)           
 language (EN)                        
 collate  en_US.UTF-8                 
 tz       <NA>                        
 date     2017-01-11                  

Packages ----------------------------------------------------------------------------------------------------------------------------------------
 package    * version    date       source                         
 curl         2.3        2016-11-24 CRAN (R 3.3.2)                 
 DBI        * 0.5-14     2017-01-11 Github (rstats-db/DBI@8afe845) 
 devtools     1.12.0     2016-12-05 CRAN (R 3.3.2)                 
 digest       0.6.11     2017-01-03 CRAN (R 3.3.2)                 
 git2r        0.18.0     2017-01-01 CRAN (R 3.3.2)                 
 httr         1.2.1      2016-07-03 CRAN (R 3.3.2)                 
 memoise      1.0.0      2016-01-29 CRAN (R 3.3.2)                 
 odbc       * 0.0.0.9000 2017-01-11 Github (rstats-db/odbc@2d187b3)
 R6           2.2.0      2016-10-05 CRAN (R 3.3.2)                 
 Rcpp         0.12.8     2016-11-17 cran (@0.12.8)                 
 rstudioapi   0.6        2016-06-27 CRAN (R 3.3.2)                 
 withr        1.0.2      2016-06-20 CRAN (R 3.3.2)

Config Files:

/etc$ cat odbc.ini
[warehouse]
Driver 		= FreeTDS
Server 		= warehousesql.xxxxxxxxx.com
UserName 	= xxxxxxxxxxxxx
Password	= xxxxxxxxxxxxx
Database	= xxxxxxxxx

/etc$ cat odbcinst.ini
[FreeTDS]
Description = v0.91 with protocol v7.2
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

dbWriteTable() doesn't escape variable names

library(DBI)
con <- dbConnect(odbc::odbc(),
  driver = "SQLite Driver",
  database = ":memory:"
)
dbWriteTable(con, "iris", iris[1, ])
dbReadTable(con, "iris")
#>   Length Width Length Width Species
#> 1    5.1   3.5    1.4   0.2  setosa

odbcListDrivers fails

odbc::odbcListDrivers()
#> Error in matrix(unlist(value, recursive = FALSE, use.names = FALSE), nrow = nr, : length of 'dimnames' [2] not equal to array extent

Looks like it fails when odbc:::list_drivers_() is empty

odbc doesn't build on travis language:r

Issue Description and Expected Result

When I try to build include odbc in my script, it fails to compile

Database

N/A

Reproducible Example

The travis build log is at: https://travis-ci.org/AlexAxthelm/DualCredit_AP_dash/builds/221429471

`.travis.yml`:

language: R
cache: packages
addons:
  apt:
    sources:
    - ubuntu-toolchain-r-test
    packages:
    - unixodbc
    - unixodbc-dev
    - tdsodbc
    - libsqliteodbc

script:
- Rscript -e 'rmarkdown::render(input = "ap_dc.Rmd", output_file = "index.html")'

`DESCRIPTION`:

Imports:
  knitr,
  DT,
  plotly,
  tidyverse,
  DBI,
  htmltools,
  broom,
  openxlsx,
  RSQLite,
  rmarkdown,
  devtools,
  secure,
  PKI,
  odbc
Remotes:
  s-u/PKI,
  hadley/secure,
  rstats-db/odbc

Session Info

$ Rscript -e 'sessionInfo()'
R version 3.3.3 (2017-03-06)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu precise (12.04.5 LTS)
locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C              
 [3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8    
 [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8   
 [7] LC_PAPER=en_US.UTF-8       LC_NAME=C                 
 [9] LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       
attached base packages:
[1] stats     graphics  grDevices utils     datasets  base

[Oracle][ODBC]Invalid SQL data type <-25>

> res <- dbSendQuery(con, "SELECT * FROM test.V_HPME_TBL_WP_ORDER where orderid=8429157")
> x = dbFetch(res)
Error in eval(substitute(expr), envir, enclos) : ODBC error
state: HY004
native error code: 0
message: [Oracle][ODBC]Invalid SQL data type <-25>.
> dbClearResult(res)
[1] TRUE

get these error, Invalid SQL data type
and this is the FIELDS info

ORDERID NUMBER
ORDERNUM    VARCHAR2(50)
WID VARCHAR2(50)
ORDERTYPE   INTEGER
BUYDIRECTION    INTEGER
PRODUCTID   NUMBER
BUYPRICE    NUMBER(11,3)
SELLPRICE   NUMBER(11,3)
BUYMONEY    NUMBER(11,3)
FEE NUMBER(11,3)
COUNT   INTEGER
TOPLIMIT    NUMBER(2,2)
BOTTOMLIMIT NUMBER(2,2)
PLAMOUNT    NUMBER(11,2)
ADDTIME DATE
SELLTIME    DATE
COUPONFLAG  INTEGER
BANKID  NUMBER
TRANLOGNO   VARCHAR2(50)
CHANNEL NUMBER
UPDATEDTIME DATE
UPDATEDBY   VARCHAR2(50)
OPINION VARCHAR2(500)
TOPPRICE    NUMBER(11,3)
BOTTOMPRICE NUMBER(11,3)
DEFICITPRICE    NUMBER(11,3)
MERSEQID    VARCHAR2(16)
MERDATE VARCHAR2(8)
SFJSTAT VARCHAR2(10)
BELONGSID   VARCHAR2(50)
USERID  NUMBER(11)
APPROVALTYPE    NUMBER(4)
BUYNUM  VARCHAR(100)

and the data from the SQL

"ORDERID","ORDERNUM","WID","ORDERTYPE","BUYDIRECTION","PRODUCTID","BUYPRICE","SELLPRICE","BUYMONEY","FEE","COUNT","TOPLIMIT","BOTTOMLIMIT","PLAMOUNT","ADDTIME","SELLTIME","COUPONFLAG","BANKID","TRANLOGNO","CHANNEL","UPDATEDTIME","UPDATEDBY","OPINION","TOPPRICE","BOTTOMPRICE","DEFICITPRICE","MERSEQID","MERDATE","SFJSTAT","BELONGSID","USERID","APPROVALTYPE","BUYNUM","SOURCE","INCREMENTID","IFUP"
"8429157","318825846716072523222191","o0_vAv7E7yl4yq-au2SksDx3AsDw","3","1","46","4214.000","4222.000","72.000","5.400","9","0.00","0.00","-7.20","2016/7/25 23:22:21","2016/7/25 23:39:36","0","","","","","","","0.000","0.000","4294.000","","","","","258467","","318825846716072523222191","JAVA","",""

Handling of dates

I realize that this project may be under develpment, but I thought I would try it out.
With a small amount of testing I found it to work well, but I noticed that plain SQL dates (on SQL Server) seem to be handled wrong. Below is a quick example illustrating the example. I would be happy to provide more details if you like.

library(odbconnect)
conn <- dbConnect(odbconnect::odbconnect(), 
                  driver = "{SQL server Native Client 11.0}", 
                  Server = "MYSERVER\\MyInstance", 
                  Trusted_Connection = 'yes')

R <- dbSendQuery(conn, "select cast('2015-1-1' as date) d1, cast('2015-1-2' as date) d2, cast('2015-1-1' as datetime) dt")
X <- dbFetch(R)
stopifnot(dbClearResult(R)) # ok
stopifnot(class(X$d1) == 'Date', class(X$d2) == 'Date', class(X$dt) == c("POSIXct", "POSIXt" )) # ok
stopifnot(identical(as.POSIXct('2015-1-1', tz='UTC'), X$dt)) # ok
stopifnot(format(X$d1) == "3053957-01-18") # passes, but surprising!

dbDisconnect(conn)

Thanks for your work on this so far.

Unknown field type -11 with uniqueidentifier and SQL Server

Issue Description and Expected Result

dbGetQuery reports "Unknown field type (-11) in column..." with uniqueidentifier

It's a warning only, results look ok (character)

Database

SQL Server 13.0.1722 under Windows 64

odbc 1.0.1 from CRAN

Standardise readme

One paragraph overview of goals (why you should care)
Installation section, including what you need on mac and linux
Usage section showing reading, writing, and a query
Benchmarks section

Vague error connecting to SQL Server: "Error in odbc_connect(connection_string) : nanodbc.cpp:895"

Trying to connect to a SQL Server instance gives me a cryptic error:

Error in odbc_connect(connection_string) : nanodbc.cpp:895: �   �:

Database

SQL Server 2012 (and SQL Server 2016 throws the same error)

Reproducible Example

I get an error as soon as I try to connect (in this case to the example AdventureWorks database):

library(DBI)
con <- dbConnect(odbc::odbc(),
                 driver = "FreeTDS",
                 database = "AdventureWorks2012",
                 uid = "sqlfamily",
                 pwd = "sqlf@m1ly",
                 host = "mhknbn2kdz.database.windows.net",
                 port = 1433)

Error in odbc_connect(connection_string) : nanodbc.cpp:895: �   �:

(That one line, including the �, is the full error, I haven't gotten it to print out more).

I've tried some variations on this connection string, putting the data in odbc.ini, and gotten this error consistently (and the same on my own database). I've also been able to connect to my own database through isql (in unix-odbc) without a problem, so I don't think it's a problem with my driver setup.

Session Info

R version 3.3.0 (2016-05-03)
Platform: x86_64-apple-darwin13.4.0 (64-bit)
Running under: OS X 10.11.5 (El Capitan)

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

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

other attached packages:
[1] DBI_0.5-12

loaded via a namespace (and not attached):
[1] odbc_0.0.0.9000 tools_3.3.0     Rcpp_0.12.8

Read and write tibbles

dbRemoveTable doesn't seem to work on Teradata databases and may crash R

dbRemoveTable unsuccessful with Teradata tables and may crash rstudio

Database

Teradata 13.10.00

Reproducible Example

Example:

library(odbc)
library(DBI)

conn <- dbConnect(odbc::odbc(), "DWH", dbname = "db_esp")
create_tbl <- dbSendStatement(conn, 
                          "create table db_esp.am_test_tbl as 
                          (
                              select id,
                              day
                              from db_lam.vaf_mq
                              where day= date '2016-12-02'
                          ) with data;")
dbClearResult(create_tbl)
dbExistsTable(conn, "am_test_tbl") #returns TRUE
dbRemoveTable(conn, name = "am_test_tbl") #throws error

Error in new_result(connection@ptr, statement) : nanodbc.cpp:1344: 42S02: [Teradata][ODBC Teradata Driver][Teradata Database] Object 'am_test_tbl' does not exist.

Session Info

devtools::session_info()
#> 
Session info ---------------------------------------------------------------------------------------------
 setting  value                       
 version  R version 3.3.1 (2016-06-21)
 system   x86_64, mingw32             
 ui       RStudio (1.0.136)           
 language (EN)                        
 collate  French_France.1252          
 tz       Europe/Paris                
 date     2017-04-23                  

Packages -------------------------------------------------------------------------------------------------
 package  * version date       source        
 blob       1.0.0   2016-12-29 CRAN (R 3.3.3)
 DBI        0.6-1   2017-04-01 CRAN (R 3.3.3)
 devtools   1.11.1  2016-04-21 CRAN (R 3.3.0)
 digest     0.6.10  2016-08-02 CRAN (R 3.3.2)
 memoise    1.0.0   2016-01-29 CRAN (R 3.3.0)
 odbc     * 1.0.1   2017-02-07 CRAN (R 3.3.3)
 Rcpp       0.12.10 2017-03-19 CRAN (R 3.3.3)
 tibble     1.3.0   2017-04-01 CRAN (R 3.3.3)
 withr      1.0.2   2016-06-20 CRAN (R 3.3.2)

I could of course just use sendStatement and that works fine. The annoying thing is also that it crashes Rstudio (it's probably the R session that's being crashed and in turn crashes Rstudio). I'm thinking there's some kind of segfault going on.
I also tested this on Linux Fedora 24 with the same results.

dbGetQuery: Dates as Characters Microsoft SQL

Issue Description and Expected Result

dbGetQuery() returns character objects instead of date objects from dates on Microsoft SQL Server.

Database

Microsoft SQL Server (I think 2012)

Reproducible Example

Example:

library(odbc)
library(DBI)

con <- DBI::dbConnect(drv = odbc::odbc(), dsn = ... , uid = ..., pwd = ...) # Credentials to the Microsoft SQL server.

qry <- "SELECT '2017-01-01' AS date, CAST('2017-01-01' AS date) AS casted_date, CAST('2017-01-01' AS datetime) AS casted_datetime"

date_test <- DBI::dbGetQuery(con, qry)

This creates the data frame date_test with three columns:
date = Character (should in my understanding be a date object in R)
casted_date = Character (should in my understanding be a date object in R)
casted_datetime = POSIXct (is fine for me)

So, I expected the columns date and casted_date to date objects and not character objects in R.

Further information

Using RODBC leads to the same result of getting characters instead of dates. Thus, it is possible, that this is some error on my side somewhere in my ODBC settings or on the Microsoft SQL server. I hope that my example above is reproduceable to you in order to rule that case out - or at least limit the likelihood for that. :-)

Please let me know, if you need any further input.

Session Info

devtools::session_info()
Session info -----------------------------------------------------------------------
 setting  value                       
 version  R version 3.3.3 (2017-03-06)
 system   x86_64, mingw32             
 ui       RStudio (1.0.136)           
 language (EN)                        
 collate  German_Germany.1252         
 tz       Europe/Berlin               
 date     2017-05-22                  

Packages ---------------------------------------------------------------------------
 package    * version date       source        
 assertthat   0.2.0   2017-04-11 CRAN (R 3.3.3)
 base       * 3.3.3   2017-03-06 local         
 blob         1.0.0   2016-12-29 CRAN (R 3.3.3)
 datasets   * 3.3.3   2017-03-06 local         
 DBI        * 0.6-1   2017-04-01 CRAN (R 3.3.3)
 devtools     1.13.1  2017-05-13 CRAN (R 3.3.3)
 digest       0.6.12  2017-01-27 CRAN (R 3.3.2)
 dplyr      * 0.5.0   2016-06-24 CRAN (R 3.3.1)
 graphics   * 3.3.3   2017-03-06 local         
 grDevices  * 3.3.3   2017-03-06 local         
 magrittr     1.5     2014-11-22 CRAN (R 3.3.2)
 memoise      1.1.0   2017-04-21 CRAN (R 3.3.3)
 methods    * 3.3.3   2017-03-06 local         
 odbc       * 1.0.1   2017-02-07 CRAN (R 3.3.3)
 R6           2.2.1   2017-05-10 CRAN (R 3.3.3)
 Rcpp         0.12.11 2017-05-22 CRAN (R 3.3.3)
 rlang        0.1.1   2017-05-18 CRAN (R 3.3.3)
 RODBC        1.3-15  2017-04-13 CRAN (R 3.3.3)
 stats      * 3.3.3   2017-03-06 local         
 tibble       1.3.1   2017-05-17 CRAN (R 3.3.3)
 tools        3.3.3   2017-03-06 local         
 utils      * 3.3.3   2017-03-06 local         
 withr        1.0.2   2016-06-20 CRAN (R 3.3.1)

select queries are faster with the RODBC package on Hadoop databases

Impala/Hive queries faster when using RODBC

This isn't a bug per say. However I've noticed that dbGetQuery() type queries are consistently slower on Hadoop (at least on our installation).

Database

Cloudera Impala CDH 5.7.1 and Cloudera Hive CDH 5.7..1

Reproducible Example

I cannot produce a fully reproducible example since to my knowledege DBI doesn't provide standard Hive/Impala tables that can be tested with minimal work. I can say however that I have seen substantial (up to 3x times faster) on select queries using a Teradata database when using odbc in place of RODBC. Maybe there's something particular in how Hive/impala queries are handled that's not reflected in odbc's current implementation.

Example:

library(RODBC)
library(microbenchmark)
library(odbc)

conn_impala <- odbcConnect("ODBC-Impala-XXXX")
conn_impala_dbi <- dbConnect(odbc::odbc(), "ODBC-Impala-XXXX")
query_impala <- "select * from database.logs where part_date = '2016-12-15' limit 1000000"
microbenchmark(
  odbc = res_odbc_impala <- dbGetQuery(conn = conn_impala_dbi, query_impala),
  rodbc = res_RODBC_impala <- sqlQuery(channel = conn_impala, query_impala),
  times = 1L
)

  expr      min       lq     mean   median       uq      max neval
  odbc 403.3625 403.3625 403.3625 403.3625 403.3625 403.3625     1
 rodbc 152.5502 152.5502 152.5502 152.5502 152.5502 152.5502     1

Btw, the query is long enough that it's not affected by random variations.

Session Info

devtools::session_info()
#> Unit: seconds
Session info ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 setting  value                       
 version  R version 3.3.1 (2016-06-21)
 system   x86_64, linux-gnu           
 ui       RStudio (1.0.136)           
 language (EN)                        
 collate  fr_FR.UTF-8                 
 tz       Europe/Paris                
 date     2017-02-13                  

Packages -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 package        * version    date       source                        
 assertthat       0.1        2013-12-06 CRAN (R 3.3.1)                
 blob             0.0.0.9000 2016-12-13 Github (hadley/blob@f1d4bf7)  
 codetools        0.2-14     2015-07-15 CRAN (R 3.3.1)                
 colorspace       1.3-1      2016-11-18 CRAN (R 3.3.1)                
 DBI              0.5-13     2016-12-13 Github (rstats-db/DBI@238b676)
 devtools         1.12.0     2016-06-24 CRAN (R 3.3.1)                
 digest           0.6.10     2016-08-02 CRAN (R 3.3.1)                
 ggplot2          2.2.0      2016-11-11 CRAN (R 3.3.1)                
 gtable           0.2.0      2016-02-26 CRAN (R 3.3.1)                
 httr             1.2.1      2016-07-03 CRAN (R 3.3.1)                
 lattice          0.20-33    2015-07-14 CRAN (R 3.3.1)                
 lazyeval         0.2.0      2016-06-12 CRAN (R 3.3.1)                
 MASS             7.3-45     2016-04-21 CRAN (R 3.3.1)                
 Matrix           1.2-6      2016-05-02 CRAN (R 3.3.1)                
 memoise          1.0.0      2016-01-29 CRAN (R 3.3.1)                
 microbenchmark * 1.4-2.1    2015-11-25 CRAN (R 3.3.1)                
 multcomp         1.4-6      2016-07-14 CRAN (R 3.3.1)                
 munsell          0.4.3      2016-02-13 CRAN (R 3.3.1)                
 mvtnorm          1.0-5      2016-02-02 CRAN (R 3.3.1)                
 odbc           * 1.0.0      2017-02-05 CRAN (R 3.3.1)                
 plyr             1.8.4      2016-06-08 CRAN (R 3.3.1)                
 R6               2.2.0      2016-10-05 CRAN (R 3.3.1)                
 Rcpp             0.12.8     2016-11-17 CRAN (R 3.3.1)                
 RODBC          * 1.3-14     2016-09-26 CRAN (R 3.3.1)                
 rsconnect        0.6        2016-11-21 CRAN (R 3.3.1)                
 sandwich         2.3-4      2015-09-24 CRAN (R 3.3.1)                
 scales           0.4.1      2016-11-09 CRAN (R 3.3.1)                
 survival         2.40-1     2016-10-30 CRAN (R 3.3.1)                
 TH.data          1.0-7      2016-01-28 CRAN (R 3.3.1)                
 tibble           1.2        2016-08-26 CRAN (R 3.3.1)                
 withr            1.0.2      2016-06-20 CRAN (R 3.3.1)                
 zoo              1.7-13     2016-05-03 CRAN (R 3.3.1)

Unable to connect to Redshift on macOS Sierra

Issue Description and Expected Result

Unable to create connection to Amazon Redshift database from Mac running macOS Sierra (10.12.2). I installed unixodbc via homebrew, and installed the Amazon Redshift ODBC driver per Amazon's instructions. I also have /etc/odbc.ini and /etc/odbcinst.ini configured as per the readme.

Good News

The good news is that I can connect to Redshift using unixodcb:

kyle@KyleDesk:~|⇒  more /etc/odbcinst.ini
[Amazon Redshift]
Driver = /opt/amazon/redshift/lib/libamazonredshiftodbc.dylib
kyle@KyleDesk:~|⇒  more /etc/odbc.ini
[shared_public_read_user]
Driver              = Amazon Redshift
Database            = mortgage
Servername          = xxxxx
UserName            = yyyyy
Password            = zzzzz
Port                = 5439
kyle@KyleDesk:~|⇒  isql -v shared_public_read_user
+---------------------------------------+
| Connected!                            |

Bad News

The bad news is that I can't connect from within R using the same DSN.

library(DBI)
con1 <- dbConnect(odbc::odbc(), dsn = "shared_public_read_user")

> library(DBI)
> con1 <- dbConnect(odbc::odbc(), dsn = "shared_public_read_user")
Error in odbc_connect(connection_string) : 
  nanodbc.cpp:950: IM002: [unixODBC][Driver Manager]Data source name not found, and no default driver specified

Nor can I connect from within R using the same parameters as in the odbcinst.ini file, even if I tweak the connection arguments to match the examples in the README file herein.

con2 <- dbConnect(odbc::odbc(),
                  driver = "Amazon Redshift",
                  database = "mortgage",
                  # servername = "xxxxx",
                  host = "xxxxx",
                  # UserName = "yyyyy",
                  uid = "yyyyy",
                  # password = "zzzzz",
                  pwd = "zzzzz",
                  port = 5439 )

> con2 <- dbConnect(odbc::odbc(),
+                   driver = "Amazon Redshift",
+                   database = "mortgage",
+                   # servername = "xxxxx",
+                   host = "xxxxx",
+                   # UserName = "yyyyy",
+                   uid = "yyyyy",
+                   # password = "zzzzz",
+                   pwd = "zzzzz",
+                   port = 5439 )
Error in odbc_connect(connection_string) : 
  nanodbc.cpp:950: 01000: [unixODBC][Driver Manager]Can't open lib 'Amazon Redshift' : file not found

I'd appreciate any help you could offer!

BTW, thanks for putting together your slides on Database Best Practices.

Windows install fail

Issue Description and Expected Result

I'm trying to install on windows but it is failing:

devtools::install_github("rstats-db/odbc", args=c('--no-multiarch'))

#> output
Downloading GitHub repo rstats-db/odbc@master
from URL https://api.github.com/repos/rstats-db/odbc/zipball/master
Installing odbc
"C:/work/software/R/R-3.3.2/bin/x64/R" --no-site-file --no-environ --no-save  \
  --no-restore --quiet CMD INSTALL  \
  "C:/Users/wcooper003/AppData/Local/Temp/RtmpkzTJD0/devtools344440d410cf/rstats-db-odbc-201a1e1"  \
  --library="C:/work/software/R/R-3.3.2/library" --with-keep.source  \
  --install-tests --no-multiarch 

* installing *source* package 'odbc' ...
** libs
(cd cctz && \
make libcctz.a PREFIX="../" CC="C:/work/software/R/Rtools34/mingw_64/bin/gcc " CXX="C:/work/software/R/Rtools34/mingw_64/bin/g++  -std=c++0x" AR="C:/work/software/R/Rtools34/mingw_64/bin/ar" ARFLAGS=rv)
make[1]: Entering directory `/cygdrive/c/Users/wcooper003/AppData/Local/Temp/RtmpkzTJD0/devtools344440d410cf/rstats-db-odbc-201a1e1/src/cctz'
C:/work/software/R/Rtools34/mingw_64/bin/g++  -std=c++0x  -Wall -Iinclude -std=c++11 -pthread  -O -fPIC -MMD  -c -o time_zone_format.o src/time_zone_format.cc
src/time_zone_format.cc:1:0: warning: -fPIC ignored for target (all code is position independent)
 // Copyright 2016 Google Inc. All Rights Reserved.
 ^
C:/work/software/R/Rtools34/mingw_64/bin/g++  -std=c++0x  -Wall -Iinclude -std=c++11 -pthread  -O -fPIC -MMD  -c -o time_zone_if.o src/time_zone_if.cc
src/time_zone_if.cc:1:0: warning: -fPIC ignored for target (all code is position independent)
 // Copyright 2016 Google Inc. All Rights Reserved.
 ^
C:/work/software/R/Rtools34/mingw_64/bin/g++  -std=c++0x  -Wall -Iinclude -std=c++11 -pthread  -O -fPIC -MMD  -c -o time_zone_impl.o src/time_zone_impl.cc
src/time_zone_impl.cc:1:0: warning: -fPIC ignored for target (all code is position independent)
 // Copyright 2016 Google Inc. All Rights Reserved.
 ^
C:/work/software/R/Rtools34/mingw_64/bin/g++  -std=c++0x  -Wall -Iinclude -std=c++11 -pthread  -O -fPIC -MMD  -c -o time_zone_info.o src/time_zone_info.cc
src/time_zone_info.cc:1:0: warning: -fPIC ignored for target (all code is position independent)
 // Copyright 2016 Google Inc. All Rights Reserved.
 ^
C:/work/software/R/Rtools34/mingw_64/bin/g++  -std=c++0x  -Wall -Iinclude -std=c++11 -pthread  -O -fPIC -MMD  -c -o time_zone_libc.o src/time_zone_libc.cc
src/time_zone_libc.cc:1:0: warning: -fPIC ignored for target (all code is position independent)
 // Copyright 2016 Google Inc. All Rights Reserved.
 ^
C:/work/software/R/Rtools34/mingw_64/bin/g++  -std=c++0x  -Wall -Iinclude -std=c++11 -pthread  -O -fPIC -MMD  -c -o time_zone_lookup.o src/time_zone_lookup.cc
src/time_zone_lookup.cc:1:0: warning: -fPIC ignored for target (all code is position independent)
 // Copyright 2016 Google Inc. All Rights Reserved.
 ^
C:/work/software/R/Rtools34/mingw_64/bin/g++  -std=c++0x  -Wall -Iinclude -std=c++11 -pthread  -O -fPIC -MMD  -c -o time_zone_posix.o src/time_zone_posix.cc
src/time_zone_posix.cc:1:0: warning: -fPIC ignored for target (all code is position independent)
 // Copyright 2016 Google Inc. All Rights Reserved.
 ^
C:/work/software/R/Rtools34/mingw_64/bin/ar rv libcctz.a time_zone_format.o time_zone_if.o time_zone_impl.o time_zone_info.o time_zone_libc.o time_zone_lookup.o time_zone_posix.o
a - time_zone_format.o
a - time_zone_if.o
a - time_zone_impl.o
a - time_zone_info.o
a - time_zone_libc.o
a - time_zone_lookup.o
a - time_zone_posix.o
C:\work\software\R\Rtools34\mingw_64\bin\ar.exe: creating libcctz.a
make[1]: Leaving directory `/cygdrive/c/Users/wcooper003/AppData/Local/Temp/RtmpkzTJD0/devtools344440d410cf/rstats-db-odbc-201a1e1/src/cctz'
C:/work/software/R/Rtools34/mingw_64/bin/g++  -std=c++0x -I"C:/work/software/R/R-33~1.2/include" -DNDEBUG    -I"C:/work/software/R/R-3.3.2/library/Rcpp/include" -I"C:/work/software/R/R-3.3.2/library/BH/include" -I"d:/Compiler/gcc-4.9.3/local330/include"  -I. -DNANODBC_USE_BOOST_CONVERT -Icctz/include   -O2 -Wall  -mtune=core2 -c RcppExports.cpp -o RcppExports.o
C:/work/software/R/Rtools34/mingw_64/bin/g++  -std=c++0x -I"C:/work/software/R/R-33~1.2/include" -DNDEBUG    -I"C:/work/software/R/R-3.3.2/library/Rcpp/include" -I"C:/work/software/R/R-3.3.2/library/BH/include" -I"d:/Compiler/gcc-4.9.3/local330/include"  -I. -DNANODBC_USE_BOOST_CONVERT -Icctz/include   -O2 -Wall  -mtune=core2 -c connection.cpp -o connection.o
C:/work/software/R/Rtools34/mingw_64/bin/gcc  -I"C:/work/software/R/R-33~1.2/include" -DNDEBUG    -I"C:/work/software/R/R-3.3.2/library/Rcpp/include" -I"C:/work/software/R/R-3.3.2/library/BH/include" -I"d:/Compiler/gcc-4.9.3/local330/include"     -O2 -Wall  -std=gnu99 -mtune=core2 -c init.c -o init.o
C:/work/software/R/Rtools34/mingw_64/bin/g++  -std=c++0x -I"C:/work/software/R/R-33~1.2/include" -DNDEBUG    -I"C:/work/software/R/R-3.3.2/library/Rcpp/include" -I"C:/work/software/R/R-3.3.2/library/BH/include" -I"d:/Compiler/gcc-4.9.3/local330/include"  -I. -DNANODBC_USE_BOOST_CONVERT -Icctz/include   -O2 -Wall  -mtune=core2 -c nanodbc.cpp -o nanodbc.o
C:/work/software/R/Rtools34/mingw_64/bin/g++  -std=c++0x -I"C:/work/software/R/R-33~1.2/include" -DNDEBUG    -I"C:/work/software/R/R-3.3.2/library/Rcpp/include" -I"C:/work/software/R/R-3.3.2/library/BH/include" -I"d:/Compiler/gcc-4.9.3/local330/include"  -I. -DNANODBC_USE_BOOST_CONVERT -Icctz/include   -O2 -Wall  -mtune=core2 -c odbc_connection.cpp -o odbc_connection.o
In file included from C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:13:0,
                 from C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:9,
                 from odbc_connection.h:6,
                 from odbc_connection.cpp:1:
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqltypes.h:37:11: error: 'INT64' does not name a type
   typedef INT64 SQLLEN;
           ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqltypes.h:38:11: error: 'UINT64' does not name a type
   typedef UINT64 SQLULEN;
           ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqltypes.h:39:11: error: 'UINT64' does not name a type
   typedef UINT64 SQLSETPOSIROW;
           ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqltypes.h:46:11: error: 'SQLULEN' does not name a type
   typedef SQLULEN SQLROWCOUNT;
           ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqltypes.h:47:11: error: 'SQLULEN' does not name a type
   typedef SQLULEN SQLROWSETSIZE;
           ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqltypes.h:48:11: error: 'SQLULEN' does not name a type
   typedef SQLULEN SQLTRANSID;
           ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqltypes.h:49:11: error: 'SQLLEN' does not name a type
   typedef SQLLEN SQLROWOFFSET;
           ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqltypes.h:110:11: error: 'HWND' does not name a type
   typedef HWND SQLHWND;
           ^
In file included from C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:13:0,
                 from C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:9,
                 from odbc_connection.h:6,
                 from odbc_connection.cpp:1:
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqltypes.h:204:5: error: 'DWORD' does not name a type
     DWORD Data1;
     ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqltypes.h:205:5: error: 'WORD' does not name a type
     WORD Data2;
     ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqltypes.h:206:5: error: 'WORD' does not name a type
     WORD Data3;
     ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqltypes.h:207:5: error: 'BYTE' does not name a type
     BYTE Data4[8 ];
     ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqltypes.h:212:11: error: 'SQLULEN' does not name a type
   typedef SQLULEN BOOKMARK;
           ^
In file included from C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:9:0,
                 from odbc_connection.h:6,
                 from odbc_connection.cpp:1:
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:489:129: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLBindCol(SQLHSTMT StatementHandle,SQLUSMALLINT ColumnNumber,SQLSMALLINT TargetType,SQLPOINTER TargetValue,SQLLEN BufferLength,SQLLEN *StrLen_or_Ind);
                                                                                                                                 ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:489:149: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLBindCol(SQLHSTMT StatementHandle,SQLUSMALLINT ColumnNumber,SQLSMALLINT TargetType,SQLPOINTER TargetValue,SQLLEN BufferLength,SQLLEN *StrLen_or_Ind);
                                                                                                                                                     ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:491:136: error: 'SQLULEN' has not been declared
   SQLRETURN SQL_API SQLBindParam(SQLHSTMT StatementHandle,SQLUSMALLINT ParameterNumber,SQLSMALLINT ValueType,SQLSMALLINT ParameterType,SQLULEN LengthPrecision,SQLSMALLINT ParameterScale,SQLPOINTER ParameterValue,SQLLEN *StrLen_or_Ind);
                                                                                                                                        ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:491:213: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLBindParam(SQLHSTMT StatementHandle,SQLUSMALLINT ParameterNumber,SQLSMALLINT ValueType,SQLSMALLINT ParameterType,SQLULEN LengthPrecision,SQLSMALLINT ParameterScale,SQLPOINTER ParameterValue,SQLLEN *StrLen_or_Ind);
                                                                                                                                                                                                                     ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:497:198: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLColAttribute(SQLHSTMT StatementHandle,SQLUSMALLINT ColumnNumber,SQLUSMALLINT FieldIdentifier,SQLPOINTER CharacterAttribute,SQLSMALLINT BufferLength,SQLSMALLINT *StringLength,SQLLEN *NumericAttribute);
                                                                                                                                                                                                      ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:508:178: error: 'SQLULEN' has not been declared
   SQLRETURN SQL_API SQLDescribeCol(SQLHSTMT StatementHandle,SQLUSMALLINT ColumnNumber,SQLCHAR *ColumnName,SQLSMALLINT BufferLength,SQLSMALLINT *NameLength,SQLSMALLINT *DataType,SQLULEN *ColumnSize,SQLSMALLINT *DecimalDigits,SQLSMALLINT *Nullable);
                                                                                                                                                                                  ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:518:90: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLFetchScroll(SQLHSTMT StatementHandle,SQLSMALLINT FetchOrientation,SQLLEN FetchOffset);
                                                                                          ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:531:129: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLGetData(SQLHSTMT StatementHandle,SQLUSMALLINT ColumnNumber,SQLSMALLINT TargetType,SQLPOINTER TargetValue,SQLLEN BufferLength,SQLLEN *StrLen_or_Ind);
                                                                                                                                 ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:531:149: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLGetData(SQLHSTMT StatementHandle,SQLUSMALLINT ColumnNumber,SQLSMALLINT TargetType,SQLPOINTER TargetValue,SQLLEN BufferLength,SQLLEN *StrLen_or_Ind);
                                                                                                                                                     ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:534:187: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLGetDescRec(SQLHDESC DescriptorHandle,SQLSMALLINT RecNumber,SQLCHAR *Name,SQLSMALLINT BufferLength,SQLSMALLINT *StringLength,SQLSMALLINT *Type,SQLSMALLINT *SubType,SQLLEN *Length,SQLSMALLINT *Precision,SQLSMALLINT *Scale,SQLSMALLINT *Nullable);
                                                                                                                                                                                           ^
In file included from C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:9:0,
                 from odbc_connection.h:6,
                 from odbc_connection.cpp:1:
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:549:73: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLPutData(SQLHSTMT StatementHandle,SQLPOINTER Data,SQLLEN StrLen_or_Ind);
                                                                         ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:550:58: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLRowCount(SQLHSTMT StatementHandle,SQLLEN *RowCount);
                                                          ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:554:86: error: 'SQLULEN' has not been declared
   SQLRETURN SQL_API SQLSetConnectOption(SQLHDBC ConnectionHandle,SQLUSMALLINT Option,SQLULEN Value);
                                                                                      ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:558:120: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLSetDescRec(SQLHDESC DescriptorHandle,SQLSMALLINT RecNumber,SQLSMALLINT Type,SQLSMALLINT SubType,SQLLEN Length,SQLSMALLINT Precision,SQLSMALLINT Scale,SQLPOINTER Data,SQLLEN *StringLength,SQLLEN *Indicator);
                                                                                                                        ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:558:190: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLSetDescRec(SQLHDESC DescriptorHandle,SQLSMALLINT RecNumber,SQLSMALLINT Type,SQLSMALLINT SubType,SQLLEN Length,SQLSMALLINT Precision,SQLSMALLINT Scale,SQLPOINTER Data,SQLLEN *StringLength,SQLLEN *Indicator);
                                                                                                                                                                                              ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:558:211: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLSetDescRec(SQLHDESC DescriptorHandle,SQLSMALLINT RecNumber,SQLSMALLINT Type,SQLSMALLINT SubType,SQLLEN Length,SQLSMALLINT Precision,SQLSMALLINT Scale,SQLPOINTER Data,SQLLEN *StringLength,SQLLEN *Indicator);
                                                                                                                                                                                                                   ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:561:135: error: 'SQLULEN' has not been declared
   SQLRETURN SQL_API SQLSetParam(SQLHSTMT StatementHandle,SQLUSMALLINT ParameterNumber,SQLSMALLINT ValueType,SQLSMALLINT ParameterType,SQLULEN LengthPrecision,SQLSMALLINT ParameterScale,SQLPOINTER ParameterValue,SQLLEN *StrLen_or_Ind);
                                                                                                                                       ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:561:212: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLSetParam(SQLHSTMT StatementHandle,SQLUSMALLINT ParameterNumber,SQLSMALLINT ValueType,SQLSMALLINT ParameterType,SQLULEN LengthPrecision,SQLSMALLINT ParameterScale,SQLPOINTER ParameterValue,SQLLEN *StrLen_or_Ind);
                                                                                                                                                                                                                    ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sql.h:565:83: error: 'SQLULEN' has not been declared
   SQLRETURN SQL_API SQLSetStmtOption(SQLHSTMT StatementHandle,SQLUSMALLINT Option,SQLULEN Value);
                                                                                   ^
In file included from odbc_connection.h:6:0,
                 from odbc_connection.cpp:1:
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1366:51: error: 'SQLHWND' has not been declared
   SQLRETURN SQL_API SQLDriverConnect(SQLHDBC hdbc,SQLHWND hwnd,SQLCHAR *szConnStrIn,SQLSMALLINT cbConnStrIn,SQLCHAR *szConnStrOut,SQLSMALLINT cbConnStrOutMax,SQLSMALLINT *pcbConnStrOut,SQLUSMALLINT fDriverCompletion);
                                                   ^
In file included from odbc_connection.h:6:0,
                 from odbc_connection.cpp:1:
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1427:156: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLColAttributes(SQLHSTMT hstmt,SQLUSMALLINT icol,SQLUSMALLINT fDescType,SQLPOINTER rgbDesc,SQLSMALLINT cbDescMax,SQLSMALLINT *pcbDesc,SQLLEN *pfDesc);
                                                                                                                                                            ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1429:94: error: 'SQLULEN' has not been declared
   SQLRETURN SQL_API SQLDescribeParam(SQLHSTMT hstmt,SQLUSMALLINT ipar,SQLSMALLINT *pfSqlType,SQLULEN *pcbParamDef,SQLSMALLINT *pibScale,SQLSMALLINT *pfNullable);
                                                                                              ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1430:77: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLExtendedFetch(SQLHSTMT hstmt,SQLUSMALLINT fFetchType,SQLLEN irow,SQLULEN *pcrow,SQLUSMALLINT *rgfRowStatus);
                                                                             ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1430:89: error: 'SQLULEN' has not been declared
   SQLRETURN SQL_API SQLExtendedFetch(SQLHSTMT hstmt,SQLUSMALLINT fFetchType,SQLLEN irow,SQLULEN *pcrow,SQLUSMALLINT *rgfRowStatus);
                                                                                         ^
In file included from odbc_connection.h:6:0,
                 from odbc_connection.cpp:1:
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1435:52: error: 'SQLULEN' has not been declared
   SQLRETURN SQL_API SQLParamOptions(SQLHSTMT hstmt,SQLULEN crow,SQLULEN *pirow);
                                                    ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1435:65: error: 'SQLULEN' has not been declared
   SQLRETURN SQL_API SQLParamOptions(SQLHSTMT hstmt,SQLULEN crow,SQLULEN *pirow);
                                                                 ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1439:46: error: 'SQLSETPOSIROW' has not been declared
   SQLRETURN SQL_API SQLSetPos(SQLHSTMT hstmt,SQLSETPOSIROW irow,SQLUSMALLINT fOption,SQLUSMALLINT fLock);
                                              ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1442:134: error: 'SQLULEN' has not been declared
   SQLRETURN SQL_API SQLBindParameter(SQLHSTMT hstmt,SQLUSMALLINT ipar,SQLSMALLINT fParamType,SQLSMALLINT fCType,SQLSMALLINT fSqlType,SQLULEN cbColDef,SQLSMALLINT ibScale,SQLPOINTER rgbValue,SQLLEN cbValueMax,SQLLEN *pcbValue);
                                                                                                                                      ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1442:191: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLBindParameter(SQLHSTMT hstmt,SQLUSMALLINT ipar,SQLSMALLINT fParamType,SQLSMALLINT fCType,SQLSMALLINT fSqlType,SQLULEN cbColDef,SQLSMALLINT ibScale,SQLPOINTER rgbValue,SQLLEN cbValueMax,SQLLEN *pcbValue);
                                                                                                                                                                                               ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1442:209: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLBindParameter(SQLHSTMT hstmt,SQLUSMALLINT ipar,SQLSMALLINT fParamType,SQLSMALLINT fCType,SQLSMALLINT fSqlType,SQLULEN cbColDef,SQLSMALLINT ibScale,SQLPOINTER rgbValue,SQLLEN cbValueMax,SQLLEN *pcbValue);
                                                                                                                                                                                                                 ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1489:82: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLSetScrollOptions(SQLHSTMT hstmt,SQLUSMALLINT fConcurrency,SQLLEN crowKeyset,SQLUSMALLINT crowRowset);
                                                                                  ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1493:36: error: 'LPWSTR' was not declared in this scope
   RETCODE SQL_API TraceOpenLogFile(LPWSTR,LPWSTR,DWORD);
                                    ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1493:43: error: 'LPWSTR' was not declared in this scope
   RETCODE SQL_API TraceOpenLogFile(LPWSTR,LPWSTR,DWORD);
                                           ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1493:50: error: 'DWORD' was not declared in this scope
   RETCODE SQL_API TraceOpenLogFile(LPWSTR,LPWSTR,DWORD);
                                                  ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1493:55: error: expression list treated as compound expression in initializer [-fpermissive]
   RETCODE SQL_API TraceOpenLogFile(LPWSTR,LPWSTR,DWORD);
                                                       ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1495:3: error: 'VOID' does not name a type
   VOID SQL_API TraceReturn(RETCODE,RETCODE);
   ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1496:3: error: 'DWORD' does not name a type
   DWORD SQL_API TraceVersion();
   ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1501:34: error: 'DWORD' was not declared in this scope
   RETCODE SQL_API TraceVSControl(DWORD);
                                  ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1503:3: error: 'WINBOOL' does not name a type
   WINBOOL SQL_API ODBCSetTryWaitValue(DWORD dwValue);
   ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1504:3: error: 'DWORD' does not name a type
   DWORD SQL_API ODBCGetTryWaitValue();
   ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1512:11: error: 'GUID' does not name a type
     const GUID *pguidEvent;
           ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1513:5: error: 'DWORD' does not name a type
     DWORD dwFlags;
     ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1515:7: error: 'WCHAR' does not name a type
       WCHAR *wszArg;
       ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1516:7: error: 'CHAR' does not name a type
       CHAR *szArg;
       ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1519:7: error: 'WCHAR' does not name a type
       WCHAR *wszCorrelation;
       ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1520:7: error: 'CHAR' does not name a type
       CHAR *szCorrelation;
       ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1524:3: error: 'VOID' does not name a type
   VOID SQL_API FireVSDebugEvent(PODBC_VS_ARGS);
   ^
In file included from C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1531:0,
                 from odbc_connection.h:6,
                 from odbc_connection.cpp:1:
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlucode.h:30:163: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLColAttributeW(SQLHSTMT hstmt,SQLUSMALLINT iCol,SQLUSMALLINT iField,SQLPOINTER pCharAttr,SQLSMALLINT cbCharAttrMax,SQLSMALLINT *pcbCharAttr,SQLLEN *pNumAttr);
                                                                                                                                                                   ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlucode.h:34:157: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLColAttributesW(SQLHSTMT hstmt,SQLUSMALLINT icol,SQLUSMALLINT fDescType,SQLPOINTER rgbDesc,SQLSMALLINT cbDescMax,SQLSMALLINT *pcbDesc,SQLLEN *pfDesc);
                                                                                                                                                             ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlucode.h:36:162: error: 'SQLULEN' has not been declared
   SQLRETURN SQL_API SQLDescribeColW(SQLHSTMT hstmt,SQLUSMALLINT icol,SQLWCHAR *szColName,SQLSMALLINT cbColNameMax,SQLSMALLINT *pcbColName,SQLSMALLINT *pfSqlType,SQLULEN *pcbColDef,SQLSMALLINT *pibScale,SQLSMALLINT *pfNullable);
                                                                                                                                                                  ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlucode.h:44:174: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLGetDescRecW(SQLHDESC hdesc,SQLSMALLINT iRecord,SQLWCHAR *szName,SQLSMALLINT cbNameMax,SQLSMALLINT *pcbName,SQLSMALLINT *pfType,SQLSMALLINT *pfSubType,SQLLEN *pLength,SQLSMALLINT *pPrecision,SQLSMALLINT *pScale,SQLSMALLINT *pNullable);
                                                                                                                                                                              ^
In file included from C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlext.h:1531:0,
                 from odbc_connection.h:6,
                 from odbc_connection.cpp:1:
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlucode.h:55:76: error: 'SQLULEN' has not been declared
   SQLRETURN SQL_API SQLSetConnectOptionW(SQLHDBC hdbc,SQLUSMALLINT fOption,SQLULEN vParam);
                                                                            ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlucode.h:60:52: error: 'SQLHWND' has not been declared
   SQLRETURN SQL_API SQLDriverConnectW(SQLHDBC hdbc,SQLHWND hwnd,SQLWCHAR *szConnStrIn,SQLSMALLINT cbConnStrIn,SQLWCHAR *szConnStrOut,SQLSMALLINT cbConnStrOutMax,SQLSMALLINT *pcbConnStrOut,SQLUSMALLINT fDriverCompletion);
                                                    ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlucode.h:73:161: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLColAttributeA(SQLHSTMT hstmt,SQLSMALLINT iCol,SQLSMALLINT iField,SQLPOINTER pCharAttr,SQLSMALLINT cbCharAttrMax,SQLSMALLINT *pcbCharAttr,SQLLEN *pNumAttr);
                                                                                                                                                                 ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlucode.h:77:157: error: 'SQLLEN' has not been declared
   SQLRETURN SQL_API SQLColAttributesA(SQLHSTMT hstmt,SQLUSMALLINT icol,SQLUSMALLINT fDescType,SQLPOINTER rgbDesc,SQLSMALLINT cbDescMax,SQLSMALLINT *pcbDesc,SQLLEN *pfDesc);
                                                                                                                                                             ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlucode.h:99:76: error: 'SQLULEN' has not been declared
   SQLRETURN SQL_API SQLSetConnectOptionA(SQLHDBC hdbc,SQLUSMALLINT fOption,SQLULEN vParam);
                                                                            ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlucode.h:100:75: error: 'SQLULEN' has not been declared
   SQLRETURN SQL_API SQLSetStmtOptionA(SQLHSTMT hstmt,SQLUSMALLINT fOption,SQLULEN vParam);
                                                                           ^
C:/work/software/R/Rtools34/mingw_64/x86_64-w64-mingw32/include/sqlucode.h:105:52: error: 'SQLHWND' has not been declared
   SQLRETURN SQL_API SQLDriverConnectA(SQLHDBC hdbc,SQLHWND hwnd,SQLCHAR *szConnStrIn,SQLSMALLINT cbConnStrIn,SQLCHAR *szConnStrOut,SQLSMALLINT cbConnStrOutMax,SQLSMALLINT *pcbConnStrOut,SQLUSMALLINT fDriverCompletion);
                                                    ^
make: *** [odbc_connection.o] Error 1
Warning: running command 'make -f "Makevars.win" -f "C:/work/software/R/R-33~1.2/etc/x64/Makeconf" -f "C:/work/software/R/R-33~1.2/share/make/winshlib.mk" CXX='$(CXX1X) $(CXX1XSTD)' CXXFLAGS='$(CXX1XFLAGS)' CXXPICFLAGS='$(CXX1XPICFLAGS)' SHLIB_LDFLAGS='$(SHLIB_CXX1XLDFLAGS)' SHLIB_LD='$(SHLIB_CXX1XLD)' SHLIB="odbc.dll" WIN=64 TCLBIN=64 OBJECTS="RcppExports.o connection.o init.o nanodbc.o odbc_connection.o result.o"' had status 2
ERROR: compilation failed for package 'odbc'
* removing 'C:/work/software/R/R-3.3.2/library/odbc'
Error: Command failed (1)

Database

Reproducible Example

Session Info

devtools::session_info()
#> output
Session info -------------------------------------------------------------------
 setting  value                       
 version  R version 3.3.2 (2016-10-31)
 system   x86_64, mingw32             
 ui       unknown                     
 language (EN)                        
 collate  English_United States.1252  
 tz       America/New_York            
 date     2017-03-23                  

Packages -----------------------------------------------------------------------
 package  * version  date       source        
 curl       2.3      2016-11-24 CRAN (R 3.3.2)
 devtools * 1.12.0   2016-06-24 CRAN (R 3.3.2)
 digest     0.6.10   2016-08-02 CRAN (R 3.3.2)
 git2r      0.16.0   2016-11-20 CRAN (R 3.3.2)
 httr       1.2.1    2016-07-03 CRAN (R 3.3.2)
 memoise    1.0.0    2016-01-29 CRAN (R 3.3.2)
 R6         2.2.0    2016-10-05 CRAN (R 3.3.2)
 rj       * 2.1.0-11 2016-09-19 local         
 withr      1.0.2    2016-06-20 CRAN (R 3.3.2)

Compiling odbc pkg fails with "no matching function call to Rcpp::exception::exception"

I am trying to compile the the current version of the odbc package (commit f311ac2) on Ubuntu 14.04 as well as on Windows 7 using RStudio 1.0.x.

The build process fails with

odbc_connection.h:26:44: error: no matching function for call to 'Rcpp::exception::exception(const char*, int)'
odbc_result.h:26:34: error: no matching function for call to 'Rcpp::exception::exception(const char [1], bool)'

I am using R 3.2.2 64 Bit on Windows and Ubuntu.
Rcpp is installed via CRAN (version Rcpp_0.12.10).

It looks like the Rcpp constructors that odbc calls now have changed but I am compiling against and "old" Rcpp version (even though the most recent one available on CRAN), e. g.

odbc_connection.h:

   try {
      c_ = std::make_shared<nanodbc::connection>(connection_string);
    } catch (nanodbc::database_error e) {
      throw Rcpp::exception(e.what(), FALSE);
    }

Can you reproduce this behaviour? Any recommendation how to build the package (e. g. installing Rcpp from github)?

THX!

Build log details:

In file included from odbc_types.h:5:0,
from connection.cpp:4:
odbc_connection.h: In constructor 'odbc::odbc_connection::odbc_connection(std::string, std::string, std::string)':
odbc_connection.h:26:44: error: no matching function for call to 'Rcpp::exception::exception(const char*, int)'
throw Rcpp::exception(e.what(), FALSE);
^
odbc_connection.h:26:44: note: candidates are:
In file included from C:/Users/Public/inst/R-3.3.2/library/Rcpp/include/RcppCommon.h:122:0,
from C:/Users/Public/inst/R-3.3.2/library/Rcpp/include/Rcpp.h:27,
from connection.cpp:1:
C:/Users/Public/inst/R-3.3.2/library/Rcpp/include/Rcpp/exceptions.h:34:9: note: Rcpp::exception::exception(const char*, const char*, int)
exception(const char* message_, const char* file, int line) : message(message_) {
^
C:/Users/Public/inst/R-3.3.2/library/Rcpp/include/Rcpp/exceptions.h:34:9: note: candidate expects 3 arguments, 2 provided
C:/Users/Public/inst/R-3.3.2/library/Rcpp/include/Rcpp/exceptions.h:31:18: note: Rcpp::exception::exception(const char*)
explicit exception(const char* message_) : message(message_) { // #nocov start
^
C:/Users/Public/inst/R-3.3.2/library/Rcpp/include/Rcpp/exceptions.h:31:18: note: candidate expects 1 argument, 2 provided
C:/Users/Public/inst/R-3.3.2/library/Rcpp/include/Rcpp/exceptions.h:29:11: note: Rcpp::exception::exception(const Rcpp::exception&)
class exception : public std::exception {
^
C:/Users/Public/inst/R-3.3.2/library/Rcpp/include/Rcpp/exceptions.h:29:11: note: candidate expects 1 argument, 2 provided
In file included from odbc_types.h:6:0,
from connection.cpp:4:
odbc_result.h: In constructor 'odbc::odbc_error::odbc_error(nanodbc::database_error, const string&)':
odbc_result.h:26:34: error: no matching function for call to 'Rcpp::exception::exception(const char [1], bool)'
: Rcpp::exception("", false) {
^
odbc_result.h:26:34: note: candidates are:
In file included from C:/Users/Public/inst/R-3.3.2/library/Rcpp/include/RcppCommon.h:122:0,
from C:/Users/Public/inst/R-3.3.2/library/Rcpp/include/Rcpp.h:27,
from connection.cpp:1:
C:/Users/Public/inst/R-3.3.2/library/Rcpp/include/Rcpp/exceptions.h:34:9: note: Rcpp::exception::exception(const char*, const char*, int)
exception(const char* message_, const char* file, int line) : message(message_) {
^
C:/Users/Public/inst/R-3.3.2/library/Rcpp/include/Rcpp/exceptions.h:34:9: note: candidate expects 3 arguments, 2 provided
C:/Users/Public/inst/R-3.3.2/library/Rcpp/include/Rcpp/exceptions.h:31:18: note: Rcpp::exception::exception(const char*)
explicit exception(const char* message_) : message(message_) { // #nocov start
^
C:/Users/Public/inst/R-3.3.2/library/Rcpp/include/Rcpp/exceptions.h:31:18: note: candidate expects 1 argument, 2 provided
C:/Users/Public/inst/R-3.3.2/library/Rcpp/include/Rcpp/exceptions.h:29:11: note: Rcpp::exception::exception(const Rcpp::exception&)
class exception : public std::exception {
^
C:/Users/Public/inst/R-3.3.2/library/Rcpp/include/Rcpp/exceptions.h:29:11: note: candidate expects 1 argument, 2 provided
make: *** [connection.o] Error 1

DATE column type is Character.

Issue Description and Expected Result

While datetimes seem to load nicely as dttm type columns, dates are currently parsed as character type - would be great to iron out this tiny kink. Let me know if I can provide any more info. Thanks.

Database

SQL Server 2014 (12.0.5000.0)

Reproducible Example

Example:

library(odbc)
con <- dbConnect(odbc::odbc(), "Warehouse")
tbl_df(dbGetQuery(con, 'select top 10 Interestdate, InterestDateTime FROM vwInterestFlow'))

   Interestdate    InterestDateTime
          <chr>              <dttm>
1    2013-04-23 2013-04-23 19:41:32
2    2013-04-23 2013-04-24 00:12:33
3    2013-04-23 2013-04-24 00:59:41
4    2013-04-23 2013-04-23 06:54:34
5    2013-04-23 2013-04-23 05:48:09
6    2013-04-23 2013-04-23 20:17:58
7    2013-04-23 2013-04-23 16:25:53
8    2013-04-23 2013-04-23 13:39:59
9    2013-04-23 2013-04-24 00:26:43
10   2013-04-23 2013-04-23 01:38:07

Session info

``` ### Session Info ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- setting value version R version 3.3.1 (2016-06-21) system x86_64, mingw32 ui RStudio (1.0.44) language (EN) collate English_United States.1252 tz Europe/London date 2016-11-10

Packages

package * version date source
assertthat 0.1 2013-12-06 CRAN (R 3.3.1)
curl 1.2 2016-08-13 CRAN (R 3.3.1)
DBI 0.5-12 2016-11-09 Github (r-dbi/DBI@4f00863)
devtools 1.12.0 2016-06-24 CRAN (R 3.3.1)
digest 0.6.10 2016-08-02 CRAN (R 3.3.1)
dplyr * 0.5.0 2016-06-24 CRAN (R 3.3.1)
git2r 0.15.0 2016-05-11 CRAN (R 3.3.1)
httr 1.2.1 2016-07-03 CRAN (R 3.3.1)
knitr 1.14 2016-08-13 CRAN (R 3.3.1)
lazyeval 0.2.0 2016-06-12 CRAN (R 3.3.1)
magrittr 1.5 2014-11-22 CRAN (R 3.3.1)
memoise 1.0.0 2016-01-29 CRAN (R 3.3.1)
odbc * 0.0.0.9000 2016-11-09 Github (7eef2f3)
R6 2.1.3 2016-08-19 CRAN (R 3.3.1)
Rcpp 0.12.7 2016-09-05 CRAN (R 3.3.1)
tibble 1.2 2016-08-26 CRAN (R 3.3.1)
withr 1.0.2 2016-06-20 CRAN (R 3.3.1)

64 Bit integers

Issue Description and Expected Result

64 Bit integers are able to be read and written from databases. Currently they are converted to 32 bit native R integers (i.e. nonsense).

Database

All databases that support 64 bit integers.

Reproducible Example

library(DBI)
library(odbconnect)
con <- dbConnect(odbconnect::odbconnect(), dsn = "PostgreSQL")
dbGetQuery(con, "SELECT 10000000000 as a")
#>            a
#> 1 1410065408

Session Info

devtools::session_info()
#> Session info -------------------------------------------------------------
#>  setting  value                       
#>  version  R version 3.3.0 (2016-05-03)
#>  system   x86_64, darwin13.4.0        
#>  ui       X11                         
#>  language (EN)                        
#>  collate  en_US.UTF-8                 
#>  tz       America/New_York            
#>  date     2016-11-07
#> Packages -----------------------------------------------------------------
#>  package    * version     date       source                            
#>  assertthat   0.1         2013-12-06 CRAN (R 3.3.0)                    
#>  blob         0.0.0.9000  2016-11-03 Github (jimhester/blob@d677541)   
#>  clipr        0.2.1       2016-06-23 CRAN (R 3.3.0)                    
#>  colorspace   1.2-7       2016-10-11 cran (@1.2-7)                     
#>  crayon       1.3.2       2016-06-28 cran (@1.3.2)                     
#>  DBI        * 0.5-12      2016-10-13 Github (rstats-db/DBI@4f00863)    
#>  DBItest      1.3-10      2016-11-03 local (jimhester/DBItest@8284aef) 
#>  devtools     1.12.0.9000 2016-10-28 Github (hadley/devtools@f0d1c7e)  
#>  digest       0.6.10      2016-08-02 CRAN (R 3.3.0)                    
#>  evaluate     0.10        2016-10-11 cran (@0.10)                      
#>  ggplot2    * 2.1.0.9001  2016-10-28 Github (hadley/ggplot2@cd6a691)   
#>  gtable       0.2.0       2016-02-26 CRAN (R 3.3.0)                    
#>  htmltools    0.3.5       2016-03-21 CRAN (R 3.3.0)                    
#>  knitr        1.14.15     2016-11-07 Github (yihui/knitr@8cbc61a)      
#>  lazyeval     0.2.0.9000  2016-06-15 Github (hadley/lazyeval@c155c3d)  
#>  magrittr     1.5         2014-11-22 cran (@1.5)                       
#>  memoise      1.0.0.9001  2016-10-28 local (jimhester/memoise@bd541fb) 
#>  munsell      0.4.3       2016-02-13 CRAN (R 3.3.0)                    
#>  nvimcom    * 0.9-23      2016-09-26 local                             
#>  odbconnect * 0.0.0.9000  <NA>       local                             
#>  plyr         1.8.4       2016-06-08 cran (@1.8.4)                     
#>  R6           2.2.0       2016-10-05 cran (@2.2.0)                     
#>  Rcpp         0.12.7      2016-09-05 CRAN (R 3.3.0)                    
#>  reprex       0.0.0.9001  2016-05-11 Github (jennybc/reprex@826ddf4)   
#>  rmarkdown    1.1.9010    2016-10-28 Github (rstudio/rmarkdown@6305a8e)
#>  roxygen2     5.0.1       2015-11-11 CRAN (R 3.3.0)                    
#>  rprojroot    1.0-2       2016-03-28 cran (@1.0-2)                     
#>  rstudioapi   0.6         2016-06-27 cran (@0.6)                       
#>  scales       0.4.0.9003  2016-10-13 Github (hadley/scales@d58d83a)    
#>  stringi      1.1.2       2016-10-01 CRAN (R 3.3.0)                    
#>  stringr      1.1.0.9000  2016-11-07 Github (hadley/stringr@e030642)   
#>  testthat   * 1.0.2.9000  2016-10-07 local (jimhester/testthat@11b3316)
#>  tibble       1.2-12      2016-10-13 Github (hadley/tibble@7c4a712)    
#>  withr        1.0.2       2016-06-20 Github (jimhester/withr@91279ae)

Errors while compiling in Windows 7 enterprise

Issue Description and Expected Result

After downloading I opened the project on RStudio and tried to build the package. After a while it returned with the following two errors:

src/odbc_connection.h
Line 23 no matching function for call to 'Rcpp::exception::exception(const char*, int)'

src/odbc_result.h
Line 25 no matching function for call to 'Rcpp::exception::exception(const cha [1], bool)'

Database

Database is "Microsoft SQL Server 12.0.5000.0"

SQL Server requires `varchar(max)` and `varbinary(max)` columns to be at the end of `SELECT` statements.

Issue Description and Expected Result

SQL Server requires varchar(max) and varbinary(max) columns to be at the end of SELECT statements. If they are before the end a [Microsoft][ODBC Driver 13 for SQL Server]Invalid Descriptor Index error is returned. This is apparently a known workaround for this problem.

Database

SQL Server 2013 (13.00.1601)

Reproducible Example

library(DBI);library(odbc)
con <- dbConnect(odbc::odbc(), "test")

# Reorder columns and put the species column first
it <- iris[1:10, c(5, 1, 2)]
dbWriteTable(con, "it", it)
#> [1] TRUE

# SELECT using normal order with varchar(max) first
dbGetQuery(con, "SELECT * from it")
#> Warning in value[[3L]](cond): nanodbc.cpp:2755: 07009: [Microsoft][ODBC
#> Driver 13 for SQL Server]Invalid Descriptor Index
#> Warning: Pending rows
#> NULL

# However SELECT using varchar(max) at the end work fine
dbGetQuery(con, "SELECT \"Sepal.Length\", \"Sepal.Width\", Species from it")
#>    Sepal.Length Sepal.Width Species
#> 1           5.1         3.5  setosa
#> 2           4.9         3.0  setosa
#> 3           4.7         3.2  setosa
#> 4           4.6         3.1  setosa
#> 5           5.0         3.6  setosa
#> 6           5.4         3.9  setosa
#> 7           4.6         3.4  setosa
#> 8           5.0         3.4  setosa
#> 9           4.4         2.9  setosa
#> 10          4.9         3.1  setosa

Session Info

devtools::session_info()
#> Session info -------------------------------------------------------------
#>  setting  value                       
#>  version  R version 3.3.1 (2016-06-21)
#>  system   x86_64, mingw32             
#>  ui       RStudio (1.0.35)            
#>  language (EN)                        
#>  collate  English_United States.1252  
#>  tz       America/New_York            
#>  date     2016-11-07
#> Packages -----------------------------------------------------------------
#>  package    * version     date       source                          
#>  assertthat   0.1         2013-12-06 CRAN (R 3.3.1)                  
#>  blob         0.0.0.9000  2016-11-07 Github (jimhester/blob@d677541) 
#>  clipr        0.2.1       2016-06-23 CRAN (R 3.3.2)                  
#>  crayon       1.3.2       2016-06-28 CRAN (R 3.3.1)                  
#>  curl         2.1         2016-09-22 CRAN (R 3.3.1)                  
#>  DBI        * 0.5-12      2016-10-31 Github (rstats-db/DBI@4f00863)  
#>  devtools     1.12.0.9000 2016-10-06 local                           
#>  digest       0.6.10      2016-08-02 CRAN (R 3.3.1)                  
#>  evaluate     0.10        2016-10-11 CRAN (R 3.3.2)                  
#>  formatR      1.4         2016-05-09 CRAN (R 3.3.2)                  
#>  git2r        0.15.0      2016-05-11 CRAN (R 3.3.1)                  
#>  htmltools    0.3.5       2016-03-21 CRAN (R 3.3.2)                  
#>  httr         1.2.1       2016-07-03 CRAN (R 3.3.1)                  
#>  jsonlite     1.1         2016-09-14 CRAN (R 3.3.1)                  
#>  knitr        1.14        2016-08-13 CRAN (R 3.3.2)                  
#>  magrittr     1.5         2014-11-22 CRAN (R 3.3.1)                  
#>  memoise      1.0.0       2016-01-29 CRAN (R 3.3.1)                  
#>  odbconnect * 0.0.0.9000  <NA>       local                           
#>  R6           2.2.0       2016-10-05 CRAN (R 3.3.1)                  
#>  Rcpp         0.12.7      2016-09-05 CRAN (R 3.3.1)                  
#>  reprex       0.0.0.9001  2016-11-07 Github (jennybc/reprex@826ddf4) 
#>  rmarkdown    1.1         2016-10-16 CRAN (R 3.3.2)                  
#>  roxygen2     5.0.1       2015-11-11 CRAN (R 3.3.1)                  
#>  stringi      1.1.2       2016-10-01 CRAN (R 3.3.1)                  
#>  stringr      1.1.0       2016-08-19 CRAN (R 3.3.1)                  
#>  testthat     1.0.2.9000  2016-10-11 Github (hadley/testthat@46d15da)
#>  tibble       1.2         2016-08-26 CRAN (R 3.3.1)                  
#>  withr        1.0.2       2016-06-20 CRAN (R 3.3.1)

`dbWriteTable()` needs a fallback if transactions are not supported by a driver

Issue Description and Expected Result

dbWriteTable() needs a fallback if transactions are not supported by a driver. Currently the code assumes it can use transactions, but not all ODBC drivers support them.

Database

Magnitude Spark Db / Driver does not support transactions.

Improve the error messages with SQL statements

Issue Description and Expected Result

e.g.

Error: Unable to retrieve result set for SELECT * FROM "HumanResources.Department": Invalid object name 'HumanResources.Department'.

instead of

Error in result_execute(res@ptr) :
  nanodbc.cpp:1532: 42S02: [FreeTDS][SQL Server]Invalid object name 'HumanResources.Department'.

Reproducible Example

Example:

library(DBI)
con <- dbConnect(odbc(),
                 driver = "SQL Server Driver",
                 database = "AdventureWorks2012",
                 uid = "sqlfamily",
                 pwd = "sqlf@m1ly",
                 server = "mhknbn2kdz.database.windows.net",
                 port = 1433)
dbReadTable(con, "HumanResources.Department")

con2 <- dbConnect(RSQLServer::SQLServer(),
                 database = "AdventureWorks2012",
                 properties = c(user = "sqlfamily",
                   password = "sqlf@m1ly"),
                 server = "mhknbn2kdz.database.windows.net",
                 port = 1433)
dbReadTable(con2, "HumanResources.Department")

Session Info

Session info -------------------------------------------------------------------
 setting  value
 version  R version 3.3.2 RC (2016-10-26 r71594)
 system   x86_64, darwin13.4.0
 ui       X11
 language (EN)
 collate  en_US.UTF-8
 tz       America/New_York
 date     2016-12-14

Packages -----------------------------------------------------------------------
 package    * version    date       source
 assertthat   0.1        2013-12-06 CRAN (R 3.3.0)
 BH           1.62.0-1   2016-11-19 CRAN (R 3.3.0)
 blob         0.0.0.9000 2016-11-11 Github (hadley/blob@f1d4bf7)
 clisymbols   1.0.0      2015-06-08 cran (@1.0.0)
 codetools    0.2-15     2016-10-05 CRAN (R 3.3.2)
 commonmark   1.0        2016-12-01 cran (@1.0)
 covr         2.2.1      2016-12-05 local (jimhester/covr@81b4a4a)
 crayon       1.3.2      2016-06-28 cran (@1.3.2)
 DBI        * 0.5-1      2016-09-10 CRAN (R 3.3.0)
 desc         1.0.1      2016-09-23 cran (@1.0.1)
 devtools     1.12.0     2016-06-24 CRAN (R 3.3.0)
 digest       0.6.10.1   2016-12-07 local (jimhester/digest@NA)
 dplyr        0.5.0.9000 2016-12-14 Github (javierluraschi/dplyr@6220be8)
 highlite     0.0.0.9000 2016-11-30 Github (jimhester/highlite@767b122)
 httr         1.2.1.9000 2016-12-05 Github (gaborcsardi/httr@30001d4)
 jsonlite     1.1        2016-09-14 CRAN (R 3.3.0)
 lazyeval     0.2.0      2016-06-12 CRAN (R 3.3.0)
 lookup     * 0.0.0.9000 2016-12-09 local (jimhester/lookup@NA)
 magrittr     1.5        2014-11-22 cran (@1.5)
 memoise      1.0.0.9001 2016-12-02 Github (hadley/memoise@e392c7b)
 nvimcom    * 0.9-23     2016-09-26 local
 odbc       * 0.0.0.9000 <NA>       local
 prettycode   1.0.0      2016-11-17 Github (gaborcsardi/prettycode@b0144d1)
 purrr        0.2.2.9000 2016-11-09 Github (hadley/purrr@5360143)
 R6           2.2.0      2016-10-05 CRAN (R 3.3.0)
 Rcpp         0.12.8.2   2016-12-08 Github (RcppCore/Rcpp@8c7246e)
 reprex       0.0.0.9001 2016-11-23 Github (jennybc/reprex@040670c)
 rex          1.1.1      2016-03-11 cran (@1.1.1)
 rJava        0.9-8      2016-01-07 cran (@0.9-8)
 roxygen2     5.0.1.9000 2016-12-07 Github (klutometis/roxygen@6225d7a)
 RSQLServer * 0.2.099    2016-12-14 Github (imanuelcostigan/RSQLServer@ab018d9)
 rstudioapi   0.6        2016-06-27 cran (@0.6)
 stringi      1.1.2      2016-10-01 CRAN (R 3.3.0)
 stringr      1.1.0.9000 2016-11-07 Github (hadley/stringr@e030642)
 testthat     1.0.2      2016-04-23 cran (@1.0.2)
 tibble       1.2        2016-08-26 cran (@1.2)
 tracer       1.0.0      2016-11-23 local (jimhester/tracer@67d6b40)
 withr        1.0.2      2016-11-10 local (jimhester/withr@NA)
 xml2         1.0.0.9001 2016-12-08 local (jimhester/xml2@NA)

Manual parameterized queries using dbBind()

library(DBI);con <- dbConnect(odbc::odbc(), "PostgreSQL");dbWriteTable(con, "iris", iris)
iris_result <- dbSendQuery(con, "SELECT * FROM iris WHERE 'Petal.Width' > ?")
dbBind(iris_result, list(2.3))
dbFetch(iris_result)
dbBind(iris_result, list(2.4))
dbFetch(iris_result)
dbClearResult(iris_result); dbDisconnect(con)

Error message needs newlines?

Error in odbc_connect(connection_string, timezone = timezone) : 
  nanodbc.cpp:950: 01000: [unixODBC][Driver Manager]Can't open lib 'SQLite Driver' : file not found

Ideally would be

Error: nanodbc.cpp:950: 01000: [unixODBC][Driver Manager]
Can't open lib 'SQLite Driver' : file not found

Investigate why SQL Server tests are not running on Appveyor

Probably something changed in the authentication.

Support for Manifold GIS

odbc cannot connect to a Manifold data source on Windows that works with RODBC.

I'm hoping that odbc can support Manifold GIS: http://www.manifold.net/ so looking for some guidance on how to help, but certainly I can easily do tests.

Issue Description and Expected Result

Currently there's an error with the connection:

dbConnect(odbc::odbc(), connection_string  = dstring)
# Error in eval(substitute(expr), envir, enclos) : 
#   nanodbc.cpp:895: IM002: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Database

Manifold System 8.0 Ultimate Edition (build 8.0.29.0)

Example

(requires installation of Manifold 8.0 on windows)

library(odbc)
mapfile <- "C:/temp/manifold.map"
dstring <- sprintf("DRIVER={Manifold Project Driver (*.map)};DBQ=%s;Unicode=True;Ansi=False;OpenGIS=True;DSN=Default;", 
normalizePath(mapfile))
dbConnect(odbc::odbc(), connection_string  = dstring)
# Error in eval(substitute(expr), envir, enclos) : 
#   nanodbc.cpp:895: IM002: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Successful result from RODBC

This works as expected with RODBC:

library(RODBC)
mapfile <- "C:/temp/manifold.map"
dstring <- sprintf("DRIVER={Manifold Project Driver (*.map)};DBQ=%s;Unicode=True;Ansi=False;OpenGIS=True;DSN=Default;", normalizePath(mapfile))
con <- RODBC::odbcDriverConnect(dstring)
sqlTables(con)
# TABLE_CAT TABLE_SCHEM                TABLE_NAME TABLE_TYPE REMARKS
# 1      <NA>        <NA>             Drawing Table      TABLE        
# 2      <NA>        <NA>          GEOMETRY_COLUMNS      TABLE        
# 3      <NA>        <NA> SPATIAL_REFERENCE_SYSTEMS      TABLE        
# 4      <NA>        <NA>              drawing_attr       VIEW        
# 5      <NA>        <NA>        drawing_wkb_native       VIEW        
# 6      <NA>        <NA>        drawing_wkb_latlon       VIEW

devtools::session_info()
# Session info -------------------------------------------------------------------
#  setting  value                                      
#  version  R version 3.3.2 Patched (2016-11-09 r71645)
#  system   x86_64, mingw32                            
#  ui       Rgui                                       
#  language (EN)                                       
#  collate  English_Australia.1252                     
#  tz       Australia/Hobart                           
#  date     2016-11-13                                 
# 
# Packages -----------------------------------------------------------------------
#  package  * version    date       source                            
#  DBI        0.5-12     2016-10-07 Github (rstats-db/DBI@4f00863)    
#  devtools   1.12.0     2016-06-24 CRAN (R 3.3.2)                    
#  digest     0.6.10     2016-08-02 CRAN (R 3.3.1)                    
#  memoise    1.0.0      2016-01-29 CRAN (R 3.3.0)                    
#  odbc     * 0.0.0.9000 2016-11-13 Github (hadley/odbconnect@8426669)
#  Rcpp       0.12.7     2016-09-05 CRAN (R 3.3.1)                    
#  RODBC      1.3-14     2016-09-26 CRAN (R 3.3.1)                    
#  withr      1.0.2      2016-06-20 CRAN (R 3.3.0)

The project file is zipped here, though obviously using requires an installation of Manifold on Windows:
manifold.zip

Support for TIME type (MySQL)

Issue Description and Expected Result

Columns of type TIME generates an error when fetching from a MySQL database with the following error:

Error in result_fetch(res@ptr, n, ...) : type incompatible

Database

MySQL 5.1.73

Reproducible Example

library(odbc)

con <- dbConnect(odbc::odbc(),"MySQLdb")
res <- dbGetQuery(con,statement ="select time(0)")

Session Info

devtools::session_info()
#> output

Session info -------------------------------------------------------------------
 setting  value                       
 version  R version 3.3.3 (2017-03-06)
 system   x86_64, mingw32             
 ui       Rgui                        
 language (EN)                        
 collate  Danish_Denmark.1252         
 tz       Europe/Paris                
 date     2017-03-31                  

Packages -----------------------------------------------------------------------
 package    * version date       source        
 assertthat   0.1     2013-12-06 CRAN (R 3.3.3)
 blob         1.0.0   2016-12-29 CRAN (R 3.3.3)
 DBI          0.6     2017-03-09 CRAN (R 3.3.3)
 devtools     1.12.0  2016-06-24 CRAN (R 3.3.3)
 digest       0.6.12  2017-01-27 CRAN (R 3.3.3)
 memoise      1.0.0   2016-01-29 CRAN (R 3.3.3)
 odbc       * 1.0.1   2017-02-07 CRAN (R 3.3.3)
 Rcpp         0.12.10 2017-03-19 CRAN (R 3.3.3)
 rstudioapi   0.6     2016-06-27 CRAN (R 3.3.3)
 tibble       1.2     2016-08-26 CRAN (R 3.3.3)
 withr        1.0.2   2016-06-20 CRAN (R 3.3.3)

Spark, Hive, Impala odbc: dbWriteTable() unable to create new tables

Issue Description and Expected Result

dbWriteTable() is throwing the error below when attempting to create a new table in Spark. This same problem happens for the Hive, Impala, Salesforce and MongoDB drivers as well.

Database

Salesforce, MongoDB, Hive2, Impala, Spark

Reproducible Example

This error is produced when running the example code below:

Error in new_result(connection@ptr, statement) : nanodbc.cpp:1344: HY000: [Simba][Salesforce] (120) SOQL_FIRST mode prepare failure: SOQL error: [Simba][SQLEngine] (31480) syntax error near 'CREATE<<< ??? >>> TABLE "test" ( "Sepal.Length" DOUBLE PRECISION, "Sepal.Width" DOUBLE PRECISION, "Petal.Length" DOUBLE PRECISION, "Petal.Width" DOUBLE PRECISION, "Species" varchar(255) ) '. SQL error: [Simba][SQLEngine]

Example R Chunk:

library(odbc)
odbcImpalaCon <- dbConnect(odbc(),
Driver = "/etc/drivers/SimbaImpalaODBC64-1.2.7.1009/lib/libimpalaodbc_sb64.so",
Host = "***",
UID = "***",
PWD = "***",
AuthMech=3,
Port = 10000)
dbGetQuery(odbcImpalaCon, "select * From customers")
dbListTables(odbcImpalaCon)
dbWriteTable(odbcImpalaCon, "test", iris, overwrite = TRUE) # fails
dbReadTable(odbcImpalaCon, "mtcars") # fails

Session Info

devtools::session_info()
#>
Session info ---------------------------------------------------------------------------------------------------------
 setting  value                       
 version  R version 3.3.2 (2016-10-31)
 system   x86_64, linux-gnu           
 ui       RStudio (1.0.136)           
 language (EN)                        
 collate  en_US.UTF-8                 
 tz       America/New_York            
 date     2017-03-09                  

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)                    
 base64enc    0.1-3    2015-07-28 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)                    
 jsonlite   * 1.3      2017-02-28 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-07 CRAN (R 3.3.2)                    
 Rcpp         0.12.9   2017-01-14 CRAN (R 3.3.2)                    
 rjson      * 0.2.15   2014-11-03 CRAN (R 3.3.2)                    
 rmarkdown    1.3.9002 2017-03-01 Github (rstudio/rmarkdown@f098542)
 RODBC      * 1.3-14   2016-09-26 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.2.0    2017-02-18 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)

dbListTable() doesn't list temporary tables

library(DBI)

con <- dbConnect(odbc::odbc(),
  driver = "SQLite Driver",
  database = ":memory:"
)

dbWriteTable(con, "mtcars", mtcars, temporary = TRUE)
dbListTables(con)
#> character(0)

Clarify connection process

Pull out function to create connection string from name-value pairs
Document dbConnect() method separately
Include an example

r-dbi / odbc Goto Github PK

odbc's Introduction

odbc

Overview

Installation

Usage

odbc's People

Contributors

Stargazers

Watchers

Forkers

odbc's Issues

Issue Description and Expected Result

Database

Reproducible Example

Issue Description and Expected Result

Database

Reproducible Example

R session info

Other observations

Problem retrieving bigint values

Database

Non-reproducible example

Issue Description and Expected Result

Database

Reproducible Example

Issue Description and Expected Result

Database

Reproducible Example

Issue Description and Expected Result

Database

Reproducible Example

Issue Description and Expected Result

Database

Reproducible Example

Issue Description and Expected Result

Database

Reproducible Example

Issue Description and Expected Result

Database

Example

Issue Description and Expected Result

Database

Reproducible Example

.travis.yml:

DESCRIPTION:

Issue Description and Expected Result

Database

Database

Reproducible Example

dbRemoveTable unsuccessful with Teradata tables and may crash rstudio

Database

Reproducible Example

Issue Description and Expected Result

Database

Reproducible Example

Further information

Impala/Hive queries faster when using RODBC

Database

Reproducible Example

Issue Description and Expected Result

Good News

Bad News

Issue Description and Expected Result

Database

Reproducible Example

Issue Description and Expected Result

Database

Reproducible Example

Packages

Issue Description and Expected Result

Database

Reproducible Example

Session Info

Issue Description and Expected Result

Database

Issue Description and Expected Result

Database

Reproducible Example

Issue Description and Expected Result

`.travis.yml`:

`DESCRIPTION`: