Solved

Stored Procedure in JSP?

Posted on 2004-10-05
5
620 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

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

Learn to move / copy / export exchange contacts to iPhone without using any software. Also see the issues in configuration of exchange with iPhone to migrate contacts.
In this article, I will show you HOW TO: Create your first Windows Virtual Machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, the Windows OS we will install is Windows Server 2016.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

17 Experts available now in Live!

Get 1:1 Help Now