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

Web Service CRF

kevintehkevinteh Posts: 32
edited April 2014 in Web Services & Integration
Dear developers

This might be a simple yes/no answer. I'm so sorry if it is. So i'm wondering if there's a way of getting user input ITEM OIDS using web services (don't mind rest or soap)? The closest one i've seen is studyEventDefinitions (SOAP) however no mention of items...Thank you so much for your help...Cheers..

Post edited by bbaumann on


  • toskriptoskrip Posts: 279 ✭✭✭
    Hi Kevin,

    You can use study getMetadata SOAP service. It give you all the information about study/events/crfs/items together with OIDs

    More info here:


  • kevintehkevinteh Posts: 32

    Sorry for sounding silly...I'm very new with REST..This is what i've typed in terminal based on the examples on the web:
    GET /OpenClinica/ClinicalData/html/view/S_12345/1238888/SE_IMAGINGVISIT/F_GENERAL?tabId=1&exitTo=ViewStudySubject?id=1
    Not much luck. Must be missing something. Can I request an example of a working REST request please?  Thank you so much for your help. Cheers..

  • toskriptoskrip Posts: 279 ✭✭✭

    for invoking REST service you have to by logged in your OpenClinica instance. If you want to call the service in your program or external application you have to before sent POST request with user credential info to login into OpenClinica.

    The REST URL can look similar to this:

    you have to replace:
    yourserver: IP or domain name of server where you are running OpenClinica
    StudyOID: OID of study you want to query
    StudySubjectOID: OID of study subject you want to query

    additionaly you can specify the format of output. My example is using JSON but you can replace json string in the URL with xml in order to get the data in ODM XML format.


  • kevintehkevinteh Posts: 32


    Thank you so much for your help. Firstly I forgot to mention that I’m using openclinica 3.1.2 VM image to try out OCs web services. You said to firstly use a POST request with user credential info to login into OpenClinica I tried:

    POST –C x:y http://localhost:8080/OpenClinica/rest/clinicaldata/

    Is this anywhere near correct? I also tried using GET statement written as shown below with –C x:y (password credentials)..Got back OCs homepage in html. Also does the GET command below stand for OC 3.1.2 or only valid in OC 3.2?



    I’m sorry to sound like a novice. Thank you so much for all your help again. Cheers.


  • toskriptoskrip Posts: 279 ✭✭✭

    First of all I think that REST Url are only supported in OC 3.1.4 and newer

    For authentication you have to basically emulate login click from OC login page. This one perform post request wit user credential (Spring security).

    The post has to submit the form data (user credentials). There is a standardised naming of login fields in Spring security, which is j_username for username and j_password for password for which you have to provide a valid data (password has to be in clear form not the SHA1 hash). And post request has to by made to:

    It is a little bit strange with REST Url that they only support this kind of authentication. If I were you I would rather consider to use SOAP.

    What exactly do you want to accoplish? You just need to find out OID of specific CRF item? Or do you also need to read values entered by user and saved into CRF item field?

  • kevintehkevinteh Posts: 32


    Ok so maybe REST might not be the best to try first. What i'm trying to do is get saved fields from user(data entry person) in particular data from an item OID from a particular patient in a site and event. Is this possible with SOAP/REST? Only trying out REST url calls because i've tried SOAP calls using visual basic c#. It's a nightmare trying to consume java wsse spring using VB. I might need to persevere to find one? Cheers.


  • toskriptoskrip Posts: 279 ✭✭✭
    I see, in your case SOAP will be no good because there is no support for what you want to accomplish in SOAP OpenClinica web services.

    REST with OpenClinica can be used from version 3.1.3 so with your current 3.1.2 it will not be possible.

    The last option, if you are ok with directly querying OC database, to run SQL script to get entered eCRF data. For this you will need: studyidentifier, subject pid, eventdefinitionoid, formversionoid, itemgroupoid and crfidtemoid.

    The SQL select can look e.g. like this:

    sql = "SELECT \n" +
                    "ss.study_subject_id,\n" +
                    "ss.study_subject_id,\n" +
                    "ss.label AS SubjID,\n" +
                    "s.date_of_birth AS BrthDat,\n" +
                    "s.gender AS Sex,\n" +
                    "s.unique_identifier AS SubjectPID,\n" +
                    "sed.study_event_definition_id AS SEID,\n" +
                    "sed.name AS SECode,\n" +
                    "sed.description AS SEName,\n" +
                    "se.sample_ordinal AS SERepeat,\n" +
                    "cv.crf_id AS FormID,\n" +
                    "ec.crf_version_id AS FormVersID,\n" +
                    "c.name AS FormName,\n" +
                    "cv.name AS VersionName,\n" +
                    "1 AS FormRepeat,\n" +
                    "i.item_id AS ItemID,\n" +
                    "i.name AS ItemCode,\n" +
                    "id.ordinal AS IGRepeat,\n" +
                    "i.description AS Item,\n" +
                    "ifm.show_item AS Visible,\n" +
                    "id.value AS ItemValue,\n" +
                    "decode AS ItemDecode\n" +
                    "FROM study_subject ss\n" +
                    "INNER JOIN subject s ON ss.subject_id = s.subject_id\n" +
                    "INNER JOIN study_event se ON ss.study_subject_id = se.study_subject_id\n" +
                    "INNER JOIN study_event_definition sed ON se.study_event_definition_id = sed.study_event_definition_id\n" +
                    "INNER JOIN event_crf ec ON se.study_event_id = ec.study_event_id\n" +
                    "INNER JOIN crf_version cv ON ec.crf_version_id = cv.crf_version_id\n" +
                    "INNER JOIN crf c ON cv.crf_id = c.crf_id\n" +
                    "INNER JOIN event_definition_crf edc ON cv.crf_id = edc.crf_id AND se.study_event_definition_id = edc.study_event_definition_id\n" +
                    "INNER JOIN item_form_metadata ifm ON cv.crf_version_id = ifm.crf_version_id\n" +
                    "INNER JOIN item i ON ifm.item_id = i.item_id\n" +
                    "LEFT JOIN item_data id ON ec.event_crf_id = id.event_crf_id AND i.item_id = id.item_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT \n" +
                    "\t  version_id as crf_version_id\n" +
                    "\t, response_set_id as set_id\n" +
                    "\t, label\n" +
                    "\t, options_values as value\n" +
                    "\t, options_text as decode\n" +
                    "\tFROM response_set\n" +
                    "\tWHERE response_type_id = 3\n" +
                    "\t UNION\n" +
                    "\tSELECT\n" +
                    "\t  version_id as crf_version_id\n" +
                    "\t, response_set_id as set_id\n" +
                    "\t, label\n" +
                    "\t, trim (both from regexp_split_to_table(options_values, E',')) as value\n" +
                    "\t, trim (both from regexp_split_to_table(options_text, E',')) as decode\n" +
                    "\tFROM response_set\n" +
                    "\tWHERE response_type_id = 6\n" +
                    "\t) cls\n" +
                    "ON ifm.response_set_id = cls.set_id\n" +
                    "AND ifm.crf_version_id = cls.crf_version_id\n" +
                    "AND id.value = cls.value\n" +
                    "\n" +
                    "WHERE ss.study_id = ? AND s.unique_identifier = ? AND sed.oc_oid = ? AND cv.oc_oid = ? AND i.oc_oid = ?" +
                    "ORDER BY\n" +
                    "  ss.study_subject_id\n" +
                    ", sed.ordinal\n" +
                    ", se.sample_ordinal\n" +
                    ", edc.ordinal\n" +
                    ", id.ordinal\n" +
                    ", ifm.ordinal";


  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    edited April 2014
    Hi @kevinteh,

    Something else you might consider is getting your data by scraping from the webpage.

    I have pushed an example I wrote in python a little while a go to my github repo [1]. The example script logs in to OpenClinica, goes to the audit logs page, scrapes the subject info shown there, puts that into a html email, sends the email then logs out of OpenClinica.

    I was using a gmail account with smtp so you'll need to add in your own username, password, sender email, recipient email; and of course your login and audit urls at the top.

    The email looked something like this:


    This is summary of enrolled subjects.

    Subject ID

    Enrol Date

    Enrol User


    07-Nov-2013 12:39:00



    22-Nov-2013 10:48:01


    If you notice any errors please let me know

    Your pal,
    The OpenClinica-bot


    To adapt this for your situation, if you know the exact subject/event/crf url already then it is a snap - just navigate to the url and scrape the data from the response. If you need to do it for all subjects or whatever, you'd need to get a list of subjects, and for each one (carefully!) navigate your way to the CRF of interest.

    An alternative to hand-coding this could be a scraping service like import.io, which gives you a nice gui to train the scraper with. Actually I got an email from them the other day saying they now support authentication.

    [1] https://github.com/lindsay-stevens-kirby/openclinica_scripts/tree/master/scrape
  • kevintehkevinteh Posts: 32

    Thank you so much for your help. I will try the phyton script you wrote thank you so much for that. Would have loved to use SOAP as in c# this is the easiest way to get to web services (however with .net 3.5 i'm still having problems talking to a java spring security framework). Are you guys gonna make it possible for SOAP soon? Most probably would go with the REST(upgraded to 3.2) solution. Cheers..

This discussion has been closed.