ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lunohodov <lunoho...@gmail.com>
Subject Re: iBatis, Spring and postgres stored procedure
Date Mon, 05 Dec 2005 11:36:44 GMT
Thanks Volker,

I receive the same error even when using

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

Danke im Voraus!



On 12/3/05, Volker Reichel <volker_reichel@t-online.de> 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: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