| OHDSI Home | Forums | Wiki | Github |
Is there a way to use the DatabaseConnector and SqlRender with an odbc connection instead of JDBC? If not how difficult would it be to add odbc support to DatabaseConnector?
I ask because I’m having trouble with JDBC SQL server integrated authentication from R running on Linux. odbc works fine though.
There currently is no support for ODBC. Adding it to DatabaseConnector is in theory possible, but since IMHO we’re already supporting too many platforms I would prefer not to.
There would be two ways this could be implemented: using a JDBC-ODBC bridge, or using the ROHDSI R package. I’ve had bad experiences with RODBC (specifically: returning only a subset of rows without warning), but that was 8 years ago.
The odbc and DBI packages are maintained by RStudio and work really well in my experience.
I thought I remembered hearing about another OHDSI package that implemented database connectivity (possibly using odbc) at the 2019 symposium but now I can’t remember what it was called or find it in the OHDSI github repo.
The OHDSI applications use a lot of ‘advanced’ database features (‘avanced’ meaning things not possible in ANSI SQL), and as a consequence we found that adding support for a new database platform requires a major investment in time to deal with all the idiosyncrasies of that platform. I don’t think ODBC will be different.
To me it makes more sense to work with the current functionality, and try and solve your current authentication problems.
That makes sense. I’m sure we will get JDBC working. Just to clarify ODBC isn’t a database platform. It is just a protocol for accessing databases similar to JDBC (at least that’s my understanding). Still, it would require changes to existing code which might not be worth the investment. It would not require changes to SQL code though since ODBC is just a way of getting SQL to the database. What ODBC would provide is a way to use the OHDSI R packages without needing to install Java. I think that organizations using Java in a production setting are now required to pay Oracle a yearly license fee for Java. Maybe this is not a big deal though since Atlas will always require a Java license anyway. Just thinking about the feasibility of removing the Java dependency from the OHDSI R packages.
I think the problems I’m experiencing with JDBC are more about using JDBC from R than with JDBC itself. If I run some simple SQL from a SQL client connected using JDBC it runs fine. When I run the same SQL from RStudio using JDBC to connect it takes forever to return and eventually I have to kill the R session. I will keep investigating and testing.
Welcome to the family! I don’t think there is any feasible solution to the integrated security issue on Linux/Mac at the moment. At least we’ve been struggling with that for a while. There is a way to bypass integrated security but then dates get messed up (https://github.com/OHDSI/DatabaseConnector/issues/58). Ended up with using Windows.