Thanks Volker,
I receive the same error even when using
{? = call insert_country(?, ?, ?, ?)}
Danke im Voraus!
On 12/3/05, Volker Reichel <[EMAIL PROTECTED]> wrote:
> 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
>
>
