Hi,
I'm trying to insert a list of data to a table using MyBatis Batch.The query
seems to be fine as I was able to insert the data successfully from a a SQL
client, but fails with MyBatis.
Below are the Camel DSL Route, Mapper Query, SQL Log and Exception trace
and SQL Query that run successfully on client. I researched but couldn't
find a solution as to what is causing it. I'm not interested in calling the
query with ExecutorType.SIMPLE and then loop over in a java method ( as few
suggested on web ). This would defeat the purpose of being able to iterate
over a collection in MyBatis.
I appreciate if you could shed some light on the issue.
*Camel Route:*
from("timer:foo?period=10000")
.setHeader(Exchange.HTTP_METHOD,
constant(HttpMethods.GET))
.setHeader(Exchange.HTTP_URI,
constant(RestURLs.TRANSACTION_BIDS_UNQUALIFIED_URL))
.process(new CamelGETMessageProcessor())
.to("restlet:http://localhost:8080/app/cts/transactionbids";)
.unmarshal(jaxbDataFormat)
.process(new TransactionBidsProcessor())
.to("mybatis:insertTransactionBidDetail?statementType=InsertList*executor")
.end();
*Mapper Query:*
<insert id="insertTransactionBidDetail"
parameterType="com.isone.cts.rest.binding.cts.TransactionBidsResponse">
INSERT ALL
<foreach item="p"
collection="transactionBids.transactionBid"
open="" close="" separator=")">
into TRANSACTION_BIDS_DATA_T
(message_request_id, transaction_source,
transaction_sink, transaction_user_ref,
gis_identifier, jess_etag_id)
values(#{messageRequestID,jdbcType=NUMERIC},
#{p.transactionSource,jdbcType=VARCHAR},
#{p.transactionSink,jdbcType=VARCHAR},
#{p.transactionUserRef,jdbcType=VARCHAR},
#{p.gisIdentifier,jdbcType=VARCHAR},
#{p.jessETagID,jdbcType=VARCHAR}
</foreach>
)SELECT * FROM dual
</insert>
*SQL Log*
### SQL: INSERT ALL into TRANSACTION_BIDS_DATA_T
(message_request_id, transaction_source, transaction_sink,
transaction_user_ref, gis_identifier, jess_etag_id) values(?, ?,
?, ?, ?, ? ) into TRANSACTION_BIDS_DATA_T
(message_request_id, transaction_source, transaction_sink,
transaction_user_ref, gis_identifier, jess_etag_id) values(?, ?,
?, ?, ?, ? ) into TRANSACTION_BIDS_DATA_T
(message_request_id, transaction_source, transaction_sink,
transaction_user_ref, gis_identifier, jess_etag_id) values(?, ?,
?, ?, ?, ? ) into TRANSACTION_BIDS_DATA_T
(message_request_id, transaction_source, transaction_sink,
transaction_user_ref, gis_identifier, jess_etag_id) values(?, ?,
?, ?, ?, ? ) into TRANSACTION_BIDS_DATA_T
(message_request_id, transaction_source, transaction_sink,
transaction_user_ref, gis_identifier, jess_etag_id) values(?, ?,
?, ?, ?, ? ) into TRANSACTION_BIDS_DATA_T
(message_request_id, transaction_source, transaction_sink,
transaction_user_ref, gis_identifier, jess_etag_id) values(?, ?,
?, ?, ?, ? ) into TRANSACTION_BIDS_DATA_T
(message_request_id, transaction_source, transaction_sink,
transaction_user_ref, gis_identifier, jess_etag_id) values(?, ?,
?, ?, ?, ? ) into TRANSACTION_BIDS_DATA_T
(message_request_id, transaction_source, transaction_sink,
transaction_user_ref, gis_identifier, jess_etag_id) values(?, ?,
?, ?, ?, ? ) into TRANSACTION_BIDS_DATA_T
(message_request_id, transaction_source, transaction_sink,
transaction_user_ref, gis_identifier, jess_etag_id) values(?, ?,
?, ?, ?, ? ) into TRANSACTION_BIDS_DATA_T
(message_request_id, transaction_source, transaction_sink,
transaction_user_ref, gis_identifier, jess_etag_id) values(?, ?,
?, ?, ?, ? ) into TRANSACTION_BIDS_DATA_T
(message_request_id, transaction_source, transaction_sink,
transaction_user_ref, gis_identifier, jess_etag_id) values(?, ?,
?, ?, ?, ? ) into TRANSACTION_BIDS_DATA_T
(message_request_id, transaction_source, transaction_sink,
transaction_user_ref, gis_identifier, jess_etag_id) values(?, ?,
?, ?, ?, ? ) into TRANSACTION_BIDS_DATA_T
(message_request_id, transaction_source, transaction_sink,
transaction_user_ref, gis_identifier, jess_etag_id) values(?, ?,
?, ?, ?, ? ) into TRANSACTION_BIDS_DATA_T
(message_request_id, transaction_source, transaction_sink,
transaction_user_ref, gis_identifier, jess_etag_id) values(?, ?,
?, ?, ?, ? ) into TRANSACTION_BIDS_DATA_T
(message_request_id, transaction_source, transaction_sink,
transaction_user_ref, gis_identifier, jess_etag_id) values(?, ?,
?, ?, ?, ? )SELECT * FROM dual
Below is the exception thrown.
[Camel (camel-1) thread #0 - timer://foo] DEBUG
org.apache.camel.processor.DefaultErrorHandler - Failed delivery for
(MessageId: ID-sporeddy-53230-1406038955619-0-9 on ExchangeId:
ID-sporeddy-53230-1406038955619-0-8). On delivery attempt: 0 caught:
org.apache.ibatis.exceptions.PersistenceException:
### Error committing transaction. Cause:
org.apache.ibatis.executor.BatchExecutorException:
transactionBidsMapper.insertTransactionBidDetail (batch index #1) failed.
Cause: java.sql.BatchUpdateException: ORA-00933: SQL command not properly
ended
### Cause: org.apache.ibatis.executor.BatchExecutorException:
transactionBidsMapper.insertTransactionBidDetail (batch index #1) failed.
Cause: java.sql.BatchUpdateException: ORA-00933: SQL command not properly
ended
*SQL Query on client
*
INSERT ALL
INTO TRANSACTION_BIDS_DATA_T
(
message_request_id,
transaction_source,
transaction_sink,
transaction_user_ref,
gis_identifier,
jess_etag_id
)
VALUES
(
1406037318681,
'NODEISONE14',
'NODENYISO0',
'USERREFGPKL44220',
'HASHMASHSMASH',
'SCA_PSE _1110RCA'
)
INTO TRANSACTION_BIDS_DATA_T
(
message_request_id,
transaction_source,
transaction_sink,
transaction_user_ref,
gis_identifier,
jess_etag_id
)
VALUES
(
1406037318681,
'NODEISONE14',
'NODENYISO0',
'USERREFGPKL44220',
'HASHMASHSMASH',
'SCA_PSE _1110RCA'
)
INTO TRANSACTION_BIDS_DATA_T
(
message_request_id,
transaction_source,
transaction_sink,
transaction_user_ref,
gis_identifier,
jess_etag_id
)
VALUES
(
1406037318681,
'NODEISONE14',
'NODENYISO0',
'USERREFGPKL44220',
'HASHMASHSMASH',
'SCA_PSE _1110RCA'
)
INTO TRANSACTION_BIDS_DATA_T
(
message_request_id,
transaction_source,
transaction_sink,
transaction_user_ref,
gis_identifier,
jess_etag_id
)
VALUES
(
1406037318681,
'NODEISONE14',
'NODENYISO0',
'USERREFGPKL44220',
'HASHMASHSMASH',
'SCA_PSE _1110RCA'
)
SELECT * FROM dual
--
View this message in context:
http://camel.465427.n5.nabble.com/Camel-2-13-1-MyBatis-Batch-Insert-to-Oracle-11g-Table-ORA-00933-SQL-command-not-properly-ended-tp5754248.html
Sent from the Camel - Users mailing list archive at Nabble.com.
