Solved

call oracle 8i stored procedure in servlets

Posted on 2004-04-06
13
793 Views
Last Modified: 2013-11-24
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
Comment
Question by:iamjhkang
  • 6
  • 4
  • 3
13 Comments
 
LVL 30

Expert Comment

by:mayankeagle
ID: 10772043
What is the problem?
0
 
LVL 30

Expert Comment

by:mayankeagle
ID: 10772048
BTW, why don't you have doPost () and doGet () methods?
0
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 25 total points
ID: 10772994
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 10772996
Oops!

>>You can iterate the parameters

should have said

You can't iterate the parameters
0
 
LVL 30

Accepted Solution

by:
mayankeagle earned 25 total points
ID: 10773028
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 10773047
>>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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 86

Expert Comment

by:CEHJ
ID: 10773056
>>It would have worked for named params to a Statement

Hang on - that's rubbish - there isn't such a thing!
0
 
LVL 30

Expert Comment

by:mayankeagle
ID: 10773078
>> iamjhkang was actually getting the names to get the values

Oh yeah, didn't notice req.getParameter ( codeName ) ;-)
0
 
LVL 1

Author Comment

by:iamjhkang
ID: 10780062
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
 
LVL 1

Author Comment

by:iamjhkang
ID: 10780070
and I didn't filter other paramternames.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10780074
Null pointer exception on which line?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10780106
8-)
0
 
LVL 1

Author Comment

by:iamjhkang
ID: 10780152
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
This video teaches viewers about errors in exception handling.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now