Calling exa.write or exa.read from provided examples causes unknown error.

>require(RODBC)
>require(exasol)

>C <- odbcConnect("exasolution")

>odbcQuery(C, "CREATE SCHEMA test")
[1] 1
>odbcQuery(C, "CREATE TABLE test.twogroups (groupid INT, val DOUBLE)")
[1] 1
>exa.writeData(C, twogroups, tableName = "test.twogroups")
Fehler in exa.writeData(C, twogroups, tableName = "test.twogroups") : 
Failed to receive proxy header (0 != 24)

odbcQuery gets executed such that schema and table are created in the database.

I'm running on OS X El Capitan (10.11.2) using the following Driver EXASolution_ODBC-5.0.13-MacOS.dmg.

R-Exasol communicates with an Exasol database via a TLS-secured connection.

Acceptance Criteria

1. Users can enable TLS through the R-Exasol API.
2. Communication between the underlying ODBC driver and the database is TLS encrypted.

Background

We need to get more detailed information about errors, reported by customers.

Acceptance Criteria

Add logging at function call level for critical parts of the code.

Using Linux Mint 17. Installed unixodbc 2.2.14

Here is the problem when I try to compile the package in RStudio.

> install_github("EXASOL/r-exasol")
Downloading GitHub repo EXASOL/r-exasol@master
Installing exasol
'/usr/lib/R/bin/R' --no-site-file --no-environ --no-save --no-restore CMD INSTALL  \
  '/tmp/RtmpLklwnf/devtools316dfe767b/EXASOL-r-exasol-9d23594' --library='/usr/local/lib/R/site-library' --install-tests 

* installing *source* package ‘exasol’ ...
** libs
gcc -std=gnu99 -I/usr/share/R/include -DNDEBUG      -fpic  -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -D_FORTIFY_SOURCE=2 -g  -c exasol.c -o exasol.o
gcc -std=gnu99 -shared -L/usr/lib/R/lib -Wl,-z,relro -o exasol.so exasol.o -L/usr/lib/R/lib -lR
installing to /usr/local/lib/R/site-library/exasol/libs
** R
** preparing package for lazy loading
** help
*** installing help indices
** building package indices
** testing if installed package can be loaded
Error in dyn.load(file, DLLpath = DLLpath, ...) : 
  unable to load shared object '/usr/local/lib/R/site-library/exasol/libs/exasol.so':
  /usr/local/lib/R/site-library/exasol/libs/exasol.so: undefined symbol: SQLFreeHandle
Error: loading failed
Execution halted
ERROR: loading failed
* removing ‘/usr/local/lib/R/site-library/exasol’
Error: Command failed (1)

Are there any plans to update the package to work with R3.6?

Background

Some methods of DBI API are not yet implemented and marked with TODO in the R code.

Acceptance Criteria

Fix all remaining TODO's.

Background

