Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69

    Angry Unanswered: DB insert dilema.

    I'm receiving a "javax.servlet.ServletException: ORA-02291: integrity constraint (SYSTEM.FK_Risk_8154) violated - parent key not found" error and I can't understand why. If you examine the attached diagram the only parent of the Risk entity is Document Stats which I populate beforehand in order to have the required entry for the risk entry.

    This is a copy & paste of the html presented in a debug page:


    1136381798 1/4/2006 0:0:0 1/4/2006 0:0:0
    1136381721 1/4/2006 0:0:0 1/4/2006 0:0:0
    1136382026 1/4/2006 0:0:0 1/4/2006 0:0:0
    1136382053 1/4/2006 0:0:0 1/4/2006 0:0:0


    Process Document Creation
    insert into "Risk" ("Number", "Condition", "Failure_Mode", "Risk_Level", "Quantity", "Incident_Date", "Document_StatsID") values (1136382053, Test, x, Low, 23, 2006-01-04, 1136382053)


    Where the first entries are a table of the contents of Document Stats and later is the insert command attempted.

    Here's the .jsp code just in case:

    Code:
    <%@page contentType="text/html"%>
    <%@page pageEncoding="UTF-8"%>
    <%@page import="SQLConnection.*"%>
    <%@page import="Dreamer.*"%>
    <%@page import="java.sql.*"%>
    
    
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
       "http://www.w3.org/TR/html4/loose.dtd">
    
    <html>
        <head>
            <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
            <title>Create document</title>
        </head>
        <body>
    
            <h1>Process Document Creation</h1>
            <%
                Validator val = new Validator();
                out.println();
                boolean incorrect = false;
                String [] date = new String[1];
                date[0]=request.getParameter("date");
                if(request.getParameter("condition").trim().equals("")) {
                    out.println("<li>Condition description can't be blank.</li>");
                    incorrect = true;
                }
                if(request.getParameter("failure").trim().equals("")) {
                    out.println("<li>Failure mode description can't be blank.</li>");
                    incorrect = true;
                }
                if(request.getParameter("level").equals("Select")) {
                    out.println("<li>Please select a valid risk level. The value 'Select' is invalid</li>");
                    incorrect = true;
                }
                if(request.getParameter("units").equals("") ||
                        (val.isNumeric(request.getParameter("units").split("-")[0]) &&
                        Integer.parseInt(request.getParameter("units")) <0)) {
                    out.println("<li>Units affected must be a positive or zero value. Value: "+request.getParameter("units")+" is not valid.</li>");
                    incorrect = true;
                }
                if(val.dc.correctDates(date)==null) {
                    out.println("<li>The format of the date: "+ date[0] + " is incorrect. The correct format is MM/DD/YYYY.</li>");
                    incorrect = true;
                }
                if(incorrect) {
                    out.println("<FORM>Please press the back button and correct the items described above." );
                    out.println("<INPUT TYPE='button' VALUE='Back' onClick='history.go(-1);return true;'> </FORM>");
                } else {
                    DBConnection db = new DBConnection("jdbc:oracle:thin:@praidbs001:1521:logcreat","SYSTEM","MANAGER",new DBConnection().Oracle);
                    ResultSet rs;
                    String sql;
                    rs=db.Query("select sysdate from dual");
                    rs.next();
                    Date d = rs.getDate(1);
                    int id;
                    rs=db.Query("SELECT round((sysdate-to_date('1-JAN-1970'))*(24*60*60)) FROM dual");
                    rs.next();
                    id=rs.getInt(1);
                    System.out.println("id: "+id);
                    PreparedStatement statement = db.getConnection().prepareStatement("insert into \"Document Stats\" " +
                            "(\"Document Stat ID\", \"Creation Date\", \"Modification Date\") values (?, ?, ?)");
                    statement.setBigDecimal(1, java.math.BigDecimal.valueOf(id));
                    statement.setDate(2, d);
                    statement.setDate(3, d);
                    statement.execute();
                    statement.close();
                    db.drawTable(db.Query("select * from \"Document Stats\""),response.getWriter(),"","",-1,false,0);
                    sql="insert into 'Risk'('Number', 'Condition', " +
                            "'Failure_Mode', 'Risk_Level', 'Quantity', 'Incident_Date', " +
                            "'Document_StatsID') values ("+id+", "+
                            request.getParameter("condition")+", "+request.getParameter("failure")+
                            ", "+request.getParameter("level")+", "+request.getParameter("units")+
                            ", to_date('"+request.getParameter("date")+"','MM/DD/YYYY'), "+id+")";
                    System.out.println(sql);
                    statement=db.getConnection().prepareStatement("insert into \"Risk\" " +
                            "(\"Number\", \"Condition\", \"Failure_Mode\", \"Risk_Level\", \"Quantity\"," +
                            " \"Incident_Date\", \"Document_StatsID\") values (?, ?, ?, ?, ?, ?, ?)");
                    out.println("insert into \"Risk\" " +
                            "(\"Number\", \"Condition\", \"Failure_Mode\", \"Risk_Level\", \"Quantity\"," +
                            " \"Incident_Date\", \"Document_StatsID\") values ("+java.math.BigDecimal.valueOf(id)+
                            ", "+request.getParameter("condition").trim()+", "+request.getParameter("failure")+
                            ", "+request.getParameter("level")+", "+
                            java.math.BigDecimal.valueOf(Integer.parseInt(request.getParameter("units")))+", "+d.toString()+
                            ", "+java.math.BigDecimal.valueOf(id)+")");
                    //statement = db.getConnection().prepareStatement("insert into \"Risk\" " +
                    //        "(\"Number\", \"Condition\", \"Failure_Mode\", \"Risk_Level\", \"Quantity\"," +
                     //       " \"Incident_Date\", \"Document_StatsID\") values ("+java.math.BigDecimal.valueOf(id)+
                     //       ", "+request.getParameter("condition").trim()+", "+request.getParameter("failure")+
                     //       ", "+request.getParameter("level")+", "+
                     //       java.math.BigDecimal.valueOf(Integer.parseInt(request.getParameter("units")))+", "+d.toString()+
                    //        ", "+java.math.BigDecimal.valueOf(id)+")");
                    statement.setBigDecimal(1, java.math.BigDecimal.valueOf(id));
                    statement.setString(2, request.getParameter("condition").trim());
                    statement.setString(3, request.getParameter("failure"));
                    statement.setString(4, request.getParameter("level"));
                    statement.setBigDecimal(5, java.math.BigDecimal.valueOf(Integer.parseInt(request.getParameter("units"))));
                    statement.setDate(6, d);
                    statement.setBigDecimal(7, java.math.BigDecimal.valueOf(id));
                    //statement.execute();
                    statement.close();
                    db.closeConnection();
                }
            %>
        </body>
    </html>
    The last statement.execute(); is in comments to get the output pasted earlier and is where the error is reported when examining the resulting servlet.

    The query to get the contents of Document Stats is ran just after the first insert to verify that the values are there when the second insert try.

    If you have any idea or need more information please let me know. Thanks in advance.

    Attached is the UML diagram of the database.
    Attached Thumbnails Attached Thumbnails ORM Diagram.jpg  

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This query should show parent-child relationship related to the failing constraint:
    Code:
    SELECT p.table_name parent_table, p.constraint_type parent_type, p.constraint_name parent_cons_name,
           c.table_name child_table,  c.constraint_type child_type,  c.constraint_name child_cons_name
    FROM user_constraints p, user_constraints c
    WHERE c.constraint_type = 'R'
      AND c.constraint_name = 'FK_RISK_8154'
      AND c.r_constraint_name = p.constraint_name;
    This should give you columns that make the constraint:
    Code:
    SELECT column_name
    FROM user_cons_columns
    WHERE constraint_name = 'FK_RISK_8154';
    The "parent table" should be populated before the child record is inserted into the "child table"; you already know that. It seems (regarding the UML model) that this is OK. What do those two queries give? Is there, perhaps, another column (different from 'document_stats_ID') involved?

    Let me tell you my opinion: regarding "SYSTEM.FK_Risk_8154" message, it is - generally - a BAD idea to work in the SYSTEM schema. Bad, bad idea. You'd better create another schema and do whatever you want there.
    Last edited by Littlefoot; 01-04-06 at 15:21.

  3. #3
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    After I ran the first query, with minor corrections, I got this:

    Code:
    SELECT p.table_name parent_table, p.constraint_type parent_type, p.constraint_name parent_cons_name,
           c.table_name child_table,  c.constraint_type child_type,  c.constraint_name child_cons_name
    FROM user_constraints p, user_constraints c
    WHERE c.constraint_type = 'R'
      AND c.constraint_name = 'FK_Risk_8154'
      AND c.r_constraint_name = p.constraint_name;
    Document_Stats,P,SYS_C003174,Risk,R,FK_Risk_8154

    That gave me exactly what I was looking for. There where 2 tables one Document_Stats and ont Document Stats. I was adding data to the latest and the Risk table "looks"to the other.

    About the schema I agree with you, but I haven't created an schema. If you can enligthen me I'll appreciate. I'll look around in the net but I'm asking for the help just in case.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If there are two tables with similar names - "Document_Stats" and "Document Stats", then - speaking in SQL terms - you'll have to use double quotes when referencing the second table. For example, if you use

    SELECT * FROM Document Stats;

    you'll get an error message (table or view doesn't exist), but this would be perfectly OK:

    SELECT * FROM "Document Stats";

    I don't speak Java so I can't tell whether this restriction applies to Java too, but you might keep this fact in mind.

    Regarding SYSTEM schema: it was created by default when the database was created. As you work with it, you know its password. Furthermore, as SYSTEM is the user with DBA privileges, it is capable of creating another users (user + its objects - tables, views, ... make a schema). In a few words, you'll create a new user using this command:

    CREATE USER new_user IDENTIFIED BY its_password;
    GRANT connect, resource TO new_user;

    Now connect as the "new_user" and work with it instead of SYSTEM:
    CONNECT new_user/its_password;

    P.S. If I'm not wrong, "connect" and "resource" roles are about to be abandoned in future Oracle versions. I'm not sure has the future come or not yet, but this *might* be the situation with Oracle 10g. Therefore, one will have to grant actual privileges instead of those two roles. If you'll need it, check the Oracle Documentation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •