0 Replies Latest reply on Jan 6, 2003 4:11 AM by Dave E

    JBoss3.0.0 & MySql insert problem

    Dave E Newbie

      Hi,

      My problem is that my user creation works sporadically. Often when somebody creates a user, they can't log in right away even though the data is visible in the database. If I restart the server (or wait n minutes) the data can be selected and a user can log in. Even thru mysqlfront I can see that the data has been inserted, but I cannot select a user via

      "SELECT * FROM user_detail WHERE user_name='auser'"
      (auser being the recently created user)

      Does JBoss queue updates or something? Or perhaps it's a setting in mysql? Any thoughts/ideas would be greatly appreciated

      Cheers,
      Dave

      CODE:


      Here is my code for inserting data:

      /**
      * Inserts a new user into database
      * @param UserDetail user
      * @return void
      *
      * */

      public void insert (UserDetail user) throws UserNameExistsException{
      Connection con = null;
      Statement stmt = null;
      String insertSQL = " INSERT INTO MOXROCKS.USER_DETAIL ( " +
      " USER_ID , FIRST_NAME ," +
      " LAST_NAME , ADDRESS1 ," +
      " ADDRESS2, EMAIL_ADDRESS ," +
      " NICKNAME , USER_NAME ," +
      " CREATED_DATE, UPDATED_DATE, " +
      " PERMISSIONS ) " +
      " VALUES " +
      " ( 0 , " +
      DBUtil.str(user.getFirstName()) + " , " +
      DBUtil.str(user.getLastName()) +" , " +
      DBUtil.str(user.getAddress1()) +" , " +
      DBUtil.str(user.getAddress2()) +" , " +
      DBUtil.str(user.getEmail()) +" , " +
      DBUtil.str(user.getNickName()) +" , " +
      DBUtil.str(user.getUserName())+" , "+
      " CURDATE() , " +
      " CURDATE(), "+
      MRPermissions.getDefaultAccess()+" ) " ;

      String insertPrincipals = " INSERT INTO MOXROCKS.PRINCIPALS (PRINCIPAL_ID, PASSWORD) "+
      " VALUES ( "+DBUtil.str(user.getUserName())+ ", "+DBUtil.str(PasswordUtils.encrypt(user.getPassword()))+" )";
      String insertRoles = " INSERT INTO MOXROCKS.ROLES (PRINCIPAL_ID, ROLE, ROLE_GROUP) " +
      " VALUES ("+DBUtil.str(user.getUserName())+" , "+ DBUtil.str(user.getUserRole().getRoleName())+" , " +
      DBUtil.str(user.getUserRole().getRoleGroup()) + " )";
      sLog.debug("insertPrincipals sql = " +insertPrincipals);
      sLog.debug("insertRoles sql = " +insertRoles);
      sLog.debug("insertSQL sql = " +insertSQL);
      try {
      //check to see if this userName already exists. Throws UserNameExistsException
      this.checkUserNameExists(user);
      con = ConnectionManager.getConnection();
      stmt = con.createStatement();
      //con.setAutoCommit(false);
      stmt.addBatch(insertSQL);
      stmt.addBatch(insertRoles);
      stmt.addBatch(insertPrincipals);
      int results [] = stmt.executeBatch();
      //con.commit();
      for (int x=0; x<results.length; x++){
      sLog.debug("results["+x+"]="+results[x]);
      }

      } catch(UserNameExistsException un) {
      sLog.debug("User name exists. Exiting and throwing UserNameExistsException");
      throw new UserNameExistsException();
      } catch(SQLException e) {
      //error, role back
      try {
      con.rollback();
      } catch(Exception ex) {
      ex.printStackTrace();
      }
      e.printStackTrace();
      }catch(Exception e){
      e.printStackTrace();
      }
      finally {
      ConnectionManager.close(stmt);
      ConnectionManager.close(con);
      }
      }