ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Paul Carr" <Paul.C...@express-gifts.co.uk>
Subject RE: Using sequences
Date Fri, 17 Mar 2006 16:52:38 GMT
Ignore me....should've had 
 
select nextval for manf_req_serial from manufacture_request..........
fetch first row only
 
-----Original Message-----
From: Paul Carr [mailto:Paul.Carr@express-gifts.co.uk] 
Sent: 17 March 2006 16:46
To: user-java@ibatis.apache.org
Subject: RE: Using sequences
 
I'm obviously missing something here Jeff,  used abator to generate it ,
(had to add identity="false") but it worked fine and generated me :-
 
  <insert id="abatorgenerated_insert"
parameterClass="egl.valueobjects.ManufactureRequest">
    <!--
      WARNING - This element is automatically generated by Abator for
iBATIS, do not modify.
      This element was generated on Fri Mar 17 16:23:02 GMT 2006.
    -->
 
    <selectKey keyProperty="manufactureRequestK"
resultClass="java.lang.Integer">
      SELECT NEXTVAL FOR MANF_REQ_SERIAL AS manufactureRequestK FROM
MANUFACTURE_REQUEST
    </selectKey>
    insert into upca.manufacture_request (MANUFACTURE_REQUEST_K, 
      RETAILER_REFERENCE_ID, RETAILER_NAME_T, MESSAGE_D, 
      MANUFACTURE_REQUEST_LINE_Q, RETAILER_GROUP_ID) 
    values (#manufactureRequestK:INTEGER#,
#retailerReferenceId:VARCHAR#, 
      #retailerNameT:VARCHAR#, #messageD:DATE#, 
      #manufactureRequestLineQ:INTEGER#, #retailerGroupId:VARCHAR#) 
  </insert>
 
 
I have manufactureRequestK defined as an Integer on my
ManufactureRequest value object, 
 
But when I run the insert it fails :- 
 
Caused by: com.ibatis.dao.client.DaoException: Failed to insert - id
[upca_manufacture_request.abatorgenerated_insert], parameterObject
[egl.valueobjects.ManufactureRequest@1581e80]. Cause:
java.sql.SQLException: Error: executeQueryForObject returned too many
results.
Caused by: java.sql.SQLException: Error: executeQueryForObject returned
too many results.
      at
com.ibatis.dao.client.template.SqlMapDaoTemplate.insert(SqlMapDaoTemplat
e.java:102)
      at
egl.dao.ManufactureRequestDAOImpl.insert(ManufactureRequestDAOImpl.java:
27)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
      at java.lang.reflect.Method.invoke(Unknown Source)
      at com.ibatis.dao.engine.impl.DaoProxy.invoke(DaoProxy.java:72)
      at $Proxy1.insert(Unknown Source)
      ... 11 more
 
 
Any ideas ?
 
 
-----Original Message-----
From: Jeff Butler [mailto:jeffgbutler@gmail.com] 
Sent: 17 March 2006 16:02
To: user-java@ibatis.apache.org
Subject: Re: Using sequences
 
Looks like you added the <selectKey> element to the insert statement by
hand, and didn't get it quite right - you need to specify the
keyProperty value on the <selectKey>.
 
Abator will generate this for you properly if you'll use the
<generatedKey> element in your abatorConfiguration like this:
 
<table schema="upca" tableName="manufacture_request" >
  <generatedKey column="MANUFACTURE_REQUEST_K" 
    sqlStatement="SELECT NEXTVAL FOR MANF_REQ_SERIAL AS
manufactureRequestKey FROM MANUFACTURE_REQUEST"/>
</table>
 
Jeff Butler


 
On 3/17/06, Paul Carr <Paul.Carr@express-gifts.co.uk> wrote: 
Thanks Bryun , I read and tried that but I get exceptions.

My SQL MAP for the insert is

<insert id="abatorgenerated_insert" 
parameterClass="egl.valueobjects.ManufactureRequest">
       <selectKey resultClass="int" >
               SELECT NEXTVAL FOR MANF_REQ_SERIAL AS
manufactureRequestKey FROM MANUFACTURE_REQUEST 
       </selectKey>
   insert into upca.manufacture_request (MANUFACTURE_REQUEST_K,
     RETAILER_REFERENCE_ID, RETAILER_NAME_T, MESSAGE_D,
     MANUFACTURE_REQUEST_LINE_Q, RETAILER_GROUP_ID)
   values (#manufactureRequestKey#, #retailerReferenceId:VARCHAR#, 
     #retailerNameT:VARCHAR#, #messageD:DATE#,
     #manufactureRequestLineQ:INTEGER#, #retailerGroupId:VARCHAR#)
</insert>

It doesn't work  : Is there anything else I need to setup ?

Caused by: com.ibatis.dao.client.DaoException: Failed to insert - id
[upca_manufacture_request.abatorgenerated_insert], parameterObject
[egl.valueobjects.ManufactureRequest@3c2378]. Cause:
com.ibatis.common.jdbc.exception.NestedSQLException :
--- The error occurred in
egl/sqlmap/upca_manufacture_request_SqlMap.xml.
--- The error occurred while applying a parameter map.
--- Check the
upca_manufacture_request.abatorgenerated_insert-InlineParameterMap. 
--- Check the statement (update failed).
--- Cause: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -407,
SQLSTATE: 23502, SQLERRMC: TBSPACEID=2, TABLEID=1301, COLNO=0
Caused by: com.ibm.db2.jcc.a.SqlException : DB2 SQL error: SQLCODE:
-407,
SQLSTATE: 23502, SQLERRMC: TBSPACEID=2, TABLEID=1301, COLNO=0
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
egl/sqlmap/upca_manufacture_request_SqlMap.xml. 
--- The error occurred while applying a parameter map.
--- Check the
upca_manufacture_request.abatorgenerated_insert-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: com.ibm.db2.jcc.a.SqlException : DB2 SQL error: SQLCODE:
-407,
SQLSTATE: 23502, SQLERRMC: TBSPACEID=2, TABLEID=1301, COLNO=0
Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -407,
SQLSTATE: 23502, SQLERRMC: TBSPACEID=2, TABLEID=1301, COLNO=0 
       at
com.ibatis.dao.client.template.SqlMapDaoTemplate.insert(SqlMapDaoTemplat
e.java:102)
       at
egl.dao.ManufactureRequestDAOImpl.insert(ManufactureRequestDAOImpl.java:
27)
       at sun.reflect.NativeMethodAccessorImpl.invoke0 (Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown
Source)
       at java.lang.reflect.Method.invoke(Unknown Source) 
       at com.ibatis.dao.engine.impl.DaoProxy.invoke(DaoProxy.java:72)
       at $Proxy1.insert(Unknown Source)
       ... 11 more
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in 
egl/sqlmap/upca_manufacture_request_SqlMap.xml.
--- The error occurred while applying a parameter map.
--- Check the
upca_manufacture_request.abatorgenerated_insert-InlineParameterMap.
--- Check the statement (update failed). 
--- Cause: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -407,
SQLSTATE: 23502, SQLERRMC: TBSPACEID=2, TABLEID=1301, COLNO=0
Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -407,
SQLSTATE: 23502, SQLERRMC: TBSPACEID=2, TABLEID=1301, COLNO=0
       at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdat
e(GeneralStatement.java:91)
       at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert
(SqlMapExecut
orDelegate.java:442)
       at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl
.java:81)
       at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.insert(SqlMapClientImpl.j

ava:58)
       at
com.ibatis.dao.client.template.SqlMapDaoTemplate.insert(SqlMapDaoTemplat
e.java:100)
       ... 18 more
Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -407,
SQLSTATE: 23502, SQLERRMC: TBSPACEID=2, TABLEID=1301, COLNO=0 
       at com.ibm.db2.jcc.a.hd.d(hd.java:1392)
       at com.ibm.db2.jcc.c.jb.l(jb.java:366)
       at com.ibm.db2.jcc.c.jb.a(jb.java:64)
       at com.ibm.db2.jcc.c.w.a(w.java:48)
       at com.ibm.db2.jcc.c.dc.c (dc.java:312)
       at com.ibm.db2.jcc.a.id.cb(id.java:1685)
       at com.ibm.db2.jcc.a.id.d(id.java:2276)
       at com.ibm.db2.jcc.a.id.Z(id.java:1295)
       at com.ibm.db2.jcc.a.id.execute(id.java:1279)
       at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor
.java:84)
       at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUp
