We hope you'll join us for our 4/23 webinar on using data tables to apply reference ranges and AE codes in OC4. For more information and to register, visit https://register.gotowebinar.com/register/2882170018956684555

OpenClinica not extracting Some REAL or INT values

First off my  apologies if this is a bit repetitive. I had upgraded OpenClinica to version 3.3 runing on Postgres 9.3, Tomcat 7 and JDK 1.8. I have added the postgres  jdbc4 driver to the Tomcat lib directory as zwets from KCRI mentions at a forum thread available here.

My issue now come when I extract data from the databases. I have set the study as a multi-site study. Whenever I extract values, some sites', say siteA's,  data is totally missing. This is also not very consistent as some values for some participants as available on the extract.  The mostly affected values are integer and real values. Has anyone who has performed an upgrade to either version 3.3 or 3.4 from 3.1.3 realised a simillar problem?

I cannot pick any helpful answers from my Tomcat logs, even as I have lowered the log level to INFO. An error that seems common from the logs is as shown in the attached log file. Maybe someone might be able to get get helpful info from it.

Thanks in advance for your suggestions and advice.

Regards,
Caesar Olima


Comments

  • haenselhaensel Posts: 602 ✭✭✭
    Hi Caesar

    There is a related thread in this forum ("Data Export no longer functions after upgrade"). Postgres 9.x isn't officially supported yet.

    Regards,
    Christian
  • kristiakkristiak Posts: 1,338 ✭✭✭
    As you can see from this link, http://www.postgresql.org/docs/8.4/static/migration.html  there are some major changes in PGSQL code between the 8.4 and 9.x versions. This will require some changes in the OC code and as you have seen from Alice's comments this is work in progress. and I would advice you not to try this on your own! If you ever have an audit, this would get you into troubles!
  • CaesarOlimaCaesarOlima Posts: 42
    @haensel and @kristiak, Thanks all for the input. I had tested my systems before upgrading  to version 9.3. At the time of testing, everything seemed fine and issues currently reported were not spotted then. I now have the systems on version 9.3 of Postgres so will really need to get to the code to understand how it all works and possible ways of accomodating higher database version.

    Regards,
    Caesar
  • haenselhaensel Posts: 602 ✭✭✭
    Hi Caesar

    This is a very ambigous task that would be highly appreciated by the community and OpenClinica. If you need a helping hand for some topics just leave a note in this forum. It might be worth to create a new thread for this (e.g. OC modifications for Postgres 9.x).

    Regards,
    Christian
  • CaesarOlimaCaesarOlima Posts: 42
    @kristiak, you mentioned that there is a major PGSQL changes from 8.4 to 9.x. According to Developing of OpenClinica guideline, it is stated that "All the legacy code is written in preparedStatements using JDBC. and 90%
    of the existing classes use this. For example, if you have to deal with
    any of the objects such as Study or CRF, it would be easier to use the
    existing beans. However, if you have a new set of tables and have a
    transaction model attached to it. use the Hibernate/Spring approach.
    There are already existing implementation for hibernate part of the
    code..."

    Do you understand how this would affect the application setup if I decided to change in the POM.xml the dependency database version from postgres library version 8.1 to version 9.3 (my development database version)? Additionally, how does the Tomcat version I am running affect the applications functionality? I am really looking into understanding how OC works under the hood and would highly appreciate some community help/ideas on this.

    Regards,
    Caesar
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    Postgres is probably not the problem, new versions tend to add
    functionality and preserve old behaviour.

    The main issue I've seen with OC is the enterprise datamart code trying
    make sure plpgsql exists by dropping and creating it, but in later versions
    plpgsql is installed by default. There are a couple of other minor table
    ownership statements which don't make sense if the dB user isn't called
    clinica. All these changes are sitting in a recent pull request on GitHub.

    Are you likely hitting any memory limits? What is the file size like on the
    extracts where you are losing data?

    Like updating the jdbc jar, maybe swapping in the latest Saxon jars might
    help if things are going wrong in the transformation steps.
    @kristiak, you mentioned that there is a major PGSQL changes from 8.4 to
    9.x. According to Developing of OpenClinica guideline, it is stated that
    "All the legacy code is written in preparedStatements using JDBC. and 90%
    of the existing classes use this. For example, if you have to deal with
    any of the objects such as Study or CRF, it would be easier to use the
    existing beans. However, if you have a new set of tables and have a
    transaction model attached to it. use the Hibernate/Spring approach.
    There are already existing implementation for hibernate part of the
    code..."

    Do you understand how this would affect the application setup if I decided
    to change in the POM.xml the dependency database version from postgres
    library version 8.1 to version 9.3 (my development database version)?
    Additionally, how does the Tomcat version I am running affect the
    applications functionality? I am really looking into understanding how OC
    works under the hood and would highly appreciate some community help/ideas
    on this.

    Regards,
    Caesar
  • kristiakkristiak Posts: 1,338 ✭✭✭

    The following is a quote from PG "As a general rule, the internal data storage format is subject to change between major releases of PostgreSQL (where the number after the first dot changes). This does not apply to different minor releases under the same major release (where the number after the second dot changes); these always have compatible storage formats. For example, releases 8.1.1, 8.2.3, and 8.3 are not compatible, whereas 8.2.3 and 8.2.4 are. When you update between compatible versions, you can simply replace the executables and reuse the data directory on disk. Otherwise you need to back up your data and restore it on the new server."

    This eems to affect OpenClinica in a major way, I have never been able to run a 64 bit version of PGSQL i.e. versions > 9.

    So I understand that OC 3.4 won't run on PGSQL 9.3

  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    That text refers to the internal storage, and is relevant mostly to
    upgrades.

    The interface that OpenClinica (jdbc) uses and SQL doesn't tend to make
    incompatible changes, rather growing either by implementing more of the
    standard or adding extensions to it.

    That is, I could write "select * from study" and it will return the same
    set in 8.4 and 9.3, despite any internal storage differences. Which makes
    me think the data is being lost after it has left the database, somewhere
    in Java land.
This discussion has been closed.