DB2 .... I've changed it anyway.....more efficient:-
select nextval for manf_req_serial from sysibm.sysdummy1;
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Larry Meadors
Sent: 17 March 2006 16:52
To: [email protected]
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 <[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(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:[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?
> >
> >
> >
> >
>
