OHDSI Home | Forums | Wiki | Github

How can I excute sql file in the "WebAPI\ddl\resuls"


Hi I’m Atlas 2.7.6 user in oracle.
I found out the “WebAPI\WEB-INF\classes\ddl\results” folder in deployed “WebAPI.war”.

It’s folder have Query that Query is excuted manually.

So! I wonder that how can I excute that’s Query automatically.


For example “heracles_period” table didn’t excute by migration.

So I excuted the “WebAPI\WEB-INF\classes\ddl\results\heracles_periods.sql” manually.
In that case, I should fix the Query Syntax to Oracle version.

(Ajit Londhe) #3

You can use the /ddl/results web service to get the full script, and then execute it in a SQL client.

Using your WebAPI endpoint, you can parameterize the SQL dialect, the results schema name, and the vocab schema name.

For instance, if I had a Redshift instance, and the results schema was named test and the vocab was in a schema named vocab5:



Thanks for your reply @Ajit_Londhe
but I’m so sorry, I can’t understand.

Are you say that I should excute the SQL in DBMS console?
Should I fix the "srttings.xml " to make “WebAPI.war”?

I can’t catch “web service”,“WebAPI endpoint” meaning you said.

(Ajit Londhe) #5

You just need to go to the address in your web browser to get the full results schema script.

In the example I gave, you would enter the following URL. Substitute in the appropriate values for the bolded sections.

WebAPI address/ddl/results?dialect=redshift&schema=test&vocabSchema=vocab5


Thank you for your kind @Ajit_Londhe :slight_smile:
I totally under stand!

" http://localhost:38080/WebAPI/ddl/results?dialect=oracle&schema=WEBAPI&vocabSchema=CDM"

It’s my test server. And I can see the converting SQL to oracle version like image.(oracle is my settings environment).


<it is well converted SQL to oracle version. >



<it is in incorrect parameter & original SQL>

It’s clear now.


Thanks to your tip , it was clearly.

But I have question one more,

It was excute by manual. the manual mean that the SQL should run by myself.
Is there any way to make it automatic?

(Ajit Londhe) #8

I don’t think WebAPI includes a step that executes the DDL script automatically – @Chris_Knoll can you confirm?

In the past, I have simply run this code any time we deploy a new CDM database to Atlas.


Thanks a lot @Ajit_Londhe !
Your frank answers have been very helpful.:+1:

(Chris Knoll) #10

WebAPI only manages it’s own database (where the meta data lives about which CDMs are defined, and what JDBC url to connect to, etc). CDMs are not automatically manged. You need to perform your own ETL and deploy a results schema yourself.