search

OHDSI / Achilles

Automated Characterization of Health Information at Large-scale Longitudinal Evidence Systems (ACHILLES) - descriptive statistics about a OMOP CDM database
https://ohdsi.github.io/Achilles/
130 stars 122 forks source link

"USE statement is not supported to switch between databases" when running on Azure SQL #514

Closed sdebruyn closed 3 years ago

sdebruyn commented 3 years ago

Expected behavior

Export should not have to switch to any other database

Actual behavior

When exporting to JSON I get the following error:

Connecting using SQL Server driver
Error in rJava::.jcall(statement, "V", "close") : 
  com.microsoft.sqlserver.jdbc.SQLServerException: USE statement is not supported to switch between databases. Use a new connection to connect to a different database.
Error in rJava::.jcall(metaData, "Ljava/sql/ResultSet;", "getCatalogs") : 
  com.microsoft.sqlserver.jdbc.SQLServerException: Broken pipe

Steps to reproduce behavior

connectionDetails <- createConnectionDetails(
  dbms="sql server", 
  server="secret.database.windows.net", 
  user="secret", 
  password='secret', 
  port="1433",
  extraSettings = sprintf("database=%s;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30", "secret"))

exportToJson(connectionDetails, 
             cdmDatabaseSchema = "omop_v5", 
             resultsDatabaseSchema = "omop_dq", 
             outputPath = "export", 
             compressIntoOneFile = TRUE)
sdebruyn commented 3 years ago

I think it's because of this line: https://github.com/OHDSI/Achilles/blob/5d60fa02db53fda776afdb7f9cc9a74a2e99e286/R/exportToJson.R#L105

sdebruyn commented 3 years ago

Apparently the schema params have to be in the format databaseName.schemaName

yradsmikham commented 3 years ago

Any luck with running the achilles function against your Azure SQL? I'm running into the exact error when I run:

achilles(connectionDetails = connectionDetails, cdmDatabaseSchema = "yvonne_dev_omop_db.dbo", resultsDatabaseSchema = "yvonne_dev_omop_db.results", vocabDatabaseSchema = "yvonne_dev_omop_db.dbo", sourceName = "OHDSI CDM V5 Database", cdmVersion = 5.3, numThreads = 1, runHeel = FALSE, outputFolder = "output")

with connectionDetails:

connectionDetails <- createConnectionDetails(dbms = "sql server", server = "yvonne-dev-omop-sql-server.database.windows.net", user = "admin", password = "password", port = "1433")