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