RE: Using sequences

Paul Carr Fri, 17 Mar 2006 08:51:16 -0800

Ignore me….should’ve had

select nextval for manf_req_serial from manufacture_request………. fetch first row only

-----Original Message-----
From: Paul Carr [mailto:[EMAIL PROTECTED]
Sent: 17 March 2006 16:46
To: [email protected]
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 :-

<!--

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.

-->

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:

<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?
> >
> >
> >
> >
>

RE: Using sequences

Reply via email to