Solved

Stored Procedure in JSP?

Posted on 2004-10-05
5
639 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 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

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

This tutorial shows how to create a greeting card by combining two image layers and a text layer on a PC using a free image editing app.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

810 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