OHDSI Home | Forums | Wiki | Github

AchillesWeb: Complications in exporting data to json file

Developers
#1

[Continuing to discuss my efforts to setup Achilles under a new topic, because i’m currently tackling a new set of issues]

I am now able to connect to the postgresql database, and interact with it using the database connector.
When I execute:

achillesResults <- achilles (connectionDetails, cdmDatabaseSchema=“public”, resultsDatabaseSchema=“public”, sourceName="", smallcellcount=1, cdmVersion=“5”)

The following results are displayed:

Connecting using PostgreSQL driver
Executing multiple queries. This could take a while
|======================================================================| 100%
Analysis took 1.31 secs
Done. Achilles results can now be found in public
Executing Achilles Heel. This could take a while
|======================================================================| 100%
Analysis took 9.44 secs
Done. Achilles Heel results can now be found in public

Next, I try to execute:

exportToJson(connectionDetails, “public”, “public”, “/Users/snkasthu/Achilles/data/”)

Unfortunately, this gives me:

Generating condition treemap
| | 0%Error executing SQL: Error in rJava::.jcall(s, “Ljava/sql/ResultSet;”, “executeQuery”, as.character(query)[1]): org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = integer
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 1075

As far as I can read, does this mean that there’s an varchar value thats supposed to be an integer in my concept table? I’m especially suspicious of the concept_code (character varying(50))…

#2

The condition treemap requires that ACHILLES would have generated results
from the CONDITION_OCCURRENCE table. If that table is null, it is possible
that the export could throw an error.

#3

The error is definitely saying that you are trying to compare a ‘character varying’ column with an int column. I’m not sure, but I think the achilles script will log the SQL statement that it attempted to execute. if not, we need to look at this query:
https://github.com/OHDSI/Achilles/blob/master/inst/sql/sql_server/export_v5/condition/sqlConditionTreemap.sql

This is the mssql dialect, so it needs to be translated before executing on postgres. But if it is a column type, it’s possible that your cdm schema isn’t per spec and you have different column definitions than expected.

-Chris

#4

Ah I think I may have it: I noticed you ran achilles with cdmVersion=“5”, but you didn’t do the same with exportToJson(). The vocabulary_id is an int in the v4, but a varchar in v5. So, the int = varchar() is probably the vocabularyId = 8 in v4 but ‘Meddra’ in v5 (making up the v4 vocabulary id!). Anyways, make sure you are running in cdmv5 mode.

-Chris

#5

Great catch Chris, spot on!

#6

@Chris_Knoll, you were right!!! I was able to resolve this problem by following what you said :sunny:

Next, I tried to setup the datasources.json file.
As instructed, I created a json file under the data folder. The contents of the file were simply:

{ “datasources”:[ { “name”:“My Sample Database”, “folder”:“sample” } ] }

Which make sense, because of my file structure, which is:

Next, I setup tomcat, and launched the index.html page via localhost.
This displays a “loading…” message, together with a dropdown list of all reports, and an empty drop down for “data sources”.
Using the javascript debug console, I see the following error message:
"Uncaught TypeError: Cannot read property ‘length’ of undefined achilles.js 598

Looking at the error’d line, apparently it seems that achilliesWeb cannot find my data sources…

#7

Hmm, this is strange, the folder looks correct and that json content also looks correct. I agree that line 598 it looks like the request completed (successfully even) and it was trying to iterate over the json array.

If you can open up the chrome debugger console and refresh the screen, you should see all the the network requests (under the network tab) and one of them should be to access /localhost/data/datasources.json. You can click on that file and see what the content is. Would you mind doing this and letting me know if it returned the file you expected?

#8

Hi @Chris_Knoll,

Thank you! So I tried this out, and this is what I see:



I can’t seem to find a data file that’s being returned… although I do receive a 200 OK response. Hmmm… :frowning:

#9

Ok, now it’s time to get a little dirtier: we can set a breakpoint on the line that’s failing and we can try to understand why the json file was loaded with 200: OK, we can see the content, and the content is an array, so why would we get 'proprty ‘length’ of undefined? This means that the object that we are looping over (the datasources proprty of that result JSON) is not an array. It clearly is, has 1 element in it, and that one element has a name and a folder property. Strange!

To set a breakpoint you open up debugger console like you did, but go to the sources tab. Find the achilles.js file under js/app/achilles.js. Go to line 598, set a breakpiont there:

for (i = 0; i < root.datasources.length; i++) {

Reload the page, and you should see that line show up in the debugger. From here, we can inspect the variable ‘root’. This is what was returned from the server (it’s the in-memory representation of the datasources.json file). The above error says that it’s NULL, but it shouldn’t be since we see the content was loaded from the server properly.

Let me know what you see, and I’ll try to continue to help you.

-Chris

#10

Thank you @Chris_Knoll, I do appreciate it!

So here’s what I found. Interestingly, the root variable (which i’m watching) contains data, but attempts to pick out root.datasources fail, and it shows as undefined. And so of course, root.datasources.length also failed.

Could it be because of my use of double quotes within the data sources.json file? Right now, I have

{ “datasources”:[ { “name”:“My Sample Database”, “folder”:“SAMPLE” } ] }

#11

Ok, so that explains it, and this is what is happening (and I’m not sure why this is, it seems like a browser issue?):

It thinks root is a string. When I set the same breakpoint on my side, and add a watch on ‘root’ it says ‘object’ next to it, and on your side, it’s showing it as a string. It could also be something related to the datasources.json file that you created…is it really UTF8 format? Did you surround the whole thign in “”? It doesn’t look like it because the raw result that we looked at for the datasources.json file does look perfect. One possiblity is that it’s not encoded utf8?

In any case, that’s the problem: It’s interpreting the datasources.json as a string not simply json text (whcih should be automatically parsed by the browser).

-Chris

#12

Something else: i’m going back to the first set of screenshots you sent me: the response headers for the request for localhost/data/datasources.json is not indicating that it is application/json. Since it’s not setting that header, the browser thinks it 's just text. YOu need to figure out how to tell your webserver to indicate that .JSON files are application/json.

see:

#14

Hi everyone, I’m happy to report that I was able to solve the problem. Basically, we needed to add the JSON mime type into the tomcat web.xml file, and everything works :sunny:

Thank you @Chris_Knoll and @Patrick_Ryan for helping us through this process :smile:
Its good to have a nice first pass working :smile:

t