r-exasol implements the DBI Interface, but not all of the methods are working right now.
(I checked the first 3 of this list and they are not working.

Acceptance Criteria

Implement all remaining methods which are not working.

See #41
What was resolved in that ticket was adding some connection snippets to Rstudio.
What's not done yet is the ExaSol connection showing up in the connections pane when you connect to it via code or the wizard, for that the connections contract probably must be added to the library.
It's also possible this got fixed via the update of the DBI package for release 3.5.0

Background

Today the integration test are executed only under Linux, using the integration-test-docker-environment.
However, it be good to run the tests also under Windows and MacOsX.
For this purpose we need an external database instance available.

Acceptance criteria

1. Use https://github.com/exasol/terraform-aws-exasol-test-setup/ in a github actions to start the cluster and execute integration tests there.
2. The github actions must only run when requested (not on every push).

Situation

We should add the versioning with changelog. I think simple git tag should suffice, since r-exasol is installed using devtools::install_github.

Later we can decide if releasing to CRAN required.

Acceptance Criteria

• Releases are versioned

problem 1: if the first line is a comment, get error:
-- as;dlkaslkd

Error in if (toupper(regmatches(statement, gregexpr("^\w+", statement, :
argument is of length zero

problem 2:
if have with statement as first line, get no result (but no error)

Background

Currently, we forward the password as is to the odbc connection string.
However, if the password contains a ";", this will break the syntax.

See this discussion how to fix this.

Acceptance Criteria

Change the encoding of the password so that it will not break the ODBC connection string syntax if the password contains a ";":

1. Encapsulate the password by { and }: password="my_password" => password="{my_password}"
2. Replace any occurance of "}" in the password with: "}}"

Background

The nightly update of the docker images (the images are used to speed up the CI builds by caching the respective R environments) is not working correctly. The script which checks for available tags returns 1 if the tag does not exists.

Acceptance Criteria

Fix the script which checks for a specific tag of the docker image, and do not return 1 if the tag is not available.

Background

Currently we use the forked DBITest for running the integration tests: marcelboldt/DBITest. However, they depend on a very old version of the DBI package (0.3.1.9008), also currently we use a forked version of DBI in the test setup: marcelboldt/DBI.

dpblyr for example depends on version 1.0.0 of the DBI package.

Acceptance Criteria

Verify what it takes to use the standard DBITest package (https://github.com/r-dbi/DBItest). If this is not feasible, use our own fork of DBITest and make it compatible with the official version of DBI (https://github.com/r-dbi/DBI).

Background

Older versions of release-droid needed 3 workflows for the release process. In the latest release those workflows are optional.

Important: Need to remove both checksum workflows, because they depend each other.

Maybe a good idea would be to move the integration tests and upload into the upload_github_release workflow and acuatally upload the resulting tgz's to the release.
Clients can install then use the binary packages simply with install.packages, and don't need to install all the development packages.

Acceptance Criteria

1. Remove dummy workflows which actually do nothing.
2. Upload the binary tar.gz to Github
3. Upload the source tar.gz to Github

Background

With #79 we prepared the CRAN submission, and there is a Dockerfile and some script which facilitates the related CRAN compliance tests. It would be good to run those test with every submit in order to avoid adding new features which cause issues for new CRAN submissions.

Acceptance Criteria

Create a GH Actions (for each PR or when pushing to branch master) which build the Docker container under /tests/cran-submission and run the scripts on it. For that, the Dockerfile needs to be changed so that it runs the script automatically when starting the container.

Background

Currently any users of r-exasol need to compile the package on their machines, which require additional dependencies (RTools on Windows for example). It would be much easier for the users of this package to install the binary package from CRAN.
Note: CRAN only provides binary packages for Windows and MacOsX. For Linux, still the compilation will be required.

Acceptance Criteria

r-exasol as binary package on CRAN.

dbExecute fails at the dbGetRowsAffected step, because no function dbGetRowsAffected is registered for the returned EXAResult.

Workaround:
register dbGetRowsAffected for EXAResult:

setMethod("dbGetRowsAffected", signature("EXAResult"), function(res)
{
return(res$rows_affected)
}))

res$rows_affected seems to be always 0 though...

I have a R table with an empty column of type character. When I try to write this table to Exasol with dbWriteTable (to a non-existing table), an invalid CREATE TABLE statement is generated:

[EXASOL][EXASolution driver]syntax error, unexpected '-', expecting UNSIGNED_INTEGER_ [line 1, column 815] 

In addition: Warning message:
In max(nchar(as.character(x)), na.rm = TRUE) :
  no non-missing arguments to max; returning -Inf

In these cases, the function max(nchar(as.character(x)), na.rm = TRUE) returns -Inf and the generated CREATE TABLE statement tries to create a column of type VARCHAR(-Inf).

Workaround:
backup your R table, identify empty char columns, temporary fill them with something, execute dbWriteTable, create and save DDL statement on exasol, drop table on exasol, create new table on exasol with saved DDL and fix varchar lenghts, write backup table to exasol.

I am trying to connect exasol with R with the following code. The problem occurs with Mac OS.

library(RODBC)
library(exasol)

con <- dbConnect("exasol", **)
 # removed the connection credentials 

df1 <- dbGetQuery(con,paste("query", sep=""))
 # removed the query

Error in try(.Call(C_asyncRODBCQueryFinish, slot, 1)) : 
  Unknown ODBC error
Error in exa.readData(conn, statement, ...) : 
  Could not allocate SQLAllocHandle (-2)
Error in exa.readData(conn, statement, ...) : Unknown ODBC error`

My system is Mac OS "x86_64" . Please can you help resolving this. ?

• Fix or remove not working status badges

Background

We want to move from Travis to GitHub Actions.

Acceptance Criteria

1. Replace Travis files with working Github Actions
2. Ideally it should be possible to run tests easily on local environment -> Implement environment in Docker image

Just a quick question.
Is the connection between the client and server encrypted when using this driver ?

Hi all,

We're seeing a weird error, which happens quite frequently, however I cannot seem to find a pattern for it:

Error in odbcGetInfo(con) : argument is not an open RODBC channel

In the warnings (I guess coming from the Exasol RODBC library) I see the following:

Calls: myFunction ... <Anonymous> -> <Anonymous> -> EXANewConnection -> odbcGetInfo
In addition: Warning messages:
1: In odbcDriverConnect(con_str) :
[RODBC] ERROR: state 08S01, code -1, message [unixODBC][EXASOL][EXASolution driver]No server listening.
2: In odbcDriverConnect(con_str) :
[RODBC] ERROR: state 08S01, code -1, message [unixODBC][EXASOL][EXASolution driver]No server listening.
3: In odbcDriverConnect(con_str) : ODBC connection failed

Interestingly, this happens only when I'm trying to write data to the warehouse and only with the r-exasol package, whereas python package seems to be working fine.

Any help appreciated!

Background

Currently, whenever any Roxygen2 documentation was changed, the developer must call manually devtools::document() in order to update both the .Rd files as also the NAMESPACE file. It's easy to forget this!

Acceptance Criteria

Implement a GH Actions which calls devtools::document() and verifies that the git workspace remains clean. If git detects any changes, it means that the submitter forgot to trigger the documentation update, and it must fail the GH Actions.

Hi,
I am trying to run the following code:

library(RODBC)
library(exasol)

drv <- dbDriver("exasol") **PASS**
con <- dbConnect(drv, exahost = "host", uid = "my_id", pwd ="my_pass", schema = "SCHEMA") .  **PASS**
dbListTables(con) **Throws an error**

error:

Error in try(.Call(C_asyncRODBCQueryFinish, slot, 1)) :
Unknown ODBC error
Error in exa.readData(conn, qstr, ...) :
Could not allocate SQLAllocHandle
Error in exa.readData(conn, qstr, ...) : Unknown ODBC error

I am using mac, os 10, Exasol ver 5.17, and am able to access the DB from the Exaplus and see tables etc..

thanks!
Shahar

Hello, I am trying to install r-exasol on my mac but the compilation fails. It installs fine on my windows machine.

Below is the error

> devtools::install_github("EXASOL/r-exasol")
Downloading GitHub repo EXASOL/r-exasol@HEAD
✓  checking for file ‘/private/var/folders/q7/lvjhd0sx3fd9mqs1h7mlt__40000gn/T/RtmpcQFvyB/remotes20c79c4fb7d/exasol-r-exasol-3784b3c/DESCRIPTION’ ...
─  preparing ‘exasol’:
✓  checking DESCRIPTION meta-information ...
─  cleaning src
─  installing the package to process help pages
         -----------------------------------
─  installing *source* package ‘exasol’ ...
   ** using staged installation
   CPPFLAGS:-I/usr/local/include
   CPPFLAGS:-I/usr/local/include -I. 
   checking whether the C++ compiler works... yes
   checking for C++ compiler default output file name... a.out
   checking for suffix of executables... 
   checking whether we are cross compiling... no
   checking for suffix of object files... o
   checking whether we are using the GNU C++ compiler... yes
   checking whether clang++ -mmacosx-version-min=10.13 -std=gnu++14 accepts -g... yes
   checking how to run the C++ preprocessor... clang++ -mmacosx-version-min=10.13 -std=gnu++14 -E
   checking whether we are using the GNU C++ compiler... (cached) yes
   checking whether clang++ -mmacosx-version-min=10.13 -std=gnu++14 accepts -g... (cached) yes
   configure: creating ./config.status
   config.status: creating src/Makevars
   ** libs
   clang++ -mmacosx-version-min=10.13 -std=gnu++14 -I"/Library/Frameworks/R.framework/Resources/include" -DNDEBUG -I/usr/local/include -I.  -I'/Library/Frameworks/R.framework/Versions/4.0/Resources/library/testthat/include' -I/usr/local/include   -fPIC  -Wall -g -O2  -c r_exasol/connection_context.cpp -o r_exasol/connection_context.o
   In file included from r_exasol/connection_context.cpp:1:
   In file included from ./r_exasol/connection_context.h:5:
   ./r_exasol/external/sql.h:16:10: error: 'sql.h' file not found with <angled> include; use "quotes" instead
   #include <sql.h>
            ^~~~~~~
            "sql.h"
   ./r_exasol/external/sql.h:17:10: fatal error: 'sqlext.h' file not found
   #include <sqlext.h>
            ^~~~~~~~~~
   2 errors generated.
   make: *** [r_exasol/connection_context.o] Error 1
   ERROR: compilation failed for package ‘exasol’
─  removing ‘/private/var/folders/q7/lvjhd0sx3fd9mqs1h7mlt__40000gn/T/RtmpcjyrBJ/Rinst211d110fa934/exasol’
         -----------------------------------
   ERROR: package installation failed
Error: Failed to install 'exasol' from GitHub:
  System command 'R' failed, exit status: 1, stdout + stderr (last 10 lines):
E>          "sql.h"
E> ./r_exasol/external/sql.h:17:10: fatal error: 'sqlext.h' file not found
E> #include <sqlext.h>
E>          ^~~~~~~~~~
E> 2 errors generated.
E> make: *** [r_exasol/connection_context.o] Error 1
E> ERROR: compilation failed for package ‘exasol’
E> * removing ‘/private/var/folders/q7/lvjhd0sx3fd9mqs1h7mlt__40000gn/T/RtmpcjyrBJ/Rinst211d110fa934/exasol’
E>       -----------------------------------
E> ERROR: package installation failed

Is this a bug or Is there something I need to do additionally for mac?

Thanks

Background

Invoking dbConnect(EXAConnection) cannot connect to ODBC.

Most likely problem is here: All odbc keys and values are converted to upper case, including uid and password!

Acceptance Criteria

Invoking dbConnect() with existing connection must work.

dbGetQuery and dbExecute cannot execute statements that start with a comment. For example:

dbExecute(con, statement = '/*comment*/drop table blah.CARS')
Error in if (stmt_cmd == "SELECT") { : argument is of length zero

dbGetQuery(con, statement = '/*comment*/select * from blah.CARS')
Error in if (toupper(regmatches(statement, gregexpr("^\w+", statement, :
argument is of length zero

Hi,

when I want to install from github R gives me this error message

devtools::install_github("EXASOL/r-exasol") Downloading GitHub repo EXASOL/r-exasol@HEAD Error in utils::download.file(url, path, method = method, quiet = quiet, : cannot open URL 'https://api.github.com/repos/EXASOL/r-exasol/tarball/HEAD'

Session Info:

R version 3.5.1 (--) Platform: x86_64-pc-linux-gnu (64-bit) Running under: Ubuntu 16.04.6 LTS Matrix products: default BLAS/LAPACK: /usr/lib/libopenblasp-r0.2.18.so
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 methods base
other attached packages: [1] Rook_1.1-1 RJSONIO_1.3-1.2 Cairo_1.5-6
loaded via a namespace (and not attached): [1] Rcpp_1.0.1 magrittr_1.5 usethis_1.6.1 devtools_2.3.1 [5] pkgload_1.0.2 R6_2.4.0 brew_1.0-6 rlang_0.4.7 [9] fansi_0.4.0 tools_3.5.1 pkgbuild_1.1.0 sessioninfo_1.1.1 [13] cli_2.0.2 withr_2.1.2 ellipsis_0.3.1 remotes_2.2.0 [17] assertthat_0.2.1 digest_0.6.19 rprojroot_1.3-2 crayon_1.3.4 [21] processx_3.4.3 callr_3.4.3 fs_1.3.1 ps_1.3.0 [25] codetools_0.2-16 curl_3.3 testthat_2.3.2 memoise_1.1.0 [29] glue_1.4.1 compiler_3.5.1 desc_1.2.0 backports_1.1.4 [33] prettyunits_1.0.2

Any idea?

Best,

David

if (length(field_types != ncol(data))) line 1376 in r-exasol/R/EXADBI.R will always be TRUE, should be if (length(field_types) != ncol(data)).

Calling exa.writeData multiple times in a loop cases socket error.
For example, the following code crashed at iteration 4065.

require(RODBC)
require(exasol)
C <- odbcConnect("exasolution")
for(i in 1:5000) {
  df <- data.frame( ... single row of some data ... )
  exa.writeData(C, df, "mytable")
}

$ uname -s -r -v -m -p -i -o
Linux 3.13.0-65-generic #106-Ubuntu SMP Fri Oct 2 22:08:27 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

$ cat /proc/meminfo 
MemTotal:       32896676 kB
MemFree:        14085916 kB
Buffers:          133672 kB
Cached:          8216236 kB
SwapCached:         1908 kB
Active:         13122696 kB
Inactive:        5180692 kB
Active(anon):    9936752 kB
Inactive(anon):    16780 kB
Active(file):    3185944 kB
Inactive(file):  5163912 kB
Unevictable:           0 kB
Mlocked:               0 kB
SwapTotal:       2093052 kB
SwapFree:        2075980 kB
Dirty:                 0 kB
Writeback:             0 kB
AnonPages:       9951932 kB
Mapped:            32472 kB
Shmem:                52 kB
Slab:             269212 kB
SReclaimable:     248576 kB
SUnreclaim:        20636 kB
KernelStack:        1160 kB
PageTables:        23472 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:    18541388 kB
Committed_AS:   10122864 kB
VmallocTotal:   34359738367 kB
VmallocUsed:       57396 kB
VmallocChunk:   34359669400 kB
HardwareCorrupted:     0 kB
AnonHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:    33570816 kB
DirectMap2M:           0 kB

In order to receive a resultset, the DB cluster currently needs to open a (HTTP) connection to the client. This is not possible e.g. if the client is behind NAT gateway or a restrictive firewall.
Solution: implement a fallback via EXPORT TO LOCAL CSV, which uses a proxy inside the cluster to which the client can connect.

Something like the following call fails: dbListTables(exaconn, schema="RDEMO")
The reason is that readData is called with a SQL statement that filters for table_schema = "RDEMO" in this case, producing an error due to double quoted schema name.

Situation

There are many issues reported by the lintr. They should be addressed so that the codebase keeps consistent style.

Acceptance Criteria

• Lintr issues are addressed
• Suppressions are added if needed

Basic Example to use dbplyr:

devtools::install_github("exasol/r-exasol")
install.packages("dbplyr")
install.packages("tidyverse")
install.packages("dplyr")

library(exasol)
library(dplyr)
library(dbplyr)
library(tidyverse)
con <- dbConnect("exa", exahost = "172.23.0.2:8888", uid = "sys", pwd = "exasol")
dbListTables(con)
mytable <- tbl(con,in_schema("TEST", "COMP1")) %>% filter(IDX<10) %>% select(IDX, CHAR1_1) %>% show_query() %>% collect()

Situation:

• currently, it complains that the function dbHasCompleted is not available for EXAResult

Fix:

• add this function

dbListTables(conn = db_con, schema = "SCHEMA_NAME") gives error:

Error in exa.readData(conn, qstr, ...) : Could not read header.
Error in exa.readData(conn, qstr, ...) :
42000 -6811776 [EXASOL][EXASolution driver]object "SCHEMA_NAME" not found [line 1, column 82]

Background

Project does not contain changelog/developer guide/...

Acceptance Criteria

• Changelogs
• First version of Developer Guide

Is there an example on how to add an ExaSol connection to the connections pane?

Background

Currently the implementation of r-exasol uses ODBC as the underlying communication protocol with the database. This however has several drawbacks:

• Correct installation and configuration of ODBC driver and DSN
• On MacOsX there is a compatibity issue with the binary RODBC package on CRAN
• ...

Switching to Websockets could resolve some of the issues.

Background

In preparation of #56 we need to refactor the C code, and support C++ unit test.

Acceptance Criteria

The r-exasol library should be prepared to implement C++ unit tests and can be implemented in C++.

I need 3.3.3 to install devtools, but then I get

> devtools::install_github("EXASOL/r-exasol")
...
Installation failed: Command failed (1)

Any ideas?

> devtools::session_info()
Session info ----------------------------------------------------------------------------------------------------------------------------------
setting value
version R version 3.3.3 (2017-03-06)
system x86_64, mingw32
ui RStudio (1.1.383)
language (EN)
collate English_United States.1252
tz America/New_York
date 2017-12-07

Packages --------------------------------------------------------------------------------------------------------------------------------------
package * version date source
base * 3.3.3 2017-03-06 local
curl 3.0 2017-10-06 CRAN (R 3.3.3)
datasets * 3.3.3 2017-03-06 local
DBI * 0.7 2017-06-18 CRAN (R 3.3.3)
devtools 1.13.4 2017-11-09 CRAN (R 3.3.3)
digest 0.6.12 2017-01-27 CRAN (R 3.3.3)
git2r 0.19.0 2017-07-19 CRAN (R 3.3.3)
graphics * 3.3.3 2017-03-06 local
grDevices * 3.3.3 2017-03-06 local
httr 1.3.1 2017-08-20 CRAN (R 3.3.3)
memoise 1.1.0 2017-04-21 CRAN (R 3.3.3)
methods * 3.3.3 2017-03-06 local
R6 2.2.2 2017-06-17 CRAN (R 3.3.3)
stats * 3.3.3 2017-03-06 local
tools 3.3.3 2017-03-06 local
utils * 3.3.3 2017-03-06 local
withr 2.1.0 2017-11-01 CRAN (R 3.3.3)

On my Mac, the connection seems to work, but when I try to receive data (exa.readData) it fails to allocate an handle.

Hi, is it necessary to have R dependence of >= 3.3.0? thanks!

see also tgouhier/biwavelet#22

We need a dummy EXASOL odbc driver that would simulate the ODBC communication.
I tried to use SQLITE odbc driver, but SQLITE does not support EXPORT SQL command.

Otherwise, it is hard to write proper unit tests.