ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Re: Re: selectKey postgresql8 problem
Date Sun, 01 May 2005 22:09:48 GMT
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