Solved

Stored Procedure in JSP?

Posted on 2004-10-05
5
653 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
[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
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 200 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to group array of objects ? 5 79
Retrieving file from bytes array in spring mvc 8 64
mask rule in struts validation.xml 5 104
web application structure 18 135
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Had a business requirement to store the mobile number in an environmental variable. This is just a quick article on how this was done.

751 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