Exception while trying to add a new user to the database

[sumanta panda]

Dear Sir,

As per my requirement in my existing project I am adding a GID column and this GID column is inserted in the LOGIN_USER table.
So I have only change my existing function addUserMasterData().

private void addUserMasterData(AbcUserValue user) throws AbcUserException, SQLException, EJBException {

/*
String sql =
"INSERT INTO LOGIN_USER (LOGIN_NAME, PASSWORD,EMAIL,EMP_ID ) values (?, ?, ?, ?)";// //With out GID it is working

*/
String sql =
"INSERT INTO LOGIN_USER (LOGIN_NAME, PASSWORD,EMAIL,EMP_ID,GID ) values (?, ?, ?, ?, ?)";

try
            {
               /*   con = DbUtil.fetchConnection();
                  ps = con.prepareStatement(sql);
                  ps.setString(1, user.getLoginName());
                  ps.setString(2, user.getPasswd());
                  ps.setString(3, user.getEmail());
                  ps.setString(4, user.getEmplID());*/ //With out GID it is working
                  

             con = DbUtil.fetchConnection();
                  ps = con.prepareStatement(sql);
                  ps.setString(1, user.getLoginName());
                  ps.setString(2, user.getPasswd());
                  ps.setString(3, user.getEmail());
                  ps.setString(4, user.getEmplID());
                  ps.setString(5, user.getGid());

);
       System.out.println("The Data is  login name::"+user.getLoginName()+ ",password::" +user.getPasswd()+ ",Email::"  +user.getEmail() + ",Emp ID::"  +user.getEmplID()+",gid::" + user.getGid());//The value is coming properly

          ps.executeUpdate();//This function not working after adding gid
                  System.out.println("The data is updated-------->"+  ps.executeUpdate());
            }
            catch (SQLException sqe)
            {
                  log.error("SQLException sqlstate is " + sqe.getSQLState());
                  log.error("Exception while trying to add a new user to the database", sqe);
                  if (sqe.getSQLState().equals("23000"))
                  {
                        getSessionContext().setRollbackOnly();
                        throw new AbcUserException("The login name you chose is already in use. Please select another one.", AbcUserException.LOGIN_NOT_UNIQUE);
                  } else if (sqe.getSQLState().equals("S0001"))
                  {
                        getSessionContext().setRollbackOnly();
                        throw new AbcUserException("The login name you chose is already in use. Please select another one.", AbcUserException.USER_EXISTS);
                  }
                  throw sqe;
            }
            catch (EJBException ejbe)
            {
                  log.warn("Could not establish connection to the database to add a new user.", ejbe);
                  throw ejbe;
            }
            finally
            {
                  cleanupDBStmt(ps);
                  cleanupDBConn(con);
            }
      }

While fill the user form and submit I am getting the exception.

Manually if I insert into the LOGIN_USER table which is inserted in the database without any problem.
INSERT INTO LOGIN_USER
(LOGIN_NAME, PASSWORD, EMAIL, EMPL_ID, GID) values(‘s.panda’,’ password’,’ [e-mail]’, 1234,123456) //Properly inserted.


Same thing I have done in function which is causing exception.If I will not add the gid value in the programming level then it is working.But after adding gid value which is causing exception.


Note::I am always inserted the unique value.


Execption ::

[9/23/09 23:47:54:219 IST] 00000031 SystemOut O The Data is login name:: s.panda,password::password, Email:: [e-mail],Emp ID:: 1234,gid ::123456 //The value is comming properly

