g118481
asked on
Stored Procedure in JSP?
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
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
ASKER
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.DB2 Driver");
java.sql.Connection connection = java.sql.DriverManager.get Connection ("JDBC:db2 os390:DB2T ");
CallableStatement cs = connection.prepareCall("{C ALL REQFILE (?,?,?,?,?,?,?,?,?,?)}");
if (request.getParameter("TEC H")==null)
out.write("Tech is null");
cs.setString(1,request.get Parameter( "TYPE"));
cs.setString(2,request.get Parameter( "CURDATE") );
cs.setString(3,request.get Parameter( "FNAME"));
cs.setString(4,request.get Parameter( "PHONE"));
cs.setString(5,request.get Parameter( "TECH"));
cs.setString(6,request.get Parameter( "CCID"));
cs.setString(7,request.get Parameter( "TECH"));
cs.setString(8,("CLOSED")) ;
cs.setString(9,request.get Parameter( "CLOSETIME "));
cs.setString(10,request.ge tParameter ("NODENAME "));
cs.executeUpdate();
connection.close();
}
%>
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.DB2
java.sql.Connection connection = java.sql.DriverManager.get
CallableStatement cs = connection.prepareCall("{C
if (request.getParameter("TEC
out.write("Tech is null");
cs.setString(1,request.get
cs.setString(2,request.get
cs.setString(3,request.get
cs.setString(4,request.get
cs.setString(5,request.get
cs.setString(6,request.get
cs.setString(7,request.get
cs.setString(8,("CLOSED"))
cs.setString(9,request.get
cs.setString(10,request.ge
cs.executeUpdate();
connection.close();
}
%>
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.Or acleDriver ");
Connection con = DriverManager.getConnectio n("jdbc:or acle:thin: @192.111.2 12.1:1521: schemaName ",userId,p assword);
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.FLOA T);
cs.executeQuery();
float flResult = cs.getInt(1);
}catch(SQLException ex){
System.out.println("SQLExc eption:::" +ex);
}catch(Exception ex){
System.out.println("Except ion:::"+ex );
}
else if there is no OUT parameter in the stored procedure, it will be like this
try{
Class.forName("oracle.jdbc .driver.Or acleDriver ");
Connection con = DriverManager.getConnectio n("jdbc:or acle:thin: @192.111.2 12.1:1521: schemaName ",userId,p assword);
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("SQLExc eption:::" +ex);
//
}catch(Exception ex){
System.out.println("Except ion:::"+ex );
}
I have not given the full code for exception handling and transaction management. You need to take care of that.
Thanks
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
Connection con = DriverManager.getConnectio
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,
cs.executeQuery();
float flResult = cs.getInt(1);
}catch(SQLException ex){
System.out.println("SQLExc
}catch(Exception ex){
System.out.println("Except
}
else if there is no OUT parameter in the stored procedure, it will be like this
try{
Class.forName("oracle.jdbc
Connection con = DriverManager.getConnectio
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("SQLExc
//
}catch(Exception ex){
System.out.println("Except
}
I have not given the full code for exception handling and transaction management. You need to take care of that.
Thanks
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ie:
CallableStatement proc = connection.prepareCall("{ ? = yourStoredProcThatReturnsA
proc.registerOutParameter(
proc.setString( 2, value ) ;
cs.execute();
int age = proc.getInt( 1 ) ;