search

exasol / r-exasol

The EXASOL package for R provides an interface to the EXASOL database.
http://www.exasol.com
BSD 3-Clause "New" or "Revised" License
22 stars 8 forks source link

Rstudio connections pane: Connection showing up #70

Closed pj-spoelders closed 2 years ago

pj-spoelders commented 3 years ago

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

tomuben commented 3 years ago

I did some research for that, and it looks like that the connection pane view is only supported for ODBC connections:

R-Studio says:

After successfully connecting to a database using odbc, the Connections pane will do the following: ...

So I believe, for DBI compliant packages (like r-exasol), the connection pane view is not supposed to work.

However, I found this library as workaround https://github.com/cran/connections and indeed it works. It seems the purpose of this library is exactly to fill the gap:

The main goal of connections is to integrate DBI-compliant packages with the RStudio IDE’s Connection Pane. Packages such as RPostgres, RSQLite, RMariaDB and bigrquery connect R to those databases, but do not provide a direct integration with the Connections Pane. connections reads the configuration of the connection and creates the integration with RStudio.

I will add a screenshot for demonstration. Unfortunately, the library status is experimental and we should think twice before using it. But, I see two options to continue:

  1. Create a dependency to the connections package and adjust the snippets so that they connect via that library (low effort)
  2. Understand how the library works and integrate the code which communicates with the connection pane into r-exasol

After some tests I discoverd that I can browse the database and tables, but opening a table throws an error.

Could not execute SQL: '42000' -1' [EXASOL][EXASolution driver]'.' character is not allowed within quoted identifiers [line 1, column 23] (Session: 1708638733866500096)'

tomuben commented 3 years ago

Screenshot from 2021-08-20 15-45-38

tkilias commented 3 years ago

@tomuben do you know what query raised the error for opening a table. It seems, it had put schema and table together into double quotes

tkilias commented 3 years ago

This is actually the original repo https://github.com/rstudio/connections, the cran repo is only a mirror

tkilias commented 3 years ago

FYI: https://cran.r-project.org/web/packages/rscontract/readme/README.html

tkilias commented 3 years ago

Actually, we only need to implement the following stuff in r-exasol if we prefer not to use other libraries.

https://rstudio.github.io/rstudio-extensions/connections-contract.html

It requires us to call some functions from the observer and provide a view callbacks.

tomuben commented 3 years ago

I discovered that using the lib has also one more issue: Our custom functions don't work with this connection wrapper. So I believe integrating the connection contract is the only feasible solution.