[9/23/09 23:47:55:281 IST] 00000031 SystemOut O ERROR 2009-09-23 23:47:55,266 UserManagerBean.java:testar2 - SQLException sqlstate is 23000
[9/23/09 23:47:55:406 IST] 00000031 SystemOut O ERROR 2009-09-23 23:47:55,281 UserManagerBean.java:testar2 - Exception while trying to add a new user to the database
com.ibm.websphere.ce.cm.DuplicateKeyException: [IBM][SQLServer JDBC Driver][SQLServer]Violation of PRIMARY KEY constraint 'PK_ABC_USER'. Cannot insert duplicate key in object 'dbo.ABC_USER'.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:67)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:521) at com.ibm.websphere.rsadapter.GenericDataStoreHelper.mapExceptionHelper(GenericDataStoreHelper.java:523)
at com.ibm.websphere.rsadapter.GenericDataStoreHelper.mapException(GenericDataStoreHelper.java:578)
at com.ibm.ws.rsadapter.jdbc.WSJdbcUtil.mapException(WSJdbcUtil.java:909)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeUpdate(WSJdbcPreparedStatement.java:759)
at com.siemens.ssl.abc.ejb.user.UserManagerBean.addUserMasterData(UserManagerBean.java:3504)
at com.siemens.ssl.abc.ejb.user.UserManagerBean.addUser(UserManagerBean.java:591)
at com.siemens.ssl.abc.ejb.user.EJSRemoteStatelessUserManager_1bab77e9.addUser(EJSRemoteStatelessUserManager_1bab77e9.java:1966)
at com.siemens.ssl.abc.ejb.user._UserManager_Stub.addUser(_UserManager_Stub.java:475)
at com.siemens.ssl.abc.web.user.UserManagerAccess.addUser(UserManagerAccess.java:166)
at com.siemens.ssl.abc.web.user.CrudUserAction.saveMasterData(CrudUserAction.java:648)
at com.siemens.ssl.abc.web.user.CrudUserAction.perform(CrudUserAction.java:94)
at org.apache.struts.action.ActionServlet.processActionPerform(ActionServlet.java:1787)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1586)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:510)
at org.apache.struts.action.AbcActionServlet.doPost(AbcActionServlet.java:147)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1146)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:592)
at com.ibm.ws.wswebcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:481)
at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:90)
at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:748)
at com.ibm.ws.wswebcontainer.WebContainer.handleRequest(WebContainer.java:1466)
at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:122)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:458)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:387)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:267)
at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminators(NewConnectionInitialReadCallback.java:214)
at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:113)
at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:165)
at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:136)
at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:196)
at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:751)
at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:881)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1497)
[9/23/09 23:47:56:031 IST] 00000031 SystemOut O ERROR 2009-09-23 23:47:56,031 CrudUserAction.java:testar2 - Cannot store the user data!
[9/23/09 23:47:56:031 IST] 00000031 SystemOut O 5385----->Cannot store the user data!

Please suggest me how I will fix this issue.

Thanks in advance.

Regards,
Sumanta Panda

[sumanta panda]

Any one please suggest me the needful.

[Campbell Ritchie]

Patience, patience. It's barely two hours. And please don't use all those different colours; they are difficult to read.

[Jeanne Boyarsky]

com.ibm.websphere.ce.cm.DuplicateKeyException: [IBM][SQLServer JDBC Driver][SQLServer]Violation of PRIMARY KEY constraint 'PK_ABC_USER'. Cannot insert duplicate key in object 'dbo.ABC_USER'.

This is the relevant point. Check your database for a value that already exists.

[sumanta panda]

Dear Jeanne,

Thanks for the prompt response.

I have checked the value before submiting in the form.All the value in the form i am putting which is unique.

Same value i have insert manually in the database which is inserted without causing PRIMARY KEY constraint 'PK_ABC_USER'.

INSERT INTO LOGIN_USER
(LOGIN_NAME, PASSWORD, EMAIL, EMPL_ID, GID) values(‘s.panda’,’ password’,’ [e-mail]’, 1234,123456) //Properly inserted.


Please suggest me what could be the other reason for com.ibm.websphere.ce.cm.DuplicateKeyException.

Thanks for your time.
Regards,
Sumanta

[Neena Rangnani]

Hi Sumanta,

I noticed that in code table name is "LOGIN_USER" where code is inserting values. however in error if you see it saying that "Cannot insert duplicate key in object 'dbo.ABC_USER' ".

Could you please check for "dbo.ABC_USER" table/object? Please also check 'PK_ABC_USER' constraint's definition i.e which table and column it is refering.

Thanks,
Neena

[sumanta panda]

Dear Neena,

Actually i am using ABC_USER table.But the piece of code i have mentioned LOGIN_USER table.

So that is not an issue.

Please suggest if any other reason.

Thanks for your time.

Regards,
Sumanta

[sumanta panda]

Dear All,

I did a small mistake in my function addUserMasterData().

I write ps.executeUpdate() in the System.out.println("The data is updated-------->"+ ps.executeUpdate());

So it executeed two times and getting exception com.ibm.websphere.ce.cm.DuplicateKeyException

ps.executeUpdate();//This function not working after adding gid
System.out.println("The data is updated-------->"+ ps.executeUpdate());

Thanks to all for thier support.

Regards,
Sumanta