search

EHDEN / CatalogueExport

Exports the data from the OMOP-CDM that is necessary for the EHDEN Database Catalogue
Apache License 2.0
9 stars 6 forks source link

java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object - dropAllScratchTables #28

Closed scossin closed 3 years ago

scossin commented 3 years ago

Describe the bug The program ran successfully the first time, without errors. The error occured when I tried to re-run a query analysis with createTable=F

To Reproduce

CatalogueExport::catalogueExport(connectionDetails,
                                 cdmDatabaseSchema = cdmDatabaseSchema ,
                                 resultsDatabaseSchema = resultsDatabaseSchema ,
                                 vocabDatabaseSchema = vocabDatabaseSchema ,
                                 numThreads = 1,
                                 analysisIds = 430,
                                 cdmVersion = "5.3.0",
                                 createTable = F)

The error occurs because it tries to create a scratch table that already exists:

CREATE TABLE OMOP_COHORT.yjau2xbzs_tmpach_430

The solution is to remove the scratch tables but the first time the program ran, the function "dropAllScratchTables" was not called with numThreads = 1 even though dropScratchTables parameter was set to TRUE by default. It is the expected behavior ?

PRijnbeek commented 3 years ago

Thank that behaviour may be different on oracle temp tables, postgresql these are removed automatically at the end of the session. we could fix this by forcing dropScratchTables but need to check if that will work on all dbms

PRijnbeek commented 3 years ago

The scratch tables in the scratch schema are not used if you are not running one thread. It then uses temp tables in the database which are removed by disconnecting https://github.com/EHDEN/CatalogueExport/blob/a7cdbd86e916e43ba1b67714236fc1eaf57b6320/R/CatalogueExport.R#L443

This seems to also work like this for Oracle.

It could be you had a crash and therefore the temp table was not removed if the connection was still open.

PRijnbeek commented 3 years ago

I now force a disconnect in the catch statement when an error occurs in a query that should solve this probably