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 <[EMAIL PROTECTED]> 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 > [EMAIL PROTECTED] 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(SqlMapDaoTemplate.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:[EMAIL PROTECTED] > Sent: 17 March 2006 16:02 > To: [email protected] > 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 <[EMAIL PROTECTED]> 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 > [EMAIL PROTECTED] 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:[EMAIL PROTECTED] > Sent: 17 March 2006 14:45 > To: [email protected] > Subject: RE: Using sequences > > See "Auto Generated Keys" on page 15 of the SqlMaps doc. > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > Sent: Friday, March 17, 2006 7:38 AM > > To: [email protected] > > 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? > > > > > > > > > > > > > > > > >
