DatabaseConnector connections fail when used with CDMConnector

[Gaelle-R]

Hello !

I'm using the package CDMConnector in the context of CDMOnboarding and DrugExposureDiagnostics packages, and I always get the following error that I don't understand with cdm_from_con() method :

I'm using R version : 4.1.0.

Thanks for your help !

[ablack3]

Hi @Gaelle-R,

Thanks for the error report. The package currently does not support DatabaseConnector as a driver although I'd like to add that. It works sometimes for some database but I have not fully tested it. I'll work on it.

In the meantime would you try connecting with the RPostgres package instead?

Your connection would look something like this.

con <- DBI::dbConnect(RPostgres::Postgres(),
                      dbname = Sys.getenv("CDM5_POSTGRESQL_DBNAME"),
                      host = Sys.getenv("CDM5_POSTGRESQL_HOST"),
                      user = Sys.getenv("CDM5_POSTGRESQL_USER"),
                      password = Sys.getenv("CDM5_POSTGRESQL_PASSWORD"))

cdm <- cdm_from_con(con, cdm_schema = Sys.getenv("CDM5_POSTGRESQL_CDM_SCHEMA"))
DBI::dbDisconnect(con)

[Gaelle-R]

Hi @ablack3,

Thanks for your reply, it works fine with RPostgres !

[siirsu]

Hello @ablack3,

I would like to test the IncidencePrevalence package and encountered a similar problem described above when attempting to use DatabaseConnector. I see in the thread that the package currently does not support DatabaseConnector, do you have any timelines on when do you think this can be added? I am working with Snowflake and installing ODBC drivers as a workaround for using the DBI connection does not seem to work. Do you have any recommendations for my case to be able to use this package?

Thank you in advance :)

[ablack3]

So you would like to used DatabaseConnector + CDMConnector on a spark database correct? I can try to add this for the next release in the next month or two.

[rfherrerac]

@ablack3 Hi Adam, I was trying to use IncidencePrevalence in Snowflake using DatabaseConnector. But I got this error: Error in CDMConnector::datediff(): , do you have a work around way?

Backtrace: ▆

1. ├─DrugUtilisation::generateDrugUtilisationCohortSet(...)
2. │ ├─DrugUtilisation:::applyLimit(...)
3. │ │ └─DrugUtilisation:::sumcounts(cohort)
4. │ │ ├─dplyr::pull(cohortCount(cohort), "number_records")
5. │ │ └─omopgenerics::cohortCount(cohort)
6. │ │ └─omopgenerics:::assertClass(cohort, "cohort_table")
7. │ │ ├─base::paste0(...)
8. │ │ └─base::paste0(base::class(x), collapse = ", ")
9. │ ├─DrugUtilisation:::trimEndDate(...)
   10. │ │ └─DrugUtilisation:::sumcounts(cohort)
   11. │ │ ├─dplyr::pull(cohortCount(cohort), "number_records")
   12. │ │ └─omopgenerics::cohortCount(cohort)
   13. │ │ └─omopgenerics:::assertClass(cohort, "cohort_table")
   14. │ │ ├─base::paste0(...)
   15. │ │ └─base::paste0(base::class(x), collapse = ", ")
   16. │ ├─DrugUtilisation:::trimStartDate(...)
   17. │ │ └─DrugUtilisation:::sumcounts(cohort)
   18. │ │ ├─dplyr::pull(cohortCount(cohort), "number_records")
   19. │ │ └─omopgenerics::cohortCount(cohort)
   20. │ │ └─omopgenerics:::assertClass(cohort, "cohort_table")
   21. │ │ ├─base::paste0(...)
   22. │ │ └─base::paste0(base::class(x), collapse = ", ")
   23. │ ├─DrugUtilisation:::requirePriorObservation(...)
   24. │ │ └─DrugUtilisation:::sumcounts(cohort)
   25. │ │ ├─dplyr::pull(cohortCount(cohort), "number_records")
   26. │ │ └─omopgenerics::cohortCount(cohort)
   27. │ │ └─omopgenerics:::assertClass(cohort, "cohort_table")
   28. │ │ ├─base::paste0(...)
   29. │ │ └─base::paste0(base::class(x), collapse = ", ")
   30. │ ├─DrugUtilisation:::requirePriorUseWashout(...)
   31. │ │ └─DrugUtilisation:::sumcounts(cohort)
   32. │ │ ├─dplyr::pull(cohortCount(cohort), "number_records")
   33. │ │ └─omopgenerics::cohortCount(cohort)
   34. │ │ └─omopgenerics:::assertClass(cohort, "cohort_table")
   35. │ │ ├─base::paste0(...)
   36. │ │ └─base::paste0(base::class(x), collapse = ", ")
   37. │ ├─DrugUtilisation:::erafyCohort(...)
   38. │ │ └─DrugUtilisation:::sumcounts(cohort)
   39. │ │ ├─dplyr::pull(cohortCount(cohort), "number_records")
   40. │ │ └─omopgenerics::cohortCount(cohort)
   41. │ │ └─omopgenerics:::assertClass(cohort, "cohort_table")
   42. │ │ ├─base::paste0(...)
   43. │ │ └─base::paste0(base::class(x), collapse = ", ")
   44. │ ├─omopgenerics::newCohortTable(...)
   45. │ │ └─omopgenerics:::assertClass(table, "cdm_table")
   46. │ │ ├─base::paste0(...)
   47. │ │ └─base::paste0(base::class(x), collapse = ", ")
   48. │ └─DrugUtilisation:::subsetTables(...)
   49. │ ├─dplyr::compute(...)
   50. │ ├─... %>% ...
   51. │ ├─dplyr::filter(...)
   52. │ └─PatientProfiles::addDemographics(cohort, sex = FALSE, age = FALSE)
   53. │ └─x %>% dplyr::compute()
   54. ├─dplyr::select(...)
   55. ├─dplyr::mutate(...)
   56. ├─dplyr::mutate(...)
   57. ├─dplyr::compute(.)
   58. ├─omopgenerics:::compute.cdm_table(.)
   59. │ ├─dplyr::compute(...)
   60. │ └─CDMConnector:::compute.db_cdm(...)
   61. │ └─CDMConnector:::.computeQuery(...)
   62. │ └─dbplyr::sql_render(x)
   63. │ ├─dbplyr::sql_render(...)
   64. │ └─dbplyr:::sql_render.tbl_lazy(...)
   65. │ ├─dbplyr::sql_render(...)
   66. │ └─dbplyr:::sql_render.lazy_query(...)
   67. │ ├─dbplyr::sql_build(query, con = con, sql_options = sql_options)
   68. │ └─dbplyr:::sql_build.lazy_select_query(query, con = con, sql_options = sql_options)
   69. │ └─dbplyr:::get_select_sql(...)
   70. │ └─dbplyr:::translate_select_sql(con, select)
   71. │ └─dbplyr::translatesql(...)
   72. │ └─base::lapply(...)
   73. │ └─dbplyr (local) FUN(X[[i]], ...)
   74. │ ├─dbplyr::escape(eval_tidy(x, mask), con = con)
   75. │ └─rlang::eval_tidy(x, mask)
   76. └─CDMConnector::datediff Run rlang::last_trace(drop = FALSE) to see 2 hidden frames.

[ablack3]

@rfherrerac,

You can try using odbc instead of DatabaseConnector. I'm working on getting DatabaseConnector working with IncidencePrevalence but until that is done you can try using the odbc driver. It should work.