problem with stored procedures in ibatis Options

[itmohan2002]

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

 

[Juha Laiho]

(e-mail address removed) said:

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.

You're using either an old version of MySQL, or a such version of JDBC
library for MySQL that doesn't support callable statements.

Check the run-time classpath of your application to find the offending
library.

For more information, take a look at
http://forums.mysql.com/read.php?39,115582,115582#msg-115582
and perhaps do a Google search for your keywords
mysql jdbc "callable statements"
(which is how I found the above link, despite having no experience with
iBatis or MySQL.