Error: nanodbc/nanodbc.cpp:1708: 42S01: [Simba][SQLEngine] (31738) Table or view already exists: MGSTDdrug_cohorts_set

[anallg]

Hi, I´m having the following issuer during the execution of the script:

Even though I make sure that the table does not exist before starting the execution, it still fails even after creating the table. I don't quite understand what the problem is, but the table contains data despite the error and seems ok:

Furthermore, if I continue with the step-by-step execution of the script, I encounter another error related to other table 2 statements later(removing temporary=TRUE argument because it generate a unused argument error ):

The table drug_cohorts also exists. I can´t understand where is the problem.

[tiozab]

Hi @anallg anallg, it looks like the renv did not deploy? In the version IncidencePrevalence package version that is used in this code, the temporarytable is still a used argument (it was only removed in the next version).
Please try again with the renv.

[tiozab]

@catalamarti do you think you can help with the first error in generateDrugUtilidationCohortSet? it does not look like a package version problem? it does not create the cdm$drug_cohorts object.

[anallg]

Hi @tiozab, I've never worked with renv before. Yesterday, it gave me problems with the path, so I proceeded without deploying renv. Today, I believe I am running it correctly. Exists a folder named renv in the working directory but the error persists when executing the code against the empty schema, so I'm sure the table doesn't exist. Can you tell me how to confirm if I'm running with renv? Bellow you can find the full log execution.
Please let me know if there's anything I can do to fix this issue.

_> renv::activate()

renv::restore()
It looks like you've called renv::restore() in a project that hasn't been activated yet.
How would you like to proceed?

1: Activate the project and use the project library.
2: Do not activate the project and use the current library paths.
3: Cancel and resolve the situation another way.

Restarting R session...

1
[1] 1

library("devtools")
Loading required package: usethis
library("odbc")
library("RPostgres")
library("DBI")
library("dplyr")

Attaching package: ‘dplyr’

The following objects are masked from ‘package:stats’:

filter, lag

The following objects are masked from ‘package:base’:

intersect, setdiff, setequal, union

library("dbplyr")

Attaching package: ‘dbplyr’

The following objects are masked from ‘package:dplyr’:

ident, sql

library("CirceR")
library("CDMConnector")

Attaching package: ‘CDMConnector’

The following object is masked from ‘package:dbplyr’:

in_schema

library("here")
here() starts at H:/MEGASTUDY/Incidence Prevalence/IncidencePrevalence Code
library("log4r")

Attaching package: ‘log4r’

The following object is masked from ‘package:base’:

debug

library("zip")

Attaching package: ‘zip’

The following objects are masked from ‘package:utils’:

unzip, zip

library("IncidencePrevalence")
library("CodelistGenerator")
library("tidyr")
library("ggplot2")
library("Capr")

Attaching package: ‘Capr’

The following object is masked from ‘package:ggplot2’:

unit

The following object is masked from ‘package:CDMConnector’:

attrition

Warning message:
In Sys.timezone() : unable to identify current timezone 'C':
please set environment variable 'TZ'

library("testthat")

Attaching package: ‘testthat’

The following object is masked from ‘package:tidyr’:

matches

The following object is masked from ‘package:dplyr’:

matches

The following object is masked from ‘package:devtools’:

test_file

library("DrugUtilisation")

Attaching package: ‘DrugUtilisation’

The following object is masked from ‘package:Capr’:

attrition

db <- DBI::dbConnect(XXXXXXXXXX)

parameters to connect to create cdm object

cdmSchema <- "OMOP3"
writeSchema <- "MEGASTUDY" # schema with writing permission
writePrefix <- "MGSTD" # combination of at least 5 letters + _ (eg. "abcde_") that will lead any table written in the cdm
dbName <- "OMOP BIFAP" # name of the database, use acronym in capital letters (eg. "CPRD GOLD")

Run the study

source("H:/MEGASTUDY/Incidence Prevalence/IncidencePrevalence Code/RunIncidencePrevalence.R")
Error in eval(ei, envir) :
nanodbc/nanodbc.cpp:1708: 42S01: [Simba][SQLEngine] (31738) Table or view already exists: MGSTDdrug_cohorts_set
In addition: Warning message:
cohort_name must be snake case, the following cohorts will be
renamed:

• c1_esterase_inhibitor -> c_1_esterase_inhibitor_

[tiozab]

@catalamarti, the database management system is "oracle", the DrugUtilisation package version : "0.4.1"
any idea what is going on when executing the GenerateDrugUtilisationCohortSet and this error is thrown (fyi: it is the first time a cdm object is created by the code, so there must be a problem there) ? nanodbc/nanodbc.cpp:1708: 42S01: [Simba][SQLEngine] (31738) Table or view already exists: MGSTDdrug_cohorts_set

[tiozab]

@anallg can you use another database connection for this study? oracle is not supported @edward-burn

[anallg]

@tiozab Sorry, I thought it was clear. My database is not Oracle, it's Databricks. This is my connection string:

db <- DBI::dbConnect(odbc::odbc(),
Driver = "Simba Spark ODBC Driver",
Host = "XXXXX",
Port = 443,
AuthMech = 3,
HTTPPath = "XXXXXXX",
Protocol = "https",
ThriftTransport = 2,
SSL = 1,
UID = "token",
PWD = "xxxxxxxxx",
catalog = "xxxxxxxxx"
)

[tiozab]

We got additional resources, @sebastiaan101 can you be of help and liaise with @anallg that would be great