3 Replies Latest reply on Sep 21, 2003 6:01 AM by gorges

    Can not insert records properly

    gorges
    gorges Sep 21, 2003 2:42 AM

      The problem is that:I'm using PreparedStatement of java to insert some records into an oracle database table with no primary key set,When I started JBoss,the function was fine and the desired records were inserted,but after some records have been inserted,when I insert one record,JBoss inserted two same records into the table.
      Could anyone tell me the cause and how to solve it?


      I'm using jboss-3.2.1_tomcat-4.1.24,and have configured my oracle datasource as follows in oracle-ds.xml:

      <local-tx-datasource>
      <jndi-name>OracleDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@localhost:1521:ORACLEDB</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>VISITOR</user-name>
      VISITOR
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      </local-tx-datasource>



      The code to insert a record is:
      DataSource ds = (DataSource) ctx.lookup("java:/OracleDS");
      Connection conn = ds.getConnection();
      PreparedStatement ps = conn.prepareStatement("insert into ROLE_PERMISSION(ROLENAME,PERMISSION) values(?,?)");
      ps.setString(1, role);
      ps.setString(2, perm);
      ps.executeUpdate();

      • 3783 Views
      • Tags:
        • 1. Re: Can not insert records properly
          gorges
          gorges Sep 21, 2003 3:18 AM (in response to gorges)

          I found that It's a problem of my JSP

            • Actions
          • 2. Re: Can not insert records properly
            jonlee
            jonlee Sep 21, 2003 3:25 AM (in response to gorges)

            The problem is most likely with your SQL, not with JBoss.

            You insert a record even if there is an existing record. There is no check to prevent this. You need to have checked whether there is an existing record and based on the result, insert or update accordingly. By not having a primary key or restriction on an existing "matching" record, there is no exception if you do insert an identical copy.

              • Actions
            • 3. Re: Can not insert records properly
              gorges
              gorges Sep 21, 2003 6:01 AM (in response to gorges)

              Below is my JSP.It managies the relationship between the role and the permission of the role.Role_PermMan is the JavaBean to carry out the function.In which permExists() determines if the role already has the permission,assignPerm() assigns a permission to a role,revokePerm() revokes a permission from a role.
              The "actionType" is used to determine what kind of action to take.
              Sometimes,it just insert one record;but sometimes it insert two same records.This seems completely random.I'm confused.

              <jsp:useBean id="hasConPerms" scope="page" class="SecurityMan.Role_PermMan" />
              <%
              String a=request.getParameter("actionType");
              String role=(String)session.getAttribute("mRole1");
              if(a.equals("grant")){
              String grant=(String)request.getParameter("allcperms");
              if(hasConPerms.permExists(role,grant)){
              %>
              The Role already has the permission!
              <%
              }
              else
              {
              hasConPerms.assignPerm(role,grant);
              %>
              The permission has been assigned to the role!
              <%
              }
              }
              else if(a.equals("revoke")){
              String revoke=(String)request.getParameter("hascperms");
              hasConPerms.revokePerm(role,revoke);
              %>
              The permission has been revoked form the role!
              <%
              }
              %>

                • Actions