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:[EMAIL PROTECTED] 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