I am still having problems to get just one insert working....
My table is this:
CREATE TABLE OFFENCES
(
OFFENCE_UUID NUMBER NOT NULL,
STATUS VARCHAR2(20) NOT NULL,
AUTHORITY VARCHAR2(20),
TICKET_NUMBER VARCHAR2(20),
OFFENCE VARCHAR2(20),
EMPLOYEE_UUID VARCHAR2(20),
DATETIME_OF_OFFENCE DATE,
DATETIME_OF_TICKET DATE,
PARKING_ATTENDANT VARCHAR2(20),
SITE NUMBER,
FLEET_UUID VARCHAR2(20),
LOC_OF_OFFENCE VARCHAR2(30),
POSTCODE VARCHAR2(8),
CREDITCARD_AUTH VARCHAR2(10),
FINE NUMBER,
AMOUNT_PAID NUMBER,
ORDER_NUMBER VARCHAR2(10),
ROUTE_ID VARCHAR2(10),
ENTERED_BY VARCHAR2(50) NOT NULL,
ASSIGNED_TO VARCHAR2(50),
LAST_MODIFIED DATE DEFAULT SYSDATE
NOT NULL,
LAST_MODIFIED_BY VARCHAR2(50) NOT NULL,
CREATION_DATE DATE DEFAULT SYSDATE
NOT NULL
)
My object this:
private Integer id;
private String status;
private String authority;
private String ticketNumber;
private String offence;
private String employeeID;
private Date dateOfOffence;
private Date dateOfTicket;
private String parkingAttendant;
private Integer site;
private String fleetID;
private String locOfOffence;
private String postcode;
private String creditCardAuth;
private Integer fine;
private Integer amountPaid;
private String orderNumber;
private String routeID;
private String enteredBy;
private String assignedTo;
private Date lastModified;
private String lastModifiedBy;
private Date creationDate;
My sqlMap this:
<typeAlias alias="offence" type="com.ocado.tracker.offences.pojo.Offence"/>
<parameterMap id="offenceMap" class="offence">
<parameter property="id" jdbcType="NUMERIC"
javaType="integer"/>
<parameter property="status" jdbcType="VARCHAR"
nullValue="NO_ENTRY"/>
<parameter property="authority" jdbcType="VARCHAR"
nullValue="NO_ENTRY"/>
<parameter property="ticketNumber" jdbcType="VARCHAR"
nullValue="NO_ENTRY"/>
<parameter property="offence" jdbcType="VARCHAR"
nullValue="NO_ENTRY"/>
<parameter property="employeeID" jdbcType="VARCHAR"
nullValue="NO_ENTRY"/>
<parameter property="dateOfOffence" jdbcType="DATE"
nullValue="NO_ENTRY"/>
<parameter property="dateOfTicket" jdbcType="DATE"
nullValue="NO_ENTRY"/>
<parameter property="parkingAttendant" jdbcType="VARCHAR"
nullValue="NO_ENTRY"/>
<parameter property="site" jdbcType="NUMERIC"
nullValue="NO_ENTRY"/>
<parameter property="fleetID" jdbcType="VARCHAR"
nullValue="NO_ENTRY"/>
<parameter property="locOfOffence" jdbcType="VARCHAR"
nullValue="NO_ENTRY"/>
<parameter property="postcode" jdbcType="VARCHAR"
nullValue="NO_ENTRY"/>
<parameter property="creditCardAuth" jdbcType="VARCHAR"
nullValue="NO_ENTRY"/>
<parameter property="fine" jdbcType="NUMERIC"
nullValue="NO_ENTRY"/>
<parameter property="amountPaid" jdbcType="NUMERIC"
nullValue="NO_ENTRY"/>
<parameter property="orderNumber" jdbcType="VARCHAR"
nullValue="NO_ENTRY"/>
<parameter property="routeID" jdbcType="VARCHAR"
nullValue="NO_ENTRY"/>
<parameter property="enteredBy" jdbcType="VARCHAR"
nullValue="NO_ENTRY"/>
<parameter property="assignedTo" jdbcType="VARCHAR"
nullValue="NO_ENTRY"/>
<parameter property="lastModified" jdbcType="DATE" />
<parameter property="lastModifiedBy" jdbcType="VARCHAR"
nullValue="NO_ENTRY"/>
<parameter property="creationDate" jdbcType="DATE"
nullValue="NO_ENTRY"/>
</parameterMap>
<insert id="addOffence" parameterClass="offence"
parameterMap="offenceMap">
<selectKey resultClass="offence" keyProperty="id" >
SELECT OFFENCES_SEQ.NEXTVAL AS ID FROM DUAL
</selectKey>
insert into
OFFENCES (OFFENCE_UUID, STATUS, AUTHORITY, TICKET_NUMBER,
OFFENCE,
EMPLOYEE_UUID, DATETIME_OF_OFFENCE, DATETIME_OF_TICKET,
PARKING_ATTENDANT,
SITE, FLEET_UUID, LOC_OF_OFFENCE, POSTCODE,
CREDITCARD_AUTHORITY, FINE, AMOUNT_PAID,
ORDER_NUMBER,
ROUTE_ID, ENTERED_BY, ASSIGNED_TO, LAST_MODIFIED_BY,
LAST_MODIFIED,
CREATION_DATE)
values (
#id#, 'new', #authority#, #ticketNumber#,
#offence#, #employeeID#, #dateOfOffence#, #dateOfTicket#,
#parkingAttendant#, #site#, #fleetID#, #locOfOffence#,
#postcode#, #creditCardAuth#, #fine#, #amountPaid#,
#orderNumber#,
#routeID#, #enteredBy#, #assignedTo#, #lastModifiedBy#,
sysdate, sysdate);
</insert>
My error this:
04-02-05 15:39:20:569 - {DEBUG} sql.PreparedStatement Thread [main];
{pstm-1000
01} PreparedStatement: insert into OFFENCES
(OFFENCE_UUI
D, STATUS, AUTHORITY, TICKET_NUMBER, OFFENCE, EMPLOYEE_UUID,
DATETIME_
OF_OFFENCE, DATETIME_OF_TICKET, PARKING_ATTENDANT, SITE,
FLEET_UUID, LO
C_OF_OFFENCE, POSTCODE, CREDITCARD_AUTHORITY, FINE, AMOUNT_PAID,
ORDER
_NUMBER, ROUTE_ID, ENTERED_BY, ASSIGNED_TO, LAST_MODIFIED_BY,
LAST_MODIFIED,
CREATION_DATE) values ( #id#, 'new', #authority#,
#ticketNumber#,
#offence#, #employeeID#, #dateOfOffence#, #dateOfTicket#,
#parkingAttendant#
, #site#, #fleetID#, #locOfOffence#, #postcode#, #creditCardAuth#,
#fine#, #a
mountPaid#, #orderNumber#, #routeID#, #enteredBy#, #assignedTo#,
#lastModifie
dBy#, sysdate, sysdate);
04-02-05 15:39:20:569 - {DEBUG} sql.PreparedStatement Thread [main];
{pstm-1000
01} Parameters: [123, aStatus, null, null, null, null, null, null, null,
null, n
ull, null, null, null, null, null, null, null, bgill, null, 2005-02-04,
bgill, n
ull]
04-02-05 15:39:20:569 - {DEBUG} sql.PreparedStatement Thread [main];
{pstm-1000
01} Types: [java.lang.Integer, java.lang.String, null, null, null, null,
null, n
ull, null, null, null, null, null, null, null, null, null, null,
java.lang.Strin
g, null, java.sql.Date, java.lang.String, null]
04-02-05 15:39:20:585 - {DEBUG} support.SQLErrorCodeSQLExceptionTranslator
Threa
d [main]; Unable to translate SQLException with errorCode '911', will now
try t
he fallback translator
04-02-05 15:39:20:585 - {DEBUG} support.SQLStateSQLExceptionTranslator
Thread [m
ain]; Translating SQLException with SQLState '42000' and errorCode '911'
and me
ssage [
--- The error occurred in com/ocado/tracker/offences/dao/sql/OffenceSQL.xml.
--- The error occurred while applying a parameter map.
--- Check the OffenceSQL.offenceMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: ORA-00911: invalid character
]; SQL was [] for task [SqlMapClient operation]
04-02-05 15:39:20:585 - {DEBUG} datasource.DataSourceUtils Thread [main];
Closi
ng JDBC connection
04-02-05 15:39:20:585 - {ERROR} dao.OffencesDAO Thread [main]; Exception
caught
calling ibatis [Bad SQL grammar [] in task 'SqlMapClient operation'; nested
exc
eption is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/ocado/tracker/offences/dao/sql/OffenceSQL.xml.
--- The error occurred while applying a parameter map.
--- Check the OffenceSQL.offenceMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: ORA-00911: invalid character
Does anyone know what is wrong? or how I can get more info out?
Is there any way of dumping out the SQL statement it is trying to build?
-----Original Message-----
From: Daniel H. F. e Silva [mailto:[EMAIL PROTECTED]
Sent: 04 February 2005 13:31
To: ibatis-user-java@incubator.apache.org
Subject: RE: Mapping Problem
Hehe,
That was not exactly my intention, but if it worked for you, nice!
I just meant to take your OFFENCES_SEQ.NEXTVAL and put it in place of #id#
at values list.
But, it is working!
Cheers,
Daniel Silva.
--- Ben Gill <[EMAIL PROTECTED]> wrote:
Thanks Daniel, that works...
Seems strange to me though that it does, as surely the selectKey would
have
set id to a java.lang.String anyhow...
Working:
<typeAlias alias="offence"
type="com.ocado.tracker.offences.forms.OffenceForm"/>
<insert id="addOffence" parameterClass="offence">
insert into
OFFENCES (OFFENCE_UUID, STATUS, AUTHORITY, TICKET_NUMBER,
OFFENCE,
EMPLOYEE_UUID, DATETIME_OF_OFFENCE, DATETIME_OF_TICKET,
SITE, FLEET_UUID, LOC_OF_OFFENCE, POSTCODE, FINE,
ORDER_NUMBER,
ROUTE_ID, ENTERED_BY, ASSIGNED_TO, LAST_MODIFIED_BY,
LAST_MODIFIED,
CREATION_DATE)
values (
<selectKey resultClass="java.lang.String" keyProperty="id" >
SELECT OFFENCES_SEQ.NEXTVAL AS ID FROM DUAL
</selectKey>, 'new', #authority#, #ticketNumber#, #offence#,
#employeeID#, to_date(#dateOfOffence#,'dd/mm/YYYY HH24:mi'),
to_date(#dateOfTicket#,'dd/mm/YYYY HH24:mi'),
#site#, #fleetID#, #locOfOffence#, #postcode#, #fine#,
#orderNumber#, #routeID#, #enteredBy#, #assignedTo#,
sysdate, sysdate);
</insert>
Not working:
<typeAlias alias="offence"
type="com.ocado.tracker.offences.forms.OffenceForm"/>
<insert id="addOffence" parameterClass="offence">
<selectKey resultClass="java.lang.String" keyProperty="id" >
SELECT OFFENCES_SEQ.NEXTVAL AS ID FROM DUAL
</selectKey>
insert into
OFFENCES (OFFENCE_UUID, STATUS, AUTHORITY, TICKET_NUMBER,
OFFENCE,
EMPLOYEE_UUID, DATETIME_OF_OFFENCE, DATETIME_OF_TICKET,
SITE, FLEET_UUID, LOC_OF_OFFENCE, POSTCODE, FINE,
ORDER_NUMBER,
ROUTE_ID, ENTERED_BY, ASSIGNED_TO, LAST_MODIFIED_BY,
LAST_MODIFIED,
CREATION_DATE)
values (
#id#, 'new', #authority#, #ticketNumber#, #offence#,
#employeeID#, to_date(#dateOfOffence#,'dd/mm/YYYY HH24:mi'),
to_date(#dateOfTicket#,'dd/mm/YYYY HH24:mi'),
#site#, #fleetID#, #locOfOffence#, #postcode#, #fine#,
#orderNumber#, #routeID#, #enteredBy#, #assignedTo#,
sysdate, sysdate);
</insert>
Thanks anyhow...
-----Original Message-----
From: Daniel H. F. e Silva [mailto:[EMAIL PROTECTED]
Sent: 04 February 2005 12:39
To: ibatis-user-java@incubator.apache.org
Subject: RE: Mapping Problem
Ben,
Seems to me that you're using a sequence that gives you integer values,
aren't you?
So, why does your pk column has type VARCHAR(20)?
If your database is Oracle, i bet the problem is happening due to
converting an integer to a
string and then back.
What happens if you replace your #id# in the VALUES list with the call of
the sequence?
Cheers,
Daniel Silva.
--- Ben Gill <[EMAIL PROTECTED]> wrote:
Yeah I tried that... in fact I have tried a whole nuch of things but
cannot
get it to work!!
I am now using:
<typeAlias alias="offence"
type="com.ocado.tracker.offences.forms.OffenceForm"/>
<insert id="addOffence" parameterClass="offence">
<selectKey resultClass="java.lang.String" keyProperty="id" >
SELECT OFFENCES_SEQ.NEXTVAL AS ID FROM DUAL
</selectKey>
insert into
OFFENCES (OFFENCE_UUID, STATUS, AUTHORITY, TICKET_NUMBER,
OFFENCE,
EMPLOYEE_UUID, DATETIME_OF_OFFENCE, DATETIME_OF_TICKET,
SITE, FLEET_UUID, LOC_OF_OFFENCE, POSTCODE, FINE,
ORDER_NUMBER,
ROUTE_ID, ENTERED_BY, ASSIGNED_TO, LAST_MODIFIED_BY,
LAST_MODIFIED,
CREATION_DATE)
values (#id#, 'new', #authority#, #ticketNumber#, #offence#,
#employeeID#, to_date(#dateOfOffence#,'dd/mm/YYYY HH24:mi'),
to_date(#dateOfTicket#,'dd/mm/YYYY HH24:mi'),
#site#, #fleetID#, #locOfOffence#, #postcode#, #fine#,
#orderNumber#, #routeID#, #enteredBy#, #assignedTo#,
sysdate, sysdate);
</insert>
org.springframework.jdbc.UncategorizedSQLException: (SqlMapClient
operation): en
countered SQLException [
--- The error occurred in
com/ocado/tracker/offences/dao/sql/OffenceSQL.xml.
--- The error occurred while applying a parameter map.
--- Check the addOffence-InlineParameterMap.
--- Check the parameter mapping for the 'id' property.
--- Cause: java.sql.SQLException: Invalid column type]; nested exception
is
com.
ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
com/ocado/tracker/offences/dao/sql/OffenceSQL.xml.
--- The error occurred while applying a parameter map.
--- Check the addOffence-InlineParameterMap.
--- Check the parameter mapping for the 'id' property.
--- Cause: java.sql.SQLException: Invalid column type
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
com/ocado/tracker/offences/dao/sql/OffenceSQL.xml.
--- The error occurred while applying a parameter map.
--- Check the addOffence-InlineParameterMap.
--- Check the parameter mapping for the 'id' property.
--- Cause: java.sql.SQLException: Invalid column type
Caused by: java.sql.SQLException: Invalid column type
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUp
date(GeneralStatement.java:86)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExe
cutorDelegate.java:500)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionI
mpl.java:89)
-----Original Message-----
From: Aitor Imaz [mailto:[EMAIL PROTECTED]
Sent: 04 February 2005 12:19
To: ibatis-user-java@incubator.apache.org
Subject: Re: Mapping Problem
I can see one thing, in your <selectKey> the resultClass attribute
should be a String if I'm not wrong. You should put there the class of
the primary key returned, not of the object itself. Maybe that's the
cause of the error (the selectkey mapping)?
Hope it helps,
Aitor
_____________________________________________________________________
This message has been checked for all known viruses by the
MessageLabs Virus Control Centre.
This message has been checked for all known viruses by the MessageLabs
Virus Control Centre.
*********************************************************************
Notice: This email is confidential and may contain copyright material
of
Ocado Limited (the
"Company"). Opinions and views expressed in this message may not
necessarily reflect the
opinions and views of the Company.
If you are not the intended recipient, please notify us immediately and
delete all copies of
this message. Please note that it is your responsibility to scan this
message for viruses.
Company reg. no. 3875000.
Ocado Limited
Titan Court
3 Bishops Square
Hatfield Business Park
Hatfield
Herts
AL10 9NE
=== message truncated ===
__________________________________
Do you Yahoo!?
Meet the all-new My Yahoo! - Try it today!
http://my.yahoo.com
_____________________________________________________________________
This message has been checked for all known viruses by the
MessageLabs Virus Control Centre.
This message has been checked for all known viruses by the MessageLabs Virus
Control Centre.
*********************************************************************
Notice: This email is confidential and may contain copyright material of Ocado Limited
(the "Company"). Opinions and views expressed in this message may not
necessarily reflect the opinions and views of the Company.
If you are not the intended recipient, please notify us immediately and delete
all copies of this message. Please note that it is your responsibility to scan
this message for viruses.
Company reg. no. 3875000.
Ocado Limited
Titan Court
3 Bishops Square
Hatfield Business Park
Hatfield
Herts
AL10 9NE
*********************************************************************
