Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
1.7.01
-
None
-
Ubuntu 12.04.4 LTS + Oracle XE 10.2.0.1
-
Rank:0|i000kv:
-
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.
<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.