OHDSI Home | Forums | Wiki | Github

Is it possible to use odbc instead of jdbc with the OHDSI tools?

(Adam Black) #1

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.

Replacing ff with RSQLite?
(Martijn Schuemie) #2

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.

(Adam Black) #3

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.

(Martijn Schuemie) #4

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.

(Adam Black) #5

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.