[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 807
  • Last Modified:

call oracle 8i stored procedure in servlets

I have something strange.
Here are source codes.
only stored procedure works ok.

1/3. Oracle DDL Source code ======================
CREATE TABLE TBL_AGREE_AMT
(
  EIS_YEAR    VARCHAR2(4)                       NOT NULL,
  GROUP_CODE  VARCHAR2(5)                       NOT NULL,
  AGREE_AMT   NUMBER(15)                        DEFAULT 0
)

ALTER TABLE TBL_AGREE_AMT ADD (
  PRIMARY KEY (EIS_YEAR, GROUP_CODE)
)

2/3. Oracle Procedure DDL Source code ============
CREATE OR REPLACE PROCEDURE SP_AGREE_AMOUNT_CRU
(
  P_EIS_YEAR           IN VARCHAR2,
  P_GROUP_CODE            IN VARCHAR2,
  P_AGREE_AMT            IN NUMBER
)
AS
  DATA_COUNT NUMBER;

BEGIN

  BEGIN

      SELECT COUNT(*)
      INTO DATA_COUNT
      FROM TBL_AGREE_AMT
      WHERE TBL_AGREE_AMT.EIS_YEAR = P_EIS_YEAR
        AND TBL_AGREE_AMT.GROUP_CODE = P_GROUP_CODE;

   EXCEPTION
        WHEN NO_DATA_FOUND THEN
             DATA_COUNT := 0;

  END;

  IF DATA_COUNT = 0 THEN
     BEGIN
       INSERT INTO TBL_AGREE_AMT
        (
         EIS_YEAR,
         GROUP_CODE,
         AGREE_AMT
         )
       VALUES
        (
         P_EIS_YEAR,
         P_GROUP_CODE,
         P_AGREE_AMT
       );

     END;
  END IF;


  IF DATA_COUNT > 0 THEN
     BEGIN
       UPDATE TBL_AGREE_AMT
       SET AGREE_AMT      = P_AGREE_AMT
       WHERE EIS_YEAR       = P_EIS_YEAR
         AND GROUP_CODE = P_GROUP_CODE;

     END;
  END IF;

END SP_AGREE_AMOUNT_CRU;

3/3. Servlet Source code ================
package ecosmos.servlet;

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.IOException;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.sql.CallableStatement;

import ecosmos.util.DBHandler;

public class AgreeAmountHandler extends HttpServlet {
      


      private Connection con;
      
      private String target;
      
      public void init (ServletConfig config) throws ServletException{
            super.init(config);
      }
      
      public void service(HttpServletRequest req, HttpServletResponse res) throws ServletException , IOException {
      
            String year      = req.getParameter("slt_year");
            
            CallableStatement cs = null;

            try {

                  String sql = " { call SP_AGREE_AMOUNT_CRU(?,?,?) } ";

                  con = DBHandler.GetConnection();
                  
                  cs = con.prepareCall(sql);

                  java.util.Enumeration names ;
              names       = req.getParameterNames();
                               
                  String codeName = "";
              int value = 0;
/*
                  while( names.hasMoreElements() ) {
                        
                        codeName = (String) names.nextElement();

                        value = Integer.parseInt(req.getParameter(codeName));

                        cs.setString(1,year);
                        cs.setString(2,codeName);
                        cs.setInt(3,value);
                        cs.execute();
                  }
*/
                  cs.setString(1,"2004");
                  cs.setString(2,"A010");
                  cs.setInt(3,3333);
                  
                  cs.execute();
                  
                  target = "/agent/agreeAmountHandler.jsp?db_result=ok";

            }catch(Exception e) {
                  target = "/agent/agreeAmountHandler.jsp?db_result=failure";
                  e.printStackTrace();
            } finally{
                  try{ if(cs != null) cs.close(); } catch(Exception ex) {}
                  try{ if(con != null) con.close(); } catch(Exception ex) {}

            }

            RequestDispatcher rd;
            rd = req.getRequestDispatcher(target);
            rd.forward(req,res);

      }// End of service-method

}
0
iamjhkang
Asked:
iamjhkang
  • 6
  • 4
  • 3
2 Solutions
 
Mayank SAssociate Director - Product EngineeringCommented:
What is the problem?
0
 
Mayank SAssociate Director - Product EngineeringCommented:
BTW, why don't you have doPost () and doGet () methods?
0
 
CEHJCommented:
iamjhkang, you're being rather cryptic here ;-)

I assume you're finding that the SP only works when you hard code the parameters? You can iterate the parameters as you were trying with the Enumeration, as parameters to an http request are unordered - you won't be able to predict the order. Just get the parameters directly and you should be OK:

String year = req.getParameter("slt_year");
// test it and then...
cs.setString(1,year);

String x = req.getParameter("x");
// test it and then...
cs.setString(2, x);

String sY= req.getParameter("y");
// test it and then...
int y = Integer.parseInt(sY);


cs.setInt(3, y);

Obviously, use your own parameter names
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
CEHJCommented:
Oops!

>>You can iterate the parameters

should have said

You can't iterate the parameters
0
 
Mayank SAssociate Director - Product EngineeringCommented:
BTW, as for getParameterNames (), you will only be able to get the names of the parameters using that, not the values. You can get the values through getParameterValues () - when you have many parameters with the same name (like a control-array), or getParameter () - for single parameter.
0
 
CEHJCommented:
>>you will only be able to get the names of the parameters using that, not the values.

Well iamjhkang was actually getting the names to get the values, which *would* have worked but for the fact that the parameters to the SP are not named themselves, but ordered by number. It would have worked for named params to a Statement

Since there are only three params i don't think it's too much work to get them by name individually anyway ;-)
0
 
CEHJCommented:
>>It would have worked for named params to a Statement

Hang on - that's rubbish - there isn't such a thing!
0
 
Mayank SAssociate Director - Product EngineeringCommented:
>> iamjhkang was actually getting the names to get the values

Oh yeah, didn't notice req.getParameter ( codeName ) ;-)
0
 
iamjhkangAuthor Commented:
I changed to "begin SP_AGREE_AMOUNT_CRU(?,?,?); end; "
and it was null pointer exception maybe. the parameter was sltYear not slt_year.

Thanks all of you.
0
 
iamjhkangAuthor Commented:
and I didn't filter other paramternames.
0
 
CEHJCommented:
Null pointer exception on which line?
0
 
CEHJCommented:
8-)
0
 
iamjhkangAuthor Commented:
slt_year is not there so year is null, not null pointer exception.

As I mentioned
I use call ~ instead of begin ~ , I got ORA-00900 error.

BTW, call ~ statements is supposed to work ok? because it is by ansi standard.

Thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now