search

kumc-bmi / i2p-transform

i2b2 to PCORnet Common Data Model Transformation - requires i2b2 PCORnet ontology
Other
3 stars 5 forks source link

exit code from run-i2p-transform.sh is not reliable #65

Open dckc opened 7 years ago

dckc commented 7 years ago

It has often reported success to Jenkins even though it failed; for example, build 1​02 Dec 13, 2016 4:19 PM resulted in ORA-00933: ​SQL ​command ​not ​properly ​ended (fixed in ecca42224b6eb88d).

dckc commented 7 years ago

@njgraham does this mean you figured out how to get sqlplus to give reliable exit codes?

whenever oserror exit 9;
whenever sqlerror exit sql.sqlcode;

https://github.com/kumc-bmi/grouse/pull/6/files#diff-d6c1b161a9fc0ec54d3cd63ca166981aR50

cc @archaean @mprittie

njgraham commented 7 years ago

@dckc, unfortunately that doesn't result in reliable exit codes (at least in my experience/memory). Even with those statements sqlplus exits with 0 for some errors.

For example, an asktom article on trapping sqlplus SP2 errors says:

surprisingly - there are no capabilities to catch those and exit or report an error.

For reference: a list of SP2 errors. From our build log, I see several "successful" builds that actually failed with SP2 errors.

The asktom article above, linked to an article that indicates with 11g and up you can check to see if any errors occurred in your session so maybe we could do a 1/0 test on the row count from that table to achieve what we want.

I one that failed with PLS-00905: object PCORNET_CDM.PCORNETLOADER is invalid - I haven't found a specific reference yet as to why sqlplus would still return 0 for PLS-* errors but maybe the same method of checking that error table would work.

dckc commented 7 years ago

I wonder if using ant to run our sql scripts would be more reliable.

njgraham commented 7 years ago

Perhaps so. Not getting errors from sqlplus is really annoying and ends up wasting a lot of time and now I'm having the same problem with the GROUSE project. I didn't want to introduce lots of other dependencies by writing yet another SQL parser/executor but I admit I don't have experience with much more the Python/cx_Oracle for running SQL - maybe using ant would be straightforward and dependency free enough to be worth it.