ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Volker Reichel" <volker_reic...@t-online.de>
Subject AW: iBatis, Spring and postgres stored procedure
Date Sat, 03 Dec 2005 13:08:51 GMT
lunohodo,

your stored procedure returns a value to the caller (the insert_id) but
your SQLMap does not provide an OUT parameter for the value to be returned.
I think you should use the following

	{? = call insert_country(?, ?, ?, ?)}

in your sqlmap.


-----Urspr√ľngliche Nachricht-----
Von: Lunohodov [mailto:lunohodov@gmail.com]
Gesendet: Freitag, 2. Dezember 2005 16:18
An: user-java@ibatis.apache.org
Betreff: iBatis, Spring and postgres stored procedure


I stuck into a problem and after a few resultless hours I would like
to aks for your help. I searched for a similar problem(the forum as
also fired some googles) but could not find anything so I am starting
a new thread...

 I have a PostgreSQL database with several stored procedures for
fetching, updating, inserting and deleting data.

 A stored procedure takes four parameters and inserts data into two
tables. Code is as follows:


Code:
CREATE OR REPLACE FUNCTION "public"."insert_country" (varchar,
varchar, varchar, varchar) RETURNS bigint AS
$body$
declare
       insert_id bigint;
       countryIso ALIAS FOR $1;
       countryName ALIAS FOR $2;
       countryDesc ALIAS FOR $3;
       langIso ALIAS FOR $4;
 begin
       INSERT INTO "tb_Countries" ("CountryCode_ISO") VALUES (countryIso);
       insert_id := currval('"public"."tb_Countries_CountryID_seq"');
       INSERT INTO "tb_CountryNamesDescriptions" ("CountryID",
"CountryName", "Description", "LanguageCode_ISO") VALUES (insert_id,
countryName, countryDesc, langIso);
       return insert_id;
 end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

The procedure alone works OK. So the problem is not in the procedure itself.

The coresponding section from the SQL-MAP xml definition file is as follows:


Code:
<parameterMap id="insert-country-params" class="java.util.Map">
 <parameter property="isoCode" jdbcType="VARCHAR"
javaType="java.lang.String" mode="IN"/>
 <parameter property="name" jdbcType="VARCHAR"
javaType="java.lang.String" mode="IN"/>
 <parameter property="description" jdbcType="VARCHAR"
javaType="java.lang.String" mode="IN"/>
 <parameter property="langIso" jdbcType="VARCHAR"
javaType="java.lang.String" mode="IN"/>
</parameterMap>

<procedure id="insert-country-sp" parameterMap="insert-country-params"
resultClass="java.lang.Long">
       <![CDATA[
               {call insert_country(?, ?, ?, ?)}
       ]]>
</procedure>
 The Java code is as follows:


Code:
public void createCountry(ICountry c, Locale l)
{
       // code snip
       Map params = new HashMap();
       params.put("isoCode", c.getIsoCode());
       params.put("name", c.getName());
       params.put("description", c.getDescription());
       params.put("langIso", l.getLanguage());
       getSqlMapClientTemplate().insert("insert-country-sp", params);
}

 When I run a test application I receive the following error:


Code:
org.springframework.dao.DataIntegrityViolationException: SqlMapClient
operation; SQL [];
--- The error occurred in locality/postgres/maps/Country.xml.
--- The error occurred while applying a parameter map.
--- Check the Country.insert-country-params.
--- Check the parameter mapping for the 'isoCode' property.
--- Cause: org.postgresql.util.PSQLException: The column index is out
of range: 0, number of columns: 4.; nested exception is
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in locality/postgres/maps/Country.xml.
--- The error occurred while applying a parameter map.
--- Check the Country.insert-country-params.
--- Check the parameter mapping for the 'isoCode' property.


 Can someone help me?

 Thank you in advance!

 Regards
 lunohodo


Mime
View raw message