Solved

Stored Procedure in JSP?

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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
Looking for some programs to resell or buy source codes 1 77
Can't access image with tomcat/html 6 102
Using Tomcat as raspberry pi java app server 13 189
servlet example 17 51
Many businesses neglect disaster recovery and treat it as an after-thought. I can tell you first hand that data will be lost, hard drives die, servers will be hacked, and careless (or malicious) employees can ruin your data.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
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.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

792 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