Uploaded image for project: 'i2b2 Core Software'
  1. i2b2 Core Software
  2. CORE-94

Wrong built SQL statement causing SQLSyntaxErrorException at GetPDOFromInputList request

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.7.01
    • Fix Version/s: 1.7.02
    • Component/s: CRC Cell
    • Labels:
      None
    • Environment:
      Ubuntu 12.04.4 LTS + Oracle XE 10.2.0.1
    • Rank:
      0|i000kv:
    • Testing Notes:
      Tested with the latest build and it appears to be working correctly. Tested the xml message using Advanced Rest Client in google chrome. The PDO was returned without any errors.

      Description

      Consider a GetPDOFromInputList request like this:

      <message_body>
              <ns3:pdoheader>
                  <patient_set_limit>0</patient_set_limit>
                  <estimated_time>180000</estimated_time>
                  <request_type>getPDO_fromInputList</request_type>
              </ns3:pdoheader>
              <ns3:request xsi:type="ns3:GetPDOFromInputList_requestType" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                  <input_list>
                      <patient_list min="0" max="0">
                          <patient_set_coll_id>3</patient_set_coll_id>
                      </patient_list>
                  </input_list>
                  <filter_list>
                      <panel name="The panel name">
                          <panel_number>1</panel_number>
                          <panel_accuracy_scale>1</panel_accuracy_scale>
                          <invert>0</invert>
                          <item>
                              <hlevel>2</hlevel>
                              <item_key>\\i2b2\i2b2\Diagnoses\Circulatory system (390-459)\</item_key>
                              <dim_tablename>concept_dimension</dim_tablename>
                              <dim_dimcode>\i2b2\Diagnoses\Circulatory system (390-459)\</dim_dimcode>
                              <item_is_synonym>false</item_is_synonym>
                          </item>
                      </panel>
                  </filter_list>
                  <output_option names="asattributes">
                      <observation_set techdata="true" onlykeys="false" blob="false"/>
                      <patient_set techdata="true" select="using_input_list" onlykeys="false"/>
                      <event_set techdata="true" select="using_filter_list" onlykeys="false"/>
                      <observer_set_using_filter_list techdata="true" select="using_filter_list" onlykeys="false"/>
                      <concept_set_using_filter_list select="using_filter_list" onlykeys="false"/>
                      <modifier_set_using_filter_list techdata="true" select="using_filter_list" onlykeys="false"/>
                  </output_option>
              </ns3:request>
      </message_body>


      The key point is either that it has panels / items in its filter list or some output option (I don't really know). This will cause following exception:

      "java.sql.SQLSyntaxErrorException: ORA-00928: missing SELECT keyword" in class edu.harvard.i2b2.crc.dao.pdo.TablePdoQueryConceptDao.

      I think I found the bug in line 108 ff. If you don't use postgre SQL an SQL statement is built like:

      INSERT INTO I2B2BASTIPROJECT.GLOBAL_TEMP_FACT_PARAM_TABLE (char_param1) (char_param1)
      SELECT ...

      This is wrong SQL syntax as (char_param1) appears twice and Oracle expects a SELECT in the second brackets. To fix it, change line 110

      insertSql += "(char_param1) select distinct obs_concept_cd from ( "

      to

      insertSql += "select distinct obs_concept_cd from ( "


      I haven't tried this fix with postgre, so please check if it still works with postgre before checking it in.

        Attachments

          Activity

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Git Source Code