CODEWITHSUNDEEP
javajspjdbc
Surya Varanasi
Surya Varanasi

Reputation: 11

error while calling stored procedure through jsp

below is my jsp program which is not executing properly.It is directly returning exception error message.jdbc connections are there in include file.here batchcode and ccode are composite key.program should first check whether these are already inserted into database or not and then it should insert.

procedure:
create or replace procedure udp_addbatch(
p_batchcode  varchar2,
p_ccode  varchar2
)
as 
BEGIN
INSERT INTO BATCHES(BATCHCODE,CCODE) VALUES(p_batchcode,p_ccode);
commit;
END ;
/

**jsp file**

        <html>
<%@page import="java.sql.*, java.lang.String" %>
<head>
<style type='text/css'>
input{
     FONT-FAMILY: Verdana; FONT-SIZE:10pt; color:blue;
}
body{ 
    FONT-FAMILY:Verdana;
    FONT-SIZE:10pt;
    FONT-WEIGHT:Bold;
    }
table{
    FONT-FAMILY:Verdana;
    FONT-SIZE:10pt;
    FONT-WEIGHT:Bold;
     }
H1,H2,H3{
    FONT-FAMILY:Verdana;
    FONT-SIZE:13pt;
    FONT-WEIGHT:Bold;
    COLOR:BLUE;
    }
SELECT {
        font-family : verdana; font-size : 9pt; background-color : #FCFCFC; border: 1px solid #000000; color:blue;
        } 
TEXTAREA { 
    font-family : verdana; font-size : 9pt; background-color : #FCFCFC; border: 1px solid #000000; color:blue;
        }
</style>
</head>
<body bgcolor="white">
<%@include file="jdbcresults.jsp"%>
<%


    ResultSet rs=null;
    int nr;
        CallableStatement cstmt=null;
    try
    {   


        String BatchCode=request.getParameter("BatchCode");
        String CCode=request.getParameter("CCode");

        cstmt=con.prepareCall("{call udp_addbatch(?,?)}");
                cstmt.setString(1,BatchCode);
                cstmt.setString(2,CCode);



                rs=cstmt.executeQuery("select * from Batches where CCode='"+CCode+"' and BatchCode='"+BatchCode+"'");
        if (!rs.next())
        {
            cstmt.executeUpdate();
            out.println("<h2 align='center'> Batch and course Successfully added</h2>");
        }
        else
            {
                            out.println("<h2 align='center'> Batch and Course already Exists</h2>");
               out.println("<center><a href='Addbatch.jsp'>Go Back</a></center>");
                }

         rs.close();
        cstmt.close();
         con.close();
    }



catch(Exception e){ e.printStackTrace();} 
        out.print("<h2 align='center'>course doesn't exist");
                out.println("<center><a href='Addbatch.jsp'>Go Back</a></center>");
                }
finally{
        // The finally clause is always executed - even in error
        // conditions PreparedStatements and Connections will always be closed
        try
        {
                  if (cstmt = null)
                              cstmt.close();
        }
        catch(Exception e) {}

        try
        {
                  if (con = null)
                              con.close();
        }
        catch (Exception e){}
        }
}

    %>
</body>
</html>

Upvotes: 0

Views: 1358

Answers (1)

Jacob
Jacob

Reputation: 14741

First modify your procedure as follows

CREATE OR REPLACE
PROCEDURE udp_addbatch(
    p_batchcode IN VARCHAR2,
    p_ccode     IN VARCHAR2,
    o_var OUT NUMBER )
AS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO v_count
  FROM batches
  WHERE BATCHCODE=p_batchcode
  AND CCODE      =p_ccode;
  IF(v_count < 1 ) THEN
    INSERT INTO BATCHES
      (BATCHCODE,CCODE
      ) VALUES
      (p_batchcode,p_ccode
      );
    o_var := 1;
  ELSE
    o_var := 0;
  END IF;
END ;
-- you can add exception handling
/

and in your JSP do as follows, this is not completely tested and it is always better to move Java code out of JSP and have separate class for database connection.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@ page contentType="text/html;charset=windows-1252"%>
<html></html>
<html>
        <%@ page import="java.sql.*, java.lang.String"%>
        <head>
                <style type='text/css'>
input{
     FONT-FAMILY: Verdana; FONT-SIZE:10pt; color:blue;
}
body{ 
    FONT-FAMILY:Verdana;
    FONT-SIZE:10pt;
    FONT-WEIGHT:Bold;
    }
table{
    FONT-FAMILY:Verdana;
    FONT-SIZE:10pt;
    FONT-WEIGHT:Bold;
     }
H1,H2,H3{
    FONT-FAMILY:Verdana;
    FONT-SIZE:13pt;
    FONT-WEIGHT:Bold;
    COLOR:BLUE;
    }
SELECT {
        font-family : verdana; font-size : 9pt; background-color : #FCFCFC; border: 1px solid #000000; color:blue;
        } 
TEXTAREA { 
    font-family : verdana; font-size : 9pt; background-color : #FCFCFC; border: 1px solid #000000; color:blue;
        }
</style>
        </head>
        <body bgcolor="white"><%
     // Hope you are getting the connection part  
        Connection con = null; 
        CallableStatement cstmt=null;
        String BatchCode=request.getParameter("BatchCode");
        String CCode=request.getParameter("CCode");

 try {
cstmt=con.prepareCall("{call udp_addbatch(?,?,?)}");
                cstmt.setString(1,BatchCode);
                cstmt.setString(2,CCode);                
                cstmt.registerOutParameter(3, Types.INTEGER);     
                cstmt.executeUpdate(); 
                int val = cstmt.getInt(3);

       if (val == 1) {
       out.println("<h2 align='center'> Batch and course Successfully added</h2>");
       }
       else {
       out.println("<h2 align='center'> Batch and Course already Exists</h2>");
       // and do your stuff
       }
       }
       catch(Exception e){
       e.printStackTrace();
       }
       finally {
       cstmt.close();
       con.close();
       }

%></body>
</html>

Upvotes: 1

Related Questions