Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

call oracle 8i stored procedure in servlets

Posted on 2004-04-06
13
Medium Priority
?
806 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
13 Comments
 
LVL 30

Expert Comment

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

Expert Comment

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

Assisted Solution

by:CEHJ
CEHJ earned 100 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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:
Mayank S earned 100 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
 
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:Mayank S
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This video teaches viewers about errors in exception handling.
Suggested Courses

610 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