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 Community Data Mart

CaesarOlimaCaesarOlima Posts: 42
edited March 2014 in Users Mailing List
Dear Fellow OC users and Devs, I have been trying get my hands on creating a Data Mart for the OpenClinica as explained at the link http://en.wikibooks.org/wiki/OpenClinica_User_Manual/CommunityDataMart. I had to make a couple of changes to the sql script as it was originally tested on Postgres 9.2 and I wanted to test this on studies running on ver. 8.4. Please find attached a copy of the final script. I have also installed an ODBC driver for postgres 8.4 but when I try to connect to the database from the Access Processor I get the error the application has been stopped due to runtime error. A screen short of my config setting is attached. Has anyone managed to get through setting up the Community Data Mart? If you happened to have bumped onto a similar issue, how did you manage to resolve it? Thanks in advance for your help and contributions. Kind regards, Caesar Olima
Post edited by bbaumann on
Tagged:

Comments

  • CaesarOlimaCaesarOlima Posts: 42
    Dear Fellow OC users and Devs,

    I have been trying get my hands on creating a Data Mart for the OpenClinica as explained at the link http://en.wikibooks.org/wiki/OpenClinica_User_Manual/CommunityDataMart. I had to make a couple of changes to the sql script as it was originally tested on Postgres 9.2 and I wanted to test this on studies running on ver. 8.4. Please find attached a copy of the final script. I have also installed an ODBC driver for postgres 8.4 but when I try to connect to the database from the Access Processor I get the error the application has been stopped due to runtime error. A screen short of my config setting is attached. Has anyone managed to get through setting up the Community Data Mart? If you happened to have bumped onto a similar issue, how did you manage to resolve it?

    Thanks in advance for your help and contributions.

    Kind regards,
    Caesar Olima
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    Hi Caesar,
    I think the problem with 8.4 was that a couple of convenient permissions functions were added in later versions, 'grant on all tables in schema' and one other that I can't remember. So Access stops dead as it hasn't got adequate rights.
    I'll see if there is another way to do it tomorrow and get back to you - I can't read sql files on my phone it seems.
    Alternatively, it is entirely possible to have a 9.2 server running alongside an 8.4, so long as it is on a different port (e.g. 5433).
    Once you're up and running it would be great to get some feedback - you're my first customer!
    Best regards,
    Lindsay
    On Dec 3, 2013 11:09 PM, "Caesar Olima" wrote:
    Dear Fellow OC users and Devs,

    I have been trying get my hands on creating a Data Mart for the OpenClinica as explained at the link http://en.wikibooks.org/wiki/OpenClinica_User_Manual/CommunityDataMart. I had to make a couple of changes to the sql script as it was originally tested on Postgres 9.2 and I wanted to test this on studies running on ver. 8.4. Please find attached a copy of the final script. I have also installed an ODBC driver for postgres 8.4 but when I try to connect to the database from the Access Processor I get the error the application has been stopped due to runtime error. A screen short of my config setting is attached. Has anyone managed to get through setting up the Community Data Mart? If you happened to have bumped onto a similar issue, how did you manage to resolve it?

    Thanks in advance for your help and contributions.

    Kind regards,
    Caesar Olima
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    Hi Caesar,
    The following 4 things work in 9.2 but don't work in 8.4. Unfortunately they affect both the warehousing sql script and the Access module called mod_get_study_list. I have attached fixed versions of these. You might be able to get away with using the non-installer version even if you only have Access runtime, but if you happen to need a new installer, let me know.
    1. ALTER ROLE IN DATABASE SET
    It seems to work if the 'IN DATABASE' is left out. Located in line 1307 of sql script, and line 93 of mod_get_study_list.
    2. GRANT SELECT ON ALL TABLES IN SCHEMA
    The tidiest workaround I could find is described at [1], which builds the necessary per-table statements in a query, and uses a simple function to execute them as they're made. Located in line 1311 of sql script, and line 95 of mod_get_study_list.
    3. CREATE ROLE option NOREPLICATION
    This can be removed since there is no replication in 8.4 anyway. Located in line 90 of mod_get_study_list.
    4. concat()
    Replaced with double-pipe || concatenation. Located in lines 544 and 556 of sql script.
    5. Other things
    I noticed in your screenshot you had the driver name as PostgreSQL35W - I use {PostgreSQL Unicode} (including braces), in case that makes any difference. Also it seems that I forgot to mention anywhere that you need to create a role called 'access_processor', but it looks like you figured that out.
    [1] http://www.depesz.com/2007/10/19/grantall/
    On 3 December 2013 23:36, Lindsay Stevens wrote:
    Hi Caesar,
    I think the problem with 8.4 was that a couple of convenient permissions functions were added in later versions, 'grant on all tables in schema' and one other that I can't remember. So Access stops dead as it hasn't got adequate rights.
    I'll see if there is another way to do it tomorrow and get back to you - I can't read sql files on my phone it seems.
    Alternatively, it is entirely possible to have a 9.2 server running alongside an 8.4, so long as it is on a different port (e.g. 5433).
    Once you're up and running it would be great to get some feedback - you're my first customer!
    Best regards,
    Lindsay
    On Dec 3, 2013 11:09 PM, "Caesar Olima" wrote:
    Dear Fellow OC users and Devs,

    I have been trying get my hands on creating a Data Mart for the OpenClinica as explained at the link http://en.wikibooks.org/wiki/OpenClinica_User_Manual/CommunityDataMart. I had to make a couple of changes to the sql script as it was originally tested on Postgres 9.2 and I wanted to test this on studies running on ver. 8.4. Please find attached a copy of the final script. I have also installed an ODBC driver for postgres 8.4 but when I try to connect to the database from the Access Processor I get the error the application has been stopped due to runtime error. A screen short of my config setting is attached. Has anyone managed to get through setting up the Community Data Mart? If you happened to have bumped onto a similar issue, how did you manage to resolve it?

    Thanks in advance for your help and contributions.

    Kind regards,
    Caesar Olima
  • CaesarOlimaCaesarOlima Posts: 42
    Hi Lindsay,
    Thank you for extensive explanation of the processes. I think as you mentioned, with the installer I wasn't able to modify the mod_get_study_list file. In fact, after making the modifications on the sql script I just concluded, the errors must have originated from the Access file but did not know where to start debugging from.
    I will run this again and see what I get. Thanks, once again for your time.
    Kind regards,
    Caesar
    Sent: 04 December 2013 04:17
    To: [email protected]
    Subject: Re: [Developers] OpenClinica Community Data Mart
    Hi Caesar,
    The following 4 things work in 9.2 but don't work in 8.4. Unfortunately they affect both the warehousing sql script and the Access module called mod_get_study_list. I have attached fixed versions of these. You might be able to get away with using the non-installer version even if you only have Access runtime, but if you happen to need a new installer, let me know.
    1. ALTER ROLE IN DATABASE SET
    It seems to work if the 'IN DATABASE' is left out. Located in line 1307 of sql script, and line 93 of mod_get_study_list.
    2. GRANT SELECT ON ALL TABLES IN SCHEMA
    The tidiest workaround I could find is described at [1], which builds the necessary per-table statements in a query, and uses a simple function to execute them as they're made. Located in line 1311 of sql script, and line 95 of mod_get_study_list.
    3. CREATE ROLE option NOREPLICATION
    This can be removed since there is no replication in 8.4 anyway. Located in line 90 of mod_get_study_list.
    4. concat()
    Replaced with double-pipe || concatenation. Located in lines 544 and 556 of sql script.
    5. Other things
    I noticed in your screenshot you had the driver name as PostgreSQL35W - I use {PostgreSQL Unicode} (including braces), in case that makes any difference. Also it seems that I forgot to mention anywhere that you need to create a role called 'access_processor', but it looks like you figured that out.
    [1] http://www.depesz.com/2007/10/19/grantall/
    On 3 December 2013 23:36, Lindsay Stevens wrote:
    Hi Caesar,
    I think the problem with 8.4 was that a couple of convenient permissions functions were added in later versions, 'grant on all tables in schema' and one other that I can't remember. So Access stops dead as it hasn't got adequate rights.
    I'll see if there is another way to do it tomorrow and get back to you - I can't read sql files on my phone it seems.
    Alternatively, it is entirely possible to have a 9.2 server running alongside an 8.4, so long as it is on a different port (e.g. 5433).
    Once you're up and running it would be great to get some feedback - you're my first customer!
    Best regards,
    Lindsay
    On Dec 3, 2013 11:09 PM, "Caesar Olima" wrote:
    Dear Fellow OC users and Devs,

    I have been trying get my hands on creating a Data Mart for the OpenClinica as explained at the link http://en.wikibooks.org/wiki/OpenClinica_User_Manual/CommunityDataMart. I had to make a couple of changes to the sql script as it was originally tested on Postgres 9.2 and I wanted to test this on studies running on ver. 8.4. Please find attached a copy of the final script. I have also installed an ODBC driver for postgres 8.4 but when I try to connect to the database from the Access Processor I get the error the application has been stopped due to runtime error. A screen short of my config setting is attached. Has anyone managed to get through setting up the Community Data Mart? If you happened to have bumped onto a similar issue, how did you manage to resolve it?

    Thanks in advance for your help and contributions.

    Kind regards,
    Caesar Olima
This discussion has been closed.