ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Darek Dober" <doover...@op.pl>
Subject Re: Re: selectKey postgresql8 problem
Date Tue, 03 May 2005 03:36:43 GMT
The problem was stupid, of course:)

I used update method instead of insert method for insert mapped statement
( according to jPetstore 4.05)

By the way,
Postgresql 8 allows to execute two dml operations in one sql map statement +
<selectKey>. It makes it much easier to write.

Thanks, a lot

Darek



>
> I hooked up pgsql to the jgamestore app and ran the unit test against
> the following sql map. It worked fine and populated the id just fine.
> I think the key is to specify your jdbc types in the mapping
> (#myInteger:INTEGER#).
>
> <insert id="insert" parameterClass="Category">
>   <selectKey keyProperty="categoryId" resultClass="integer">
>     SELECT nextval('global_seq')
>   </selectKey>
>   INSERT INTO CATEGORY
>   (
>     categoryId,
>     title,
>     description,
>     parentCategoryId,
>     leftNode,
>     rightNode,
>     sequence,
>     ancestorTree
>   )
>   VALUES(
>     #categoryId:INTEGER#,
>     #title:VARCHAR#,
>     #description:VARCHAR#,
>     #parentCategoryId:INTEGER#,
>     #leftNode:INTEGER#,
>     #rightNode:INTEGER#,
>     #sequence:INTEGER#,
>     #ancestorTree:VARCHAR#
>   )
> </insert>
>
> Brandon
>
> On 5/1/05, dooverone@op.pl <dooverone@op.pl> wrote:
> >  Hi,
> >  Unfortunately, It still doesn't work. After many of combinations I
forgot
> to put it, but it doesn't change anything  at all.
> >
> > > The selectKey will simply retrieve the id and populate the addressId
> > >in your Customer object. It is then your responsibility to insert the
> > >proper id along with your insert.
> >
> > Exactly. So when I put the <selectKey>..</selectKey> statement. While
> invocation it should be executed
> > exactly in the order pointed by positions/statements in
> <insert>..</insert>. So if I put property addressId into insert clause or
> not, it should be populated first????
> >
> > So First property addressId should be populated (I should see it in
> database log file, but i cannot, see below). It still isn't executed.
> > Then insert should do the rest with correctly addressId populated.
> > The other way is to do insert ( from sequence) and catch inserted id
with
> <selectKey> using currval instead of nextval after inserting record into
> table.
> > It also isn't executed.
> >
> > Maybe there is a problem between that, the database column addr_id is
type
> of integer, and my resultClass is int (bean has Integer property). But I
> also tried resultClass as resultClass="integer". Nothing changed.
> >
> > Then I relised that the addressId is set to 0 (by struts), and It coulbe
> the problem, that SelectKey maybe is executed when
> > property is null (I removed it from form, so it was null). But It also
> didn't changed anything
> >
> > At the end of mail, ther's a mention about failure of updating id
column:
> > "Failed to update - id [insertCustomerNew] - parameterObject ..."
> > Why addressId isn't mentioned. I also have id column which is  extended
> from ObjectData, but it doesn't seem to be populated.
> >
> > I still hope, You can help me
> >
> > Darek
> >
> >  <insert id="insertCustomerNew" parameterClass="customer">
> > <selectKey resultClass="int" keyProperty="addressId">
> > select nextval('public.addresses_addr_id_seq'::text)
> > </selectKey>
> >      INSERT INTO ADDRESSES (
> >        ADDR_ID
> >       ,STREET
> >       ,PLACENO
> >       ,FLATNO
> >       ,CITY
> >       ,ZIP
> >       ,C_STREET
> >       ,C_PLACENO
> >       ,C_FLATNO
> >       ,C_CITY
> >       ,C_ZIP
> >      )
> >      VALUES (
> >        #addressId#
> >       ,#addressStreet#
> >       ,#addressPlaceNo#
> >       ,#addressFlatNo#
> >       ,#addressCity#
> >       ,#addressPostalCode#
> >       ,#addressCstreet#
> >       ,#addressCplaceNo#
> >       ,#addressCflatNo#
> >       ,#addressCcity#
> >       ,#addressCpostalCode#
> >     )
> >   </insert>
> >
> >   Bean of customerData known as customer parameter Class
> > public class CustomerData  extends ObjectData {
> >
> >    private Integer stateId;
> >    private Integer compId;
> >
> >    private Integer addressId;
> >    ....
> >
> >      public Integer getAddressId() {
> >         System.out.println("getaddressId" + addressId);
> >         return addressId;
> >     }
> >
> >     public void setAddressId(Integer addressId) {
> >         System.out.println("setaddressId" + addressId.intValue());
> >         this.addressId = addressId;
> >     }
> >
> >   }
> >
> > # \d addresses ;
> >            Table "public.addresses"
> >   Column   |         Type          | Modifiers
> > -----------+-----------------------+-----------
> >  addr_id   | integer               | not null
> >  street    | character varying(64) | not null
> >  placeno   | character varying(15) | not null
> >  flatno    | character varying(5)  | not null
> >  city      | character varying(30) | not null
> >  zip       | character(6)          | not null
> >  c_street  | character varying(64) |
> >  c_placeno | character varying(15) |
> >  c_flatno  | character varying(5)  | not null
> >  c_city    | character varying(30) |
> >  c_zip     | character(6)          |
> > Indexes: addr_pk primary key btree (addr_id)
> > Check constraints: "check_zip" (zip ~ '^[0-9]{2}-[0-9]{3}$'::text)
> >
> > Log from database
> >
> > with jdbc driver 8.0311
> >
> > enfo_id IN
> >                (
> >                  SELECT enfo_id
> >                  FROM entry_forms e
> >                  WHERE not exists (select 1 FROM given_entry_skills ges
> WHERE enfo_id = e.enfo_id)        )
> >              )
> > --------------------------- HERE -------------------------------
> > 2005-05-01 13:29:52 CEST LOG:  statement:       INSERT INTO ADDRESSES
> (        ADDR_ID       ,STREET       ,PLACENO       ,FLATNO       ,CITY
> ,ZIP       ,C_STREET       ,C_PLACENO       ,C_FLATNO       ,C_CITY
> ,C_ZIP      )      VALUES(        $1       ,$2       ,$3       ,$4
,$5
> ,$6       ,$7       ,$8       ,$9       ,$10       ,$11     )
> > ----------------------------------------------------------
> > 2005-05-01 13:29:52 CEST ERROR:  duplicate key violates unique
constraint
> "addr_pk"
> > 2005-05-01 13:29:52 CEST LOG:  statement:      SELECT *     FROM states
> ORDER BY  name ASC
> > 2005-05-01 13:29:52 CEST LOG:  statement:      SELECT *     FROM
COMPANIES
> ORDER BY  name ASC
> >
> > with jdbc driver 7.4 build 215
> > -------------------------------
> >
> > SELECT enfo_id
> >                  FROM entry_forms e
> >                  WHERE not exists (select 1 FROM given_entry_skills ges
> WHERE enfo_id = e.enfo_id)        )
> >              )
> >
> > 2005-05-01 13:31:17 CEST LOG:  statement: set datestyle to 'ISO'; select
> version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN'
> else getdatabaseencoding() end;
> > 2005-05-01 13:31:17 CEST LOG:  duration: 2.466 ms
> > 2005-05-01 13:31:17 CEST LOG:  statement: set client_encoding =
'UNICODE'
> > 2005-05-01 13:31:17 CEST LOG:  duration: 1.400 ms
> > 2005-05-01 13:31:17 CEST LOG:  statement: begin;
> > 2005-05-01 13:31:17 CEST LOG:  duration: 0.220 ms
> > 2005-05-01 13:31:17 CEST LOG:  statement: rollback; begin;
> > 2005-05-01 13:31:17 CEST LOG:  duration: 0.196 ms
> > --------------------------- HERE -------------------------------
> > 2005-05-01 13:31:17 CEST LOG:  statement:       INSERT INTO ADDRESSES
> (        ADDR_ID       ,STREET       ,PLACENO       ,FLATNO       ,CITY
> ,ZIP       ,C_STREET       ,C_PLACENO       ,C_FLATNO       ,C_CITY
> ,C_ZIP      )      VALUES(        0       ,'7'       ,'7'       ,'7'
> ,'7'       ,'86-300'       ,''       ,''       ,''       ,''       ,'' )
> > 2005-05-01 13:31:17 CEST ERROR:  duplicate key violates unique
constraint
> "addr_pk"
> > ----------------------------------------------------------
> > 2005-05-01 13:31:17 CEST LOG:  statement: rollback; begin;
> > 2005-05-01 13:31:17 CEST LOG:  duration: 0.351 ms
> > 2005-05-01 13:31:17 CEST LOG:  statement: rollback; begin;
> > 2005-05-01 13:31:17 CEST LOG:  duration: 0.225 ms
> > 2005-05-01 13:31:17 CEST LOG:  statement: rollback; begin;
> > 2005-05-01 13:31:17 CEST LOG:  duration: 0.431 ms
> > 2005-05-01 13:31:17 CEST LOG:  statement:      SELECT *     FROM states
> ORDER BY  name ASC
> > 2005-05-01 13:31:17 CEST LOG:  duration: 3.400 ms
> > 2005-05-01 13:31:17 CEST LOG:  statement: rollback; begin;
> > 2005-05-01 13:31:17 CEST LOG:  duration: 0.396 ms
> > 2005-05-01 13:31:17 CEST LOG:  statement: rollback; begin;
> > 2005-05-01 13:31:17 CEST LOG:  duration: 0.242 ms
> > 2005-05-01 13:31:17 CEST LOG:  statement:      SELECT *     FROM
COMPANIES
> ORDER BY  name ASC
> >
> > --------------------------- After removing hidden property addressId
from
> form, it isn't set to 0 ------------------
> > tatement: rollback; begin;
> > 2005-05-01 13:52:24 CEST LOG:  duration: 0.485 ms
> > 2005-05-01 13:52:24 CEST LOG:  statement:        INSERT INTO ADDRESSES
> (        ADDR_ID       ,STREET       ,PLACENO       ,FLATNO       ,CITY
> ,ZIP       ,C_STREET       ,C_PLACENO       ,C_FLATNO       ,C_CITY
> ,C_ZIP      )      VALUES (        null       ,'89'       ,'89'
,'898'
> ,'9'       ,'86-300'       ,''       ,''       ,''       ,'' ,''     )
> > 2005-05-01 13:52:24 CEST ERROR:  null value in column "addr_id" violates
> not-null constraint
> >
> > In my tomcat logs, I have:
> > --------------------------------
> >
> > DEBUG (CustomerNewActionDAO.java:57) -
> com.protex.persistence.actiondao.CustomerNewActionDAO:insertObject
> > getAddressId:null
> > ERROR (CustomerNewActionDAO.java:81) -
> com.protex.persistence.actiondao.CustomerNewActionDAO.insertObject error
> Failed to update - id [insertCustomerNew] - parameterObject
> [com.protex.bean.CustomerData@e61d84].  Cause:
> com.ibatis.common.jdbc.exception.NestedSQLException:
> > --- The error occurred in
> com/protex/persistence/sqlmapdao/sql/Customer.xml.
> > --- The error occurred while applying a parameter map.
> > --- Check the insertCustomerNew-InlineParameterMap.
> > --- Check the statement (update failed).
> > --- Cause: org.postgresql.util.PSQLException: ERROR: null value in
column
> "addr_id" violates not-null constraint
> >
> > Caused by: org.postgresql.util.PSQLException: ERROR: null value in
column
> "addr_id" violates not-null constraint
> >
>


Mime
View raw message