r-dbi / odbc Goto Github PK
View Code? Open in Web Editor NEWConnect to ODBC databases (using the DBI interface)
Home Page: https://odbc.r-dbi.org/
License: Other
Connect to ODBC databases (using the DBI interface)
Home Page: https://odbc.r-dbi.org/
License: Other
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.
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.
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.
Teradata 13.10.00
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.
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.
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
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
> 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
varchar
column does not helpdbWriteTable
create the target table (by omitting the append
parameter) does not helpdata$col1 <- iconv(data$col1, from="latin1", to="UTF-8"
) does not help and writes the exactly same (wrong) bytes into the databaseodbc
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.
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
Manifold System 8.0 Ultimate Edition (build 8.0.29.0)
(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
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
When I try to build include odbc in my script, it fails to compile
N/A
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
$ 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
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?
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.
SQL Server 2013 (13.00.1601)
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
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)
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?
> 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
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)
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)
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)
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).
All databases that support 64 bit integers.
library(DBI)
library(odbconnect)
con <- dbConnect(odbconnect::odbconnect(), dsn = "PostgreSQL")
dbGetQuery(con, "SELECT 10000000000 as a")
#> a
#> 1 1410065408
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)
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.
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! |
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.
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.
Magnitude Spark Db / Driver does not support transactions.
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
dbConnect()
method separatelydata <- 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
Microsoft SQL Server 13.00.1601
using FreeTDS Version: freetds v0.95.81
Let me know what else would help!
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 version Oracle 12.01.0020, ODBC version 03.80.0000, Driver Version 11.02.0003, ODBC driver version 03.52
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)
> 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)
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.
Salesforce, MongoDB, Hive2, Impala, Spark
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
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)
dbGetQuery()
returns character objects instead of date objects from dates on Microsoft SQL Server.
Microsoft SQL Server (I think 2012)
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.
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.
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)
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).
Cloudera Impala CDH 5.7.1 and Cloudera Hive CDH 5.7..1
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.
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)
Listing, querying dbListTables()
, dbReadTable()
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.
Probably something changed in the authentication.
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)
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
MySQL 5.1.73
library(odbc)
con <- dbConnect(odbc::odbc(),"MySQLdb")
res <- dbGetQuery(con,statement ="select time(0)")
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)
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
dbGetQuery reports "Unknown field type (-11) in column..." with uniqueidentifier
It's a warning only, results look ok (character)
SQL Server 13.0.1722 under Windows 64
odbc 1.0.1 from CRAN
So that you know after you've installed the driver you need to edit a file
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).
SQL Server 2014 (12.0.5000.0)
> 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
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 is "Microsoft SQL Server 12.0.5000.0"
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.
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.
SQL Server 2012
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
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)
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'.
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 -------------------------------------------------------------------
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)
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
library(DBI)
con <- dbConnect(odbc::odbc(),
driver = "SQLite Driver",
database = ":memory:"
)
dbWriteTable(con, "mtcars", mtcars, temporary = TRUE)
dbListTables(con)
#> character(0)
dbGetInfo does not return Port number and Server Name does not match for MSSQL
SQL Server 2012
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
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.
SQL Server 2014 (12.0.5000.0)
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
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)
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
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
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
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
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)
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)
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
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!
> 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","",""
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
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
Trying to connect to a SQL Server instance gives me a cryptic error:
Error in odbc_connect(connection_string) : nanodbc.cpp:895: � �:
SQL Server 2012 (and SQL Server 2016 throws the same error)
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.
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
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.
Spark 1.6.2
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)")
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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.