Link to home
Start Free TrialLog in
Avatar of Irv123
Irv123

asked on

how do I nsert values into database using jsp

I would like to insert data into oracle database from my jsp application but have been getting an error message which says:     java.sql.SQLException: ORA-02291: integrity constraint (*******.SYS_C0014104) violated - parent key not found. however if I insert values directly into the database  with an insert query(not via the application, all is well. I jusst cant find whats wrong with my query in the app. Could someone please assist? part of the code is bellow

// get parameter values from the resource user jsp
    String uname = request.getParameter("UserNo");
    String[] ProbDetails = request.getParameterValues("details");
    String detail = "";
 
    /**retrieve the text from  textarea name Details
    *into an array of strings.
    **/
 
    for (int a=0; a<ProbDetails.length; a++)
        {
            detail += ProbDetails[a];
        }
        
        String Sql = "INSERT INTO Problem VALUES (problem_seq.nextval,'" + uname + "',sysdate,'" + detail +"')";
         
      
        //get database connection
 
         SingletonDBConn conn= SingletonDBConn.getInstance();
         conn.callUpdateSql(Sql);
        
         
               
        /**retrieve the selected multi value parameters from parameter name resourceNo
        *into an array of strings.
        **/
 
        String[] resNumber = request.getParameterValues("resourceNo");
 
        int[] resNo = new int[resNumber.length];
        
     
 
        int[] ints = new int[resourceNo.length];
 
        for (int i=0; i < resNumber.length; i++)
            {
                resNo[i] = Integer.parseInt(resNos[i]);
                int resourceNo = resNo[i];
                String Sql2 = ("INSERT INTO ResourceProblem VALUES ('" + uname + "',problem_seq.currval,'" + resourceNo + "')");
                conn.callUpdateSql(Sql2);
            }

Open in new window

Avatar of Fekrat El Wehedi
Fekrat El Wehedi
Flag of Canada image

At which line do you get this error?
Avatar of Irv123
Irv123

ASKER

It appears the primary key would be missing when th e query :   String Sql = "INSERT INTO Problem VALUES (problem_seq.nextval,'" + uname + "',sysdate,'" + detail +"')";    is executed
are u sure that the uname that is entered is the correct one check for case sensitive ones.
Avatar of Irv123

ASKER

uname is a  String variable which contains the user name from the request object line no 2. It appears the primary key  (problemNO) is not being inserted into the table. The integrity constraint (schema.SYS_C0014104) referes , i assume, to the missing problemNo, so I have been thinking there might be something with the insert query that oracle doesnt recognise.
So which of the sql statement is the one giving you the error the first one or the second one??
Avatar of Irv123

ASKER

String Sql = "INSERT INTO Problem VALUES (problem_seq.nextval,'" + uname + "',sysdate,'" + detail +"')";
Avatar of Irv123

ASKER

Sorry it has to be the second one since its refering to referential integrity. So the problem_seq. currval is probably not returning, presumably cause the the problem_seq.nextval has not executed. I just can't figure out why its not happening through the application, when  i ts happening when i insert directly into the database? .The second query is :   String Sql2 = ("INSERT INTO ResourceProblem VALUES ('" + uname + "',problem_seq.currval,'" + resourceNo + "')");
                conn.callUpdateSql(Sql2);
what are the constraints on ResourceProblem Table?
Avatar of Irv123

ASKER

primary key(problemNo,userNo,resourceNo),
foreign Key (problemNo,userNo) references problem (problemNo,userNo) ,
foreign Key (resourceNo) references CompanyResource (resourceNo) );
which one of the referential integrity constraint is being violated?
Avatar of Irv123

ASKER

(schema.SYS_C0014104) referes to foreign Key (problemNo,userNo) , so it looks as if  problem_seq.nextval has not executed, hence problem_seq.currval can not be retrieved??
Would you mind posting the details of the following function "conn.callUpdateSql(Sql);"
Avatar of Irv123

ASKER

//calls insert and update queries
    public int callUpdateSql(String q)
    {
       try
        {
           
           stmt  = conn.createStatement();
            stmt.executeUpdate(q);
            return 0;

        }
        catch(Exception e)
        {
            e.printStackTrace();

            return 1;
        }
    }
would u mind printing the integer you get back from calling the function and post the output
Avatar of Irv123

ASKER

No integer is being returned but this is the error thats being returned:

INFO: Reloading this Context has started
java.sql.SQLException: ORA-02291: integrity constraint (*******.SYS_C0013213) violated - parent key not found

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
        at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1614)
        at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1579)
        at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225)
        at serg.SingletonDBConn.callUpdateSql(SingletonDBConn.java:90)
        at org.apache.jsp.problemReported_jsp._jspService(problemReported_jsp.java:77)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
        at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:332)
        at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
        at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:368)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
        at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
        at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
        at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
        at java.lang.Thread.run(Thread.java:595)
java.sql.SQLException: ORA-02291: integrity constraint (*******.SYS_C0014104) violated - parent key not found

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
        at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1614)
        at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1579)
        at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225)
        at serg.SingletonDBConn.callUpdateSql(SingletonDBConn.java:90)
        at org.apache.jsp.problemReported_jsp._jspService(problemReported_jsp.java:94)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
        at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:332)
        at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
        at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:368)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
        at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
        at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
        at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
        at java.lang.Thread.run(Thread.java:595)
java.sql.SQLException: ORA-02291: integrity constraint (I******.SYS_C0014104) violated - parent key not found

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
        at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1614)
        at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1579)
        at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225)
        at serg.SingletonDBConn.callUpdateSql(SingletonDBConn.java:90)
        at org.apache.jsp.problemReported_jsp._jspService(problemReported_jsp.java:94)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
        at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:332)
        at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
        at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:368)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
        at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
        at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
        at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
        at java.lang.Thread.run(Thread.java:595)
Which line is 94 in problem_reported jsp?
Avatar of Irv123

ASKER

This is part of the auto generated servlet

   String[] resNos = request.getParameterValues("resourceNo");
        int[] res = new int[resNos.length];
       
        int resourceNo;
        //int[] ints = new int[resourceNo.length];
        for (int i=0; i < resNos.length; i++)
            {
                res[i] = Integer.parseInt(resNos[i]);
                resourceNo = res[i];
                String Sql2 = ("INSERT INTO ResourceProblem VALUES ('" + uname + "',problem_seq.currval,'" + resourceNo + "')");
                conn.callUpdateSql(Sql2);
            }
           
            String message = "Your problems have been reported to the responsible personnel. Remedial action will be taken shortly";
   

LINE 94 is the conn.callUpdateSql(sql2);
One thing i forgot to ask is how is it that the method doesnot return an integer the definition of the function is:
"public int callUpdateSql(String q)"
which means it should return integers :S
ASKER CERTIFIED SOLUTION
Avatar of Irv123
Irv123

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial