ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Diran Ayandele <Adediran.Ayand...@Sun.COM>
Subject Re: Using sequences
Date Fri, 17 Mar 2006 21:52:35 GMT
OK, got it working with some help.  The 2 big issues I ran into were:
1.  My parameter map properties were strings and I was trying to return 
an int from the selectKey clause.
2.  I was using SqlMap.executeUpdate instead of executeInsert.  
Switching this led to much better error messages.

Thanks!
Diran

Paul Carr wrote:

>DB2 .... I've changed it anyway.....more efficient:-
>
>select nextval for manf_req_serial from sysibm.sysdummy1;   
>
>-----Original Message-----
>From: larry.meadors@gmail.com [mailto:larry.meadors@gmail.com] On Behalf
>Of Larry Meadors
>Sent: 17 March 2006 16:52
>To: user-java@ibatis.apache.org
>Subject: Re: Using sequences
>
>This SQL is wrong:
>
>SELECT NEXTVAL FOR MANF_REQ_SERIAL AS manufactureRequestK
>FROM MANUFACTURE_REQUEST
>
>...the FROM should be dual, not MANUFACTURE_REQUEST (assuming pgsql or
>Oracle).
>
>Larry
>
>On 3/17/06, Paul Carr <Paul.Carr@express-gifts.co.uk> wrote:
>  
>
>>
>>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