You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Henry Lu <zh...@umich.edu> on 2006/01/16 17:42:44 UTC
BUG?
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
Re: BUG?
Posted by Larry Meadors <lm...@apache.org>.
It lasts until the SqlMapClient reloads - depending upon your
configuration , it could be an updated class file in a web app, or a
restart.
Larry
On 1/16/06, Henry Lu <zh...@umich.edu> wrote:
> It worked! Thanks a lot. I have a question for you. If I don't specify
> "remapResults=true" How the map works? What is the time period for the
> remap? within a thread? a session? an application? reboot web server? or
> something else?
>
> -Henry
>
> Larry Meadors wrote:
>
> >Add remapResults="true" to your select tag.
> >
> >Larry
> >
> >
> >On 1/16/06, Henry Lu <zh...@umich.edu> wrote:
> >
> >
> >>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
> >>
> >>
> >>
> >>
> >
> >
> >
> >
>
Re: BUG?
Posted by Henry Lu <zh...@umich.edu>.
It worked! Thanks a lot. I have a question for you. If I don't specify
"remapResults=true" How the map works? What is the time period for the
remap? within a thread? a session? an application? reboot web server? or
something else?
-Henry
Larry Meadors wrote:
>Add remapResults="true" to your select tag.
>
>Larry
>
>
>On 1/16/06, Henry Lu <zh...@umich.edu> wrote:
>
>
>>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
>>
>>
>>
>>
>
>
>
>
Re: BUG?
Posted by Larry Meadors <lm...@apache.org>.
Add remapResults="true" to your select tag.
Larry
On 1/16/06, Henry Lu <zh...@umich.edu> wrote:
> 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
>
>