date(GeneralStatement.java:200) 
       at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdat
e(GeneralStatement.java:78)
       ... 22 more
java.lang.NumberFormatException: null
       at java.lang.Integer.parseInt (Unknown Source)
       at java.lang.Integer.<init>(Unknown Source)
       at egl.xml.TypeConverter.convert(TypeConverter.java:62)
       at egl.xml.TypeConverter.convert(TypeConverter.java:30)
       at egl.xml.XMLObject.getValueObject(XMLObject.java:101)
manufactureRequestLineK
java.lang.Integer
manufactureRequestK
java.lang.Integer
       at
egl.xml.ManufactureRequestLine.getValueObject(ManufactureRequestLine.jav

a:44)
       at egl.writer.SQLWriter.writeSingle(SQLWriter.java:42)
       at egl.xml.XMLObject.process(XMLObject.java:133)
       at egl.xml.XMLObject.process(XMLObject.java:152)
       at egl.xml.XMLObject.process (XMLObject.java:152)
       at egl.writer.SQLWriter.write(SQLWriter.java:31)
       at
egl.listeners.adaptors.ManufacturingListenerAdaptor.go(ManufacturingList
enerAdaptor.java:63)
       at tests.XMLTest.startTest (XMLTest.java:68)
       at tests.XMLTest.main(XMLTest.java:28)




-----Original Message-----
From: Bruyn Bill [mailto:Bruynb@mcao.maricopa.gov]
Sent: 17 March 2006 14:45 
To: user-java@ibatis.apache.org
Subject: RE: Using sequences

See "Auto Generated Keys" on page 15 of the SqlMaps doc.

> -----Original Message----- 
> From: Adediran.Ayandele@Sun.COM [mailto:Adediran.Ayandele@Sun.COM]
> Sent: Friday, March 17, 2006 7:38 AM
> To: user-java@ibatis.apache.org
> Subject: Re: Using sequences
>
>
>  This is an apt question for me as well.  I access my dao from the
> service layer to retrieve this, and then pass it back.  I 
> struck me as
> not very efficient, but logically it works ok because all my
> DAO calls
> come from the same layer...  So, is there a better way?
>
> Diran
>
> Paul Carr wrote: 
>
> > Hi Guys,  whats the best way to insert a row with ibatis
> when your key
> > column is generated from a sequence on the DB ?
> >
> > Would I edit the sql in the sql map ?  or is there a clever 
> way to do
> > it?
> >
> >
> >
> >
>
 

Mime
View raw message