Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (asf.osuosl.org: local policy)
Message-ID: <43CBCD04.108@umich.edu>
Date: Mon, 16 Jan 2006 11:42:44 -0500
From: Henry Lu <zhlu@umich.edu>
User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803)
MIME-Version: 1.0
To: user-java@ibatis.apache.org
Subject: BUG?
References: <20060116135722.79535.qmail@web25807.mail.ukl.yahoo.com>
In-Reply-To: <20060116135722.79535.qmail@web25807.mail.ukl.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Take a look the following debug message: (I am using ibatis 2.1.16)

PreparedStatement:      select PARAM1 as m_data1,lab_role_Fnc(PARAM2) as 
m_data2,USERID as m_da
ta3,Name_Fnc(PARAM2) as m_data4,PARAM2 as m_data5, APPLCD m_data6       
FROM AUT
HORIZE.USER_ROLES    where        APPLCD='COREYP' AND 
PARAM1='2'              OR
DER BY 2
11:25:51,156 DEBUG - [sql.PreparedStatement.debug] {pstm-100004} 
Parameters: []
11:25:51,156 DEBUG - [sql.PreparedStatement.debug] {pstm-100004} Types: []
11:25:51,156 DEBUG - [sql.ResultSet.debug] {rset-100005} ResultSet
11:25:51,171 DEBUG - [sql.ResultSet.debug] {rset-100005} Header: 
[M_DATA1, M_DAT
A2, M_DATA3, M_DATA4, M_DATA5, M_DATA6]
11:25:51,171 DEBUG - [sql.ResultSet.debug] {rset-100005} Result: [2, 
DIRECTOR, j
eschwar, Schwartz,Jessica, 01043411, COREYP]
1=2, 2=DIRECTOR, 3=jeschwar, 4=Schwartz,Jessica, 5=01043411, 6=COREYP

so far so good

11:25:51,187 DEBUG - [sql.Connection.debug] {conn-100006} Connection
11:25:51,187 DEBUG - [sql.PreparedStatement.debug] {pstm-100007} 
PreparedStateme
nt:      select LAB_ID as m_data1,LAB_ID || ' - ' || LAB_NAME as 
m_data2       F
ROM LABS                  ORDER BY LAB_NAME
11:25:51,187 DEBUG - [sql.PreparedStatement.debug] {pstm-100007} 
Parameters: []
11:25:51,187 DEBUG - [sql.PreparedStatement.debug] {pstm-100007} Types: []
11:25:51,203 DEBUG - [sql.ResultSet.debug] {rset-100008} ResultSet
11:25:51,203 DEBUG - [sql.ResultSet.debug] {rset-100008} Header: 
[M_DATA1, M_DAT
A2]
11:25:51,203 DEBUG - [sql.ResultSet.debug] {rset-100008} Result: [2, 2 - 
CMB lab
 I]

error:

org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [] in 
task 'SqlMapClient operation'; nested exception is 
com.ibatis.common.jdbc.exception.NestedSQLException: --- The error 
occurred in edu/umich/med/umms/coreyp/dao/ibatis_map/misc.xml. --- The 
error occurred while applying a result map. --- Check the 
select_list_col_str-AutoResultMap. --- Check the result mapping for the 
'm_data3' property. --- Cause: java.sql.SQLException: Invalid column name
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator(SQLErrorCodeSQLExceptionTranslator.java:223)

somehow, ibatis/sql tried to take partial of first statemen in to the 
second one.

here is my map:

  <select id="select_list_col_str"
             resultClass="Strdata">
    select $col_name_str$
           FROM $table_name$
   <dynamic prepend="where">
    <isNotEmpty prepend="and" property="where_str">
        $where_str$
    </isNotEmpty>
   </dynamic>
       ORDER BY $order_str$
  </select>

Java code:

      lab_id = "2";
     
      where_str = "APPLCD='COREYP' AND PARAM1='"
         + lab_id + "'";
     
      col_name_list =
         "PARAM1 as m_data1,"
         + "lab_role_Fnc(PARAM2) as m_data2,"
         + "USERID as m_data3,"
         + "Name_Fnc(PARAM2) as m_data4,"
         + "PARAM2 as m_data5, APPLCD m_data6";
        
      List lst2 = m_misc.SelectList_col_str(
            col_name_list,
            "AUTHORIZE.USER_ROLES",
            where_str, "2");
      if (lst2 != null && lst2.size() > 0) {
         st = (Strdata)lst2.get(0);
         System.out.println(
               "1="+st.getM_data1()+
               ", 2="+ st.getM_data2() +
               ", 3="+ st.getM_data3() +
               ", 4="+ st.getM_data4() +
               ", 5="+ st.getM_data5() +
               ", 6="+ st.getM_data6());
         form.setCnt_1(lst2.size());
         form.setList_1(lst2);
      }

      where_str = null;
      order_str = "LAB_NAME";
      col_name_list = "LAB_ID as m_data1,"
         + "LAB_ID || ' - ' || LAB_NAME as m_data2";
     
      lst = m_misc.SelectList_col_str(
            col_name_list,
            "LABS", where_str, order_str);
      if (lst != null && lst.size() > 0) {
         form.setCnt_2(lst.size());
         form.setList_2(lst);
      } else {
         return;
      }

   public List SelectList_col_str(String col_str, // required
         String table_name, // required
         String where_str,  // optional
         String order_str)  // required
   {
      Map map = new HashMap(4);
      map.put("col_name_str", col_str); // required
      map.put("table_name", table_name); // required
      if (where_str != null && where_str.length() > 0) {
         map.put("where_str", where_str);
      } else {
         map.put("where_str", "");
      }
      map.put("order_str", order_str); // required
     
      return getSqlMapClientTemplate().queryForList(
               "select_list_col_str", map);
   }

-Henry