Unhelpful error when using cdm_schema argument in cdm_from_con()

[pa-nathaniel]

Hi CDMConnector team, I love the package! I just ran into an error I hoped you could help me with:

I am able to run CDMConnector::cdm_from_con(conn = conn) just fine

conn <- DBI::dbConnect(...)
CDMConnector::cdm_from_con(conn = conn)

But when I include the write_schema argument using an internally defined schema name, I get the error:

conn <- DBI::dbConnect(...)
write_schema <- <internal_schema_name>

CDMConnector::cdm_from_con(conn = conn, write_schema = write_schema)

Error: Failed to initialise COPY: ERROR:  syntax error at or near "STDIN"
LINE 1: ...ne"."itrkh_test_table" ("chr_col", "numeric_col") FROM STDIN

I thought that I may have misspelled write_schema, but when I intentionally misspell cdm_schema I get the more information error:

conn <- DBI::dbConnect(...)
write_schema <- "asdf"

 CDMConnector::cdm_from_con(conn = conn, write_schema = write_schema)
Error: Failed to prepare query: ERROR:  schema "asdf" does not exist

Any tips on how to debug this? Thanks!!!

[ablack3]

So what is happening here is that CDMConnector cannot verify write access. When you pass in write_schema I try to write a small one row dataframe to the database using DBI::dbWriteTable(). This is essentially fail fast if write access is not possible. So can you use DBI::dbWriteTable to copy a dataframe to your database? If so will you post some example code that works for you?

I do need to improve that error message.

[ablack3]

Hi @pa-nathaniel,

Finally got around to this. Hopefully I have created a more informative error message when write access to the write_schema cannot be verified.

Here is a reprex. This will be in the next release.

Thanks for this issue! I hope all is well!

library(CDMConnector)
con <- DBI::dbConnect(duckdb::duckdb(), eunomia_dir())
cdm <- cdm_from_con(con, cdm_schema = "main", write_schema = "nonexistant_schema")
#> Error in `value[[3L]]()`:
#> ! Write access to `write_schema` could not be verified.  Do you have
#>   permissions to write data to schema `nonexistant_schema`?
#> 
#> `DBI::dbWriteTable` error message: Error: rapi_prepare: Failed to prepare query
#>   CREATE TABLE nonexistant_schema.lfkmj_test_table AS SELECT #1,#2 FROM
#>   _duckdb_write_view_qnjekqdaap Error: Catalog Error: Schema with name
#>   nonexistant_schema does not exist!
#> Backtrace:
#>     ▆
#>  1. └─CDMConnector::cdm_from_con(con, cdm_schema = "main", write_schema = "nonexistant_schema")
#>  2.   └─CDMConnector:::verify_write_access(con, write_schema = write_schema) at CDMConnector/R/cdm.R:117:5
#>  3.     └─base::tryCatch(...) at CDMConnector/R/cdm.R:410:3
#>  4.       └─base (local) tryCatchList(expr, classes, parentenv, handlers)
#>  5.         └─base (local) tryCatchOne(expr, names, parentenv, handlers[[1L]])
#>  6.           └─value[[3L]](cond)
#>  7.             └─cli::cli_abort("Write access to {.arg write_schema} could not be verified.\n        Do you have permissions to write data to schema {.code {paste0(write_schema[1:min(2, length(write_schema))], collapse = '.')}}?\n\n        {.code DBI::dbWriteTable} error message: {e}") at CDMConnector/R/cdm.R:419:7
#>  8.               └─rlang::abort(...)
DBI::dbDisconnect(con)

^{Created on 2024-01-17 with reprex v2.0.2}