Return-Path: Delivered-To: apmail-incubator-ibatis-user-java-archive@www.apache.org Received: (qmail 38647 invoked from network); 2 May 2005 18:35:45 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 2 May 2005 18:35:45 -0000 Received: (qmail 48924 invoked by uid 500); 2 May 2005 18:37:12 -0000 Delivered-To: apmail-incubator-ibatis-user-java-archive@incubator.apache.org Received: (qmail 48901 invoked by uid 500); 2 May 2005 18:37:12 -0000 Mailing-List: contact ibatis-user-java-help@incubator.apache.org; run by ezmlm Precedence: bulk Reply-To: ibatis-user-java@incubator.apache.org List-Help: List-Unsubscribe: List-Post: List-Id: Delivered-To: mailing list ibatis-user-java@incubator.apache.org Received: (qmail 48885 invoked by uid 99); 2 May 2005 18:37:12 -0000 X-ASF-Spam-Status: No, hits=1.2 required=10.0 tests=DATE_IN_FUTURE_06_12 X-Spam-Check-By: apache.org Received-SPF: pass (hermes.apache.org: domain of dooverone@op.pl designates 213.180.130.29 as permitted sender) Received: from smtp3.poczta.onet.pl (HELO smtp3.poczta.onet.pl) (213.180.130.29) by apache.org (qpsmtpd/0.28) with ESMTP; Mon, 02 May 2005 11:37:12 -0700 Received: from host-ip122-81.crowley.pl ([85.128.81.122]:43140 "EHLO none") by ps3.test.onet.pl with ESMTP id ; Mon, 2 May 2005 20:35:29 +0200 Message-ID: <007801c54f91$53f64d70$6612a8c0@none> From: "Darek Dober" To: "Darek Dober" Cc: References: <001501c54eee$65d184e0$d503a8c0@SUPPORT.inet.support.pl> Subject: Re: Re: selectKey postgresql8 problem Date: Mon, 2 May 2005 20:36:43 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1478 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1478 X-Antivirus: avast! (VPS 0518-0, 2005-05-02), Outbound message X-Antivirus-Status: Clean X-AV-Checked: ClamAV using ClamSMTP at tarchomin.pl X-Virus-Checked: Checked X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N 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 + . 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#). > > > > SELECT nextval('global_seq') > > 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# > ) > > > Brandon > > On 5/1/05, 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 .. statement. While > invocation it should be executed > > exactly in the order pointed by positions/statements in > ... 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 > 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 > > > > > > > > select nextval('public.addresses_addr_id_seq'::text) > > > > 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# > > ) > > > > > > 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 > > >