Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Stored Procedure in JSP?

Posted on 2004-10-05
5
Medium Priority
?
671 Views
Last Modified: 2008-02-01
Hi,

I want to ask if it is possible to write a stored procedure in JSP?
Or, maybe I could use a prepared statement?
If so, can someone offer some examples that accept perameters to read, write, or updata a DB2 database table?

Thanks
0
Comment
Question by:g118481
5 Comments
 
LVL 35

Expert Comment

by:TimYates
ID: 12236383
use connection.prepareCall..

ie:

CallableStatement proc =  connection.prepareCall("{ ? = yourStoredProcThatReturnsAnInt( ? ) }");
proc.registerOutParameter(1, Types.INTEGER);
proc.setString( 2, value ) ;
cs.execute();
int age = proc.getInt( 1 ) ;
0
 
LVL 1

Author Comment

by:g118481
ID: 12236576
Tim,

Your suggestion describes how to call a stored procedure.  I already have that part.  (see my code snippet below)
I want to create the stored procedure for DB2.  Or create a prepared statement.  Isn't a prepared statement a little different from a stored procedure?  

The problem I am having, is that I have been using IBM's Stored Procedure Builder for DB2, however, it is not the easiest to use, as it tends to be very unreliable.  I was hoping to be able to write my own JSP proc or prepared statement that would do the same.  Is it possible to write a prepared statement and keep it on the web server, and simply call it within my jsp query?

<%
 
{
 Class.forName("ibm.sql.DB2Driver");
 java.sql.Connection connection =   java.sql.DriverManager.getConnection("JDBC:db2os390:DB2T");
 
 CallableStatement cs = connection.prepareCall("{CALL REQFILE (?,?,?,?,?,?,?,?,?,?)}");
if (request.getParameter("TECH")==null)
      out.write("Tech is null");
 
 cs.setString(1,request.getParameter("TYPE"));
 cs.setString(2,request.getParameter("CURDATE"));
 cs.setString(3,request.getParameter("FNAME"));
 cs.setString(4,request.getParameter("PHONE"));
 cs.setString(5,request.getParameter("TECH"));
 cs.setString(6,request.getParameter("CCID"));
 cs.setString(7,request.getParameter("TECH"));
 cs.setString(8,("CLOSED"));
 cs.setString(9,request.getParameter("CLOSETIME"));
 cs.setString(10,request.getParameter("NODENAME"));
 cs.executeUpdate();
 connection.close();
}
 
%>
0
 
LVL 10

Expert Comment

by:kupra1
ID: 12236778
Hi g118481,
  It is absolutely awesome to have ur stored procedure in the jsp page at the first place because jsp pages are supposed to be the part of the presentation tier and hence are the views for the system. Once you have the stored procedure there, you are making a tight coupling between the business and the presentation components. From the design presepective, it's absolutely incorrect. So, I will suggest you to separate your business logic from your presentation logic. Even though if you have to persist with it, you should make a simple class from where can connect to the database and have your stored procedure called.
Here is the example for that:
if the stored procedure has some OUT parameters, then it will be like this

try{
      Class.forName("oracle.jdbc.driver.OracleDriver");
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@192.111.212.1:1521:schemaName",userId,password);
      con.setAutoCommit(false);
                CallableStatement cs = con.prepareCall("{? = call pkg_name.f_cal_capacity(?,?,?)}");
      cs.setString(1,param1);
      cs.setString(2,param2);
      cs.setString(3,param3);
      cs.registerOutParameter(1,Types.FLOAT);
      cs.executeQuery();
      float flResult = cs.getInt(1);

}catch(SQLException ex){
      System.out.println("SQLException:::"+ex);
}catch(Exception ex){
      System.out.println("Exception:::"+ex);      
}                  
      
else if there is no OUT parameter in the stored procedure, it will be like this

try{
      Class.forName("oracle.jdbc.driver.OracleDriver");
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@192.111.212.1:1521:schemaName",userId,password);
      con.setAutoCommit(false);
                CallableStatement cs = con.prepareCall("{ call pkg_name.f_cal_capacity(?,?,?)}");
      cs.setString(1,param1);
      cs.setString(2,param2);
      cs.setString(3,param3);
      cs.executeQuery();

}catch(SQLException ex){
      System.out.println("SQLException:::"+ex);
                //
}catch(Exception ex){
      System.out.println("Exception:::"+ex);      
}

I have not given the full code for exception handling and transaction management. You need to take care of that.

Thanks

0
 
LVL 1

Author Comment

by:g118481
ID: 12236982
Kupra,

How is your code suggestion different from what I listed in my statement above?  Your suggestion looks like another call to a stored procedure.  I already have that part.  I need to know how to write the stored procedure/prepared statement.

Cheers
0
 
LVL 9

Accepted Solution

by:
ronan_40060 earned 800 total points
ID: 12267777
I have a sample example of PL/SQl stored procedure
I have a small sample code will will help u to understand how PL/SQL is used

CREATE OR REPLACE PACKAGE my_p IS
  TYPE ref_cur IS REF CURSOR;

  PROCEDURE my_proc (c1 OUT ref_cur, c2 OUT ref_cur);
END;
/
CREATE OR REPLACE PACKAGE BODY my_pack IS
  PROCEDURE my_proc (c1 OUT ref_cur, c2 OUT ref_cur) IS
  BEGIN
    OPEN c1 FOR SELECT 1, 'A' FROM DUAL;
    OPEN c2 FOR SELECT 2, 'B' FROM DUAL;
  END;
END;
/


JSP Code:

Connection con;
// Get the connection here......

CallableStatement cs = con.prepareCall ("{call my_p.my_proc (?,?}");
cs.registerOutParameter (1, OracleTypes.CURSOR);
cs.registerOutParameter (2, OracleTypes.CURSOR);
cs.execute ();
ResultSet rs = (ResultSet) rs.getObject (1);
System.out.println ("First Cursor");
while (rs.next ())
{
    System.out.println ("1 = " + rs.getInt (1));
    System.out.println ("2 = " + rs.getString (2));
}

rs = (ResultSet) rs.getObject (2);
System.out.println ("Second Cursor.");
while (rs.next ())
{
    System.out.println ("1 = " + rs.getInt (1));
    System.out.println ("2 = " + rs.getString (2));
}

rs.close ();
cs.close ();
con.close ();

let us know more if u need
good luck
ronan
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Your business may be under attack from a silent enemy that is hard to detect. It works stealthily in the shadows to access and exploit your critical business information, sensitive confidential data and intellectual property, for commercial gain. T…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

824 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