Found more than one class "Microsoft SQL Server" in cache; using the first, from namespace '.GlobalEnv'

[ablack3]

I'm seeing Found more than one class "Microsoft SQL Server" in cache; using the first, from namespace '.GlobalEnv' printed a whole lot.

I think this is because the "Microsoft SQL Server" class is defined in two different packages that I have installed, DatabaseConnector and odbc.

My sense is that it is not a good idea to export a class that is also exported by another package but I could be wrong.

Defintion in DatabaseConnector:

DatabaseConnector/R/DBI.R

Line 68 in 5176e98

setClass("Microsoft SQL Server",

DatabaseConnector/NAMESPACE

Line 51 in 5176e98

exportClasses("Microsoft SQL Server")

Defintion in odbc:
https://github.com/r-dbi/odbc/blob/7cdeac8af11b5d304493dcdda82c3d4f63ca3452/R/db.R#L98

Does not look like odbc exports this class though...
https://github.com/r-dbi/odbc/blob/main/NAMESPACE

All I know for sure is that this message is getting printed a lot.

[schuemie]

Yes, I looked at this. Unfortunately, dbplyyr doesn't nicely export the class, it just creates it at runtime, so we can't inherit from that. I didn't see it in odbc, so maybe we can inherit from that. Ideally, there would be a central class that we'd all inherit from.

[ablack3]

Ok just wanted to report this. I'm not sure if it causes any real problems. Feel free to close it if there's nothing to be done.

[ablack3]

DatabaseConnector is overwriting another package's class if I'm understanding this error correctly. Just having DatabaseConnector installed is causing issues for users connecting to SQL Server with odbc.

[schuemie]

Weird. I see the warning ("Found more than one class..."), but not the error message, and I can use the db object just fine.

I'll try to see if I can mimic odbc's trick of dynamically creating the class (as done here). Seems less nice than a global static class definition, but may solve this problem.

[schuemie]

I have a first attempt in the dynamic_inheritance branch. The warning (and probably error) should be gone when using odbc.

The error does return when DatabaseConnector::connect() has been called prior to using odbc in which case the Microsoft SQL Server class has been dynamically defined by DatabaseConnector. Not yet sure what to do about that.

Could you give the current version in dynamic_inheritance a try? (Check is currently throwing some warnings about missing documentation, but should not prevent testing)

[ablack3]

What if we tried adding a DatabaseConnector translation method to dbplyr? It could do the same thing as sql server but wouldn't overwrite the class.

The idea would be for dbplyr to generate generic OHDSI-SQL which would then get translated by sql render?

I'll try the dynamic inheritance branch.

[schuemie]

I like the idea of adding a OhdsiSql backend to dbplyr. I'm also thinking about the future of DatabaseConnector in general. I would like it to become more and more lightweight, as the DBI drivers are becoming more and more mature and consistent with each other. DatabaseConnector should just be there to fix the last annoying incompatibility issues between drivers, not duplicate what is already there. So perhaps we may not need an OhdsiSql backend in the end (this dynamic inheritance might allow us to use the various native backends instead of SQL translation). Just thinking out loud...

[ablack3]

Yea. we've had pretty good success using odbc in Darwin but there are still some annoying differences between databases. But overall it's good. I've had a lot of issues using odbc on Oracle so it would be nice if we could use DatabaseConnector's DBI interface for Oracle and maybe snowflake as well. Basically users can just plug in whatever DBI driver user's want to use including but not limited to DatabaseConnector.

Probably one of the biggest differences between the dbplyr translation and Sqlrender has been quoting of identifiers in SQL.

Sqlrender has to quote at least one cdm column...

I've been testing cross platform code a lot lately so getting some good experience with that. Not so easy! 😄

[ablack3]

This issue seems to be fixed in the dynamic_inheritance branch. Thank you!!

library(DatabaseConnector)
con <- DBI::dbConnect(odbc::odbc(),
                      Driver   = Sys.getenv("SQL_SERVER_DRIVER"),
                      Server   = Sys.getenv("CDM5_SQL_SERVER_SERVER"),
                      Database = Sys.getenv("CDM5_SQL_SERVER_CDM_DATABASE"),
                      UID      = Sys.getenv("CDM5_SQL_SERVER_USER"),
                      PWD      = Sys.getenv("CDM5_SQL_SERVER_PASSWORD"),
                      TrustServerCertificate="yes",
                      Port     = 1433)

DBI::dbDisconnect(con)

^{Created on 2023-08-09 with reprex v2.0.2}