Hi,
Iam using ibatis 2.0. database mysql
I facing problem with executing procedures.
I got the error like this:
***************************************************************************­
****************
javax.servlet.ServletException:
--- The error occurred in sql/DropDown.xml.
--- The error occurred while executing query procedure.
--- Check the {call select_city(?)}.
--- Check the SQL Statement (preparation failed).
--- Cause: java.sql.SQLException: Callable statments not supported.

org.apache.struts.action.RequestProcessor.processException(RequestProcessor­.java:
545)

org.apache.struts.action.RequestProcessor.processActionPerform(RequestProce­
ssor.java:
486)

org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:
274)

org.apache.struts.action.ActionServlet.process(ActionServlet.java:
1482)

org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
       javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
       javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

root cause

com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in sql/DropDown.xml.
--- The error occurred while executing query procedure.
--- Check the {call select_city(?)}.
--- Check the SQL Statement (preparation failed).
--- Cause: java.sql.SQLException: Callable statments not supported.

com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWit­
hCallback(GeneralStatement.java:
185)

com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryFor­
List(GeneralStatement.java:
123)

com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExe­
cutorDelegate.java:
615)

com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExe­
cutorDelegate.java:
589)

com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionI­
mpl.java:
118)

com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClientImp­
l.java:
95)
       com.homeshift.common.DropDownDAO.getCountries(DropDownDAO.java:
29)
       com.homeshift.common.CityAction.execute(CityAction.java:35)

org.apache.struts.action.RequestProcessor.processActionPerform(RequestProce­
ssor.java:
484)

org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:
274)

org.apache.struts.action.ActionServlet.process(ActionServlet.java:
1482)

org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
       javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
       javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
***************************************************************************­
*****************

I wrote simple procedure which returns cities of a country :

CREATE DEFINER="root"@"localhost" PROCEDURE "select_city"(country INT)
BEGIN
  SELECT city_name FROM CITY WHERE country_id=country;
END $$

In Dropdown.xml :

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap   PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//
EN"
   "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="Dropdown">
  <select id="allCities" resultClass="java.util.HashMap">
       SELECT * FROM CITY
  </select>
 <parameterMap id="Parameters" class="map" >
            <parameter property="country" jdbcType="INTEGER"
javaType="java.lang.Integer"
                  mode="INOUT"/>
 </parameterMap>
 <procedure id="getCountries" parameterMap="Parameters" >
       {call select_city(?)}
 </procedure>
</sqlMap>

In Action class :
               HashMap paramMap = new HashMap();
       paramMap.put("country", new Integer(91));
       result = dropDownDAO.getCountries(paramMap);

In DAO class:
              public List getCountries(HashMap hashMap) throws
Exception {
       List dd = (List)sqlMap.queryForList("getCountries",hashMap);
       return dd;
       }

In sqMapConfig.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<!-- Always ensure to use the correct XML header as above! -->
<sqlMapConfig>

<!-- The properties (name=value) in the file specified here can be
used placeholders in this config
       file (e.g. "${driver}". The file is relative to the classpath
and is
completely optional. -->
       <properties resource="resources/sqlMapConfig.properties" />

       <!-- Configure a datasource to use with this SQL Map using
Jakarta
DBCP
       notice the use of the properties from the above resource -->

  <transactionManager type="JDBC" commitRequired="false">
  <dataSource type="DBCP">
               <property name="JDBC.Driver" value="${driver}"/>
               <property name="JDBC.ConnectionURL" value="${url}"/>
               <property name="JDBC.Username" value="${username}"/>
               <property name="JDBC.Password" value="${password}"/>
               <property name="Pool.MaximumActiveConnections"
value="10"/>
               <property name="Pool.MaximumIdleConnections" value="5"/

               <property name="Pool.MaximumCheckoutTime"
value="120000"/>
               <property name="Pool.TimeToWait" value="500"/>

       </dataSource>
 </transactionManager>

<!-- Identify all SQL Map XML files to be loaded by this SQL map.
Notice the paths
       are relative to the classpath. For now, we only have one? -->
       <sqlMap resource="sql/DropDown.xml" />
</sqlMapConfig>

Please help me out on this issue

Regards,
Mohan