I try to use batch update to insert multiple records into AS400 but I get an Exception :
<div align="left">
[1/27/05 14:50:18:565 MYT] 2a7cd SystemOut U java.sql.BatchUpdateException: [SQL7008] EXAMSCORE in STUDENT not valid for operation.
[1/27/05 14:50:18:565 MYT] 2a7cd SystemOut U at com.ibm.as400.access.AS400JDBCPreparedStatement.ex ecuteBatch(AS400JDBCPreparedStatement.java:833)
[1/27/05 14:50:18:565 MYT] 2a7cd SystemOut U at com.ibm.ejs.cm.cache.CachedStatement.executeBatch( CachedStatement.java:139)
[1/27/05 14:50:18:565 MYT] 2a7cd SystemOut U at com.ibm.ejs.cm.proxy.StatementProxy.executeBatch(S tatementProxy.java:234)
[1/27/05 14:50:18:565 MYT] 2a7cd SystemOut U at PointReq.insertTRACS(PointReq.java:727)
[1/27/05 14:50:18:565 MYT] 2a7cd SystemOut U at PointReq.doPost(PointReq.java:78)
[1/27/05 14:50:18:565 MYT] 2a7cd SystemOut U at javax.servlet.http.HttpServlet.service(HttpServlet .java:760)
[1/27/05 14:50:18:565 MYT] 2a7cd SystemOut U at javax.servlet.http.HttpServlet.service(HttpServlet .java:853)
[1/27/05 14:50:18:565 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.StrictServletInstanc e.doService(ServletManager.java:827)
[1/27/05 14:50:18:566 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.StrictLifecycleServl et._service(StrictLifecycleServlet.java:167)
[1/27/05 14:50:18:566 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.IdleServletState.ser vice(StrictLifecycleServlet.java:297)
[1/27/05 14:50:18:566 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.StrictLifecycleServl et.service(StrictLifecycleServlet.java:110)
[1/27/05 14:50:18:566 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.ServletInstance.serv ice(ServletManager.java:472)
[1/27/05 14:50:18:566 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.ValidServletReferenc eState.dispatch(ServletManager.java:1012)
[1/27/05 14:50:18:566 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.ServletInstanceRefer ence.dispatch(ServletManager.java:913)
[1/27/05 14:50:18:566 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.WebAppRequestDispatc her.handleWebAppDispatch(WebAppRequestDispatcher.j ava:721)
[1/27/05 14:50:18:566 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.WebAppRequestDispatc her.dispatch(WebAppRequestDispatcher.java:374)
[1/27/05 14:50:18:566 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.WebAppRequestDispatc her.forward(WebAppRequestDispatcher.java:118)
[1/27/05 14:50:18:566 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.srt.WebAppInvoker.doForward (WebAppInvoker.java:134)
[1/27/05 14:50:18:566 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.srt.WebAppInvoker.handleInv ocationHook(WebAppInvoker.java:239)
[1/27/05 14:50:18:566 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.invocation.CachedInvocation .handleInvocation(CachedInvocation.java:67)
[1/27/05 14:50:18:566 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.srp.ServletRequestProcessor .dispatchByURI(ServletRequestProcessor.java:151)
[1/27/05 14:50:18:567 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.oselistener.OSEListenerDisp atcher.service(OSEListener.java:317)
[1/27/05 14:50:18:567 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.http11.HttpConnection.handl eRequest(HttpConnection.java:60)
[1/27/05 14:50:18:567 MYT] 2a7cd SystemOut U at com.ibm.ws.http.HttpConnection.readAndHandleReques t(HttpConnection.java:477)
[1/27/05 14:50:18:567 MYT] 2a7cd SystemOut U at com.ibm.ws.http.HttpConnection.run(HttpConnection. java:351)
[1/27/05 14:50:18:567 MYT] 2a7cd SystemOut U at com.ibm.ws.util.CachedThread.run(ThreadPool.java:1 44)
[1/27/05 14:50:18:616 MYT] 2a7cd SystemOut U com.ibm.ejs.cm.exception.WorkRolledbackException: Outstanding work on this connection which was not comitted or rolledback by the user has been rolledback.
[1/27/05 14:50:18:616 MYT] 2a7cd SystemOut U at com.ibm.ejs.cm.pool.ConnectO.decRef(ConnectO.java: 398)
[1/27/05 14:50:18:616 MYT] 2a7cd SystemOut U at com.ibm.ejs.cm.pool.ConnectionPool.freeConnection( ConnectionPool.java:213)
[1/27/05 14:50:18:616 MYT] 2a7cd SystemOut U at com.ibm.ejs.cm.proxy.ConnectionProxy.close(Connect ionProxy.java:869)
[1/27/05 14:50:18:616 MYT] 2a7cd SystemOut U at PointReq.insertTRACS(PointReq.java:813)
[1/27/05 14:50:18:617 MYT] 2a7cd SystemOut U at PointReq.doPost(PointReq.java:78)
[1/27/05 14:50:18:617 MYT] 2a7cd SystemOut U at javax.servlet.http.HttpServlet.service(HttpServlet .java:760)
[1/27/05 14:50:18:617 MYT] 2a7cd SystemOut U at javax.servlet.http.HttpServlet.service(HttpServlet .java:853)
[1/27/05 14:50:18:617 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.StrictServletInstanc e.doService(ServletManager.java:827)
[1/27/05 14:50:18:617 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.StrictLifecycleServl et._service(StrictLifecycleServlet.java:167)
[1/27/05 14:50:18:617 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.IdleServletState.ser vice(StrictLifecycleServlet.java:297)
[1/27/05 14:50:18:617 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.StrictLifecycleServl et.service(StrictLifecycleServlet.java:110)
[1/27/05 14:50:18:617 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.ServletInstance.serv ice(ServletManager.java:472)
[1/27/05 14:50:18:617 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.ValidServletReferenc eState.dispatch(ServletManager.java:1012)
[1/27/05 14:50:18:617 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.ServletInstanceRefer ence.dispatch(ServletManager.java:913)
[1/27/05 14:50:18:617 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.WebAppRequestDispatc her.handleWebAppDispatch(WebAppRequestDispatcher.j ava:721)
[1/27/05 14:50:18:617 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.WebAppRequestDispatc her.dispatch(WebAppRequestDispatcher.java:374)
[1/27/05 14:50:18:617 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.webapp.WebAppRequestDispatc her.forward(WebAppRequestDispatcher.java:118)
[1/27/05 14:50:18:618 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.srt.WebAppInvoker.doForward (WebAppInvoker.java:134)
[1/27/05 14:50:18:618 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.srt.WebAppInvoker.handleInv ocationHook(WebAppInvoker.java:239)
[1/27/05 14:50:18:618 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.invocation.CachedInvocation .handleInvocation(CachedInvocation.java:67)
[1/27/05 14:50:18:618 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.srp.ServletRequestProcessor .dispatchByURI(ServletRequestProcessor.java:151)
[1/27/05 14:50:18:618 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.oselistener.OSEListenerDisp atcher.service(OSEListener.java:317)
[1/27/05 14:50:18:618 MYT] 2a7cd SystemOut U at com.ibm.servlet.engine.http11.HttpConnection.handl eRequest(HttpConnection.java:60)
[1/27/05 14:50:18:618 MYT] 2a7cd SystemOut U at com.ibm.ws.http.HttpConnection.readAndHandleReques t(HttpConnection.java:477)
[1/27/05 14:50:18:618 MYT] 2a7cd SystemOut U at com.ibm.ws.http.HttpConnection.run(HttpConnection. java:351)
[1/27/05 14:50:18:618 MYT] 2a7cd SystemOut U at com.ibm.ws.util.CachedThread.run(ThreadPool.java:1 44)
</div id="left">
Attached is my code snippet:
Code:
import java.sql.*;
public class PointReq extends HttpServlet {
public void init(ServletConfig config) throws ServletException {
super.init(config);
}
public void doGet (HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
doPost(req, res);
}
public void doPost (HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
String[] point = {80, 60, 100, 40};
int state = insertTRACS(point);
// DO SOMETHING
}
private int insertTRACS (String[] score) {
int status = 0;
String sqlQuery = "";
Connection connTRACS = null;
PreparedStatement insertstmt = null;
try {
if (connTRACS == null) connTRACS = getConnTRACS(); // GET CONNECTION
connTRACS.setAutoCommit(false);
sqlQuery = "INSERT INTO EXAMSCORE (SCORE) VALUES (?)";
insertstmt = connTRACS.prepareStatement(sqlQuery);
for (int i = 0; i < score.length; i++) {
insertstmt.setString(1, score[i]);
insertstmt.addBatch();
}
int numUpdates[] = insertstmt.executeBatch();
for (int i = 0; i < numUpdates.length; i++) {
if (numUpdates[i] == -2) // insert success
status = 1;
else { // insert fail
status = 0;
break;
}
}
if (status != 0)
connTRACS.commit();
else
connTRACS.rollback();
} catch (Exception e) {
// GET MESSAGE
} finally {
try {
insertstmt.close();
connTRACS.close();
} catch (Exception e) {
// GET MESSAGE
}
}
return status;
}
}
Could anyone tell me what is going wrong and how to fix this problem?
Thanks for any reply.
Batch Update to AS400
