[CORE-94] Wrong built SQL statement causing SQLSyntaxErrorException at GetPDOFromInputList request Created: 28/Apr/14  Updated: 01/Jul/14  Resolved: 09/Jun/14

Status: Closed
Project: i2b2 Core Software
Component/s: CRC Cell
Affects Version/s: 1.7.01
Fix Version/s: 1.7.02

Type: Bug Priority: Major
Reporter: Bastian Weinlich Assignee: Janice Donahoe
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment: Ubuntu 12.04.4 LTS + Oracle XE 10.2.0.1

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.
Participant/s:

 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.
Generated at Mon Apr 14 10:27:59 UTC 2025 using Jira 8.20.11#820011-sha1:0629dd8d260e3954ece49053e565d01dabe11609.