Hello everyone,
My apologies for the longest post
I hope someone can help me out. I recently mapped IeMR data to condition_occurence OMOP table so I re ran the script as below. But not working as expected.
Error 1. After running the SQL generated from Achilles (localhost). I get no errors but no data in the result table. Again in one of the tables named [OMOPResults].[dbo].[achilles_results]. in the Stratum1 - I see “ERROR: no runtime found in log file” .
Error 2. error loading report in atlas
Any leads will be highly appreciated. Below are the steps I followed
I deleted the result database and recreated as below
create database results
create USER webapi_sa for login webapi_sa
GRANT ALL TO webapi_sa
–TRUNCATE TABLE WEBAPI.ACHILLES_CACHE
–TRUNCATE TABLE WEBAPI.CDM_CACHE;
Step 1 : > C:\Program Files\Apache Software Foundation\Tomcat 8.5\bin>startup – to start tomcat
> Connect Postgres
Open a command prompt from your postgres bin folder directory and log in to your PostgreSQL server using the superuser account.
C:\Program Files\PostgreSQL\15\bin>psql -U postgres
Enter the Superuser Password: postgres
step 2 :When a new table is mapped from IeMR to OMOP. We need to rerun the below query in RGui
#Database Connector
install.packages(“DatabaseConnector”)
run the following once in RStudio
library(DatabaseConnector)
Create the connection details
connectionDetails ← createConnectionDetails(
dbms=“sql server”,
user="*",
password="",
server=“localhost”,
port=“1433”,
pathToDriver=“C:\Program Files (x86)\Java\JDBC”,
extraSettings =";databaseName=OMOP;integratedSecurity=false;encrypt=true;trustServerCertificate=true"
)
Establish the connection
con ← connect(connectionDetails)
Check if the connection is valid
dbIsValid(con)
if (!require(“remotes”)) install.packages(“remotes”)
library(Achilles)
connectionDetails ← createConnectionDetails(
dbms = “sql server”,
user = “",
password = "”,
server = “localhost”,
port = “1433”,
pathToDriver = “C:\Program Files (x86)\Java\JDBC”,
extraSettings = “;databaseName=OMOP;integratedSecurity=false;encrypt=true;trustServerCertificate=true”
)
Achilles::achilles(
cdmVersion = “5.4”,
connectionDetails = connectionDetails,
cdmDatabaseSchema = “omop.dbo”,
resultsDatabaseSchema = “results.dbo”,
createTable = TRUE, createIndices = TRUE, optimizeAtlasCache=TRUE
)
Step 3: To execute the SQL generated from Achilles (localhost) and update the SQL files, follow these steps:
Open a web browser and paste the following URL:
http://localhost:8080/WebAPI/ddl/results?dialect=sql server&schema=results&vocabSchema=omop&tempSchema=temp&initConceptHierarchy=true
The above URL will generate the SQL commands.
• Copy the generated SQL commands and save to word document. It will look like the below screenshot.
Perform a global search and replace all occurrences of "results." with "results.dbo."
Perform a global search and replace all occurrences of "omop." with "omop.dbo."
•Update the SQL file to ensure the database and schema are correctly populated by running the following:
•Save the file and copy this.
•Open Microsoft SQL Server Management Studio (SSMS), connect to your SQL database named results. Paste the commands and execute it.
•Once the execution is complete go to the next step
Ps note : The Code generated on local host saved in OMOP installation folder after the replacing results to results.dbo
Step 4: To refresh the WebAPI Source and Source_daimon tables in PostgreSQL:
***** make sure to change the sourcename and table_qualifier according to your database amd schema
o Open your PostgreSQL database management tool (e.g., pgAdmin).
o Connect to the PostgreSQL database where the WebAPI is installed.
o Execute the following SQL command to refresh the Source Source_daimon tables:
o Please ensure that the username and password are correctly updated and reflected in the command below.
truncate table webapi.source RESTART IDENTITY CASCADE;
truncate table webapi.source_daimon RESTART IDENTITY CASCADE;
INSERT INTO webapi.source (source_id, source_name, source_key, source_connection, source_dialect, is_cache_enabled)
SELECT nextval(‘webapi.source_sequence’), ‘omop.dbo’, ‘MY_CDM’, ‘jdbc:sqlserver://localhost:1433;databaseName=omop;integratedSecurity=false;encrypt=true;trustServerCertificate=true;user=webapi_sa;password=webapi$$sa123’, ‘sql server’, ‘N’;
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority)
SELECT nextval(‘webapi.source_daimon_sequence’), source_id, 0, ‘omop.dbo’, 0
FROM webapi.source
WHERE source_key = ‘MY_CDM’
;
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority)
SELECT nextval(‘webapi.source_daimon_sequence’), source_id, 1, ‘omop.dbo’, 1
FROM webapi.source
WHERE source_key = ‘MY_CDM’
;
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority)
SELECT nextval(‘webapi.source_daimon_sequence’), source_id, 2, ‘results.dbo’, 1
FROM webapi.source
WHERE source_key = ‘MY_CDM’
;
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority)
SELECT nextval(‘webapi.source_daimon_sequence’), source_id, 5, ’ OMOPTemp.dbo’, 0
FROM webapi.source
WHERE source_key = ‘MY_CDM’
;
Step 5 : Open Tomcat and connect to Atlas
Start Tomcat and open your web browser. Navigate to the following URL: "http://localhost:8080/atlas"