File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JSP and the fly likes calling a stored procedure in Jsp Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Java » JSP
Reply locked New topic
Author

calling a stored procedure in Jsp

Gregk
Greenhorn

Joined: Jan 10, 2002
Posts: 3
I am trying to call a stored procedure in a jsp,
the procedure deletes from a database, taking as a parameter user name.Here is my code, for some reasone it is not working. The error I'm getting is Type OracleCallableStatement was not found.
Here is my code:
<% // the delete link has been clicked
String myName = "";
if(request.getParameter("deleteAll") != null) {
myName = request.getParameter("deleteAll");
Connection conn = coreModel.getConn();
OracleCallableStatement oraStmt1 =
(OracleCallableStatement)conn.prepareCall("{call DS_MOTD_PKG.DELETEALLMESSAGES(myName)}");
oraStmt1.execute();
}
%>
Kyle Brown
author
Ranch Hand

Joined: Aug 10, 2001
Posts: 3892
    
    5
It sounds like you don't have the right import statements in your JSP if the problem is that OracleCallableStatement is not found. Find out what package that's in and add an import tag to your JSP (read the JSP spec and search for "import").
My second comment is DON'T DO THAT! Please move this code to a Java class (a bean) and call the appropriate method (like myDBBean.delete(String name)) instead of trying to do this sort of thing all inline with the JSP! The coders that follow after you will thank you for it...
Kyle


Kyle Brown, Author of Persistence in the Enterprise and Enterprise Java Programming with IBM Websphere, 2nd Edition
See my homepage at http://www.kyle-brown.com/ for other WebSphere information.
Ray Smilgius
Ranch Hand

Joined: Jan 29, 2001
Posts: 120
Here is some example code for sql called stored procedure that works:
<html>
<head>
<title>Database</title>
</head>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.Date" %>
<%@ page import="java.io.*" %>
<%response.setHeader("Cache-Control", "no-cache");%>
<%response.setHeader("pragma", "no-cache");%>
<%response.setDateHeader("Expires", -1);%>
<body>
<%!
public void ReplacePageInParentFrame(String sUrl)
{
}
%>
<% System.out.println("test"); %>
The current date is <%= new Date() %>.
<h2>Employee Phone Numbers</h2>
<table border="1">
<tr><th>ID Number<th>Name<th>Title</tr>
<%
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc dbc:mydatabase;UID=raymond;PWD=123456");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM Employees");
while(rs.next())
{
String employeeId = rs.getString("EmployeeID");
String employeeName = rs.getString("FirstName");
String employeeExt = rs.getString("Title");

out.print("<tr>");
out.print("<td>" + employeeId + "</td>");
out.print("<td>" + employeeName + "</td>");
out.print("<td>" + employeeExt + "</td>");
out.print("</tr>");
}
con.close();
%>
</table>
</body>
</html>
Hope it helps.
Ray


SCJO, SCJD, SCWCD, I-Net+, A+, Network+, MCSD, MCDBA, MCP, MCT
Kyle Brown
author
Ranch Hand

Joined: Aug 10, 2001
Posts: 3892
    
    5
Originally posted by Ray Smilgius:
Here is some example code for sql called stored procedure that works:
<html>
<head>
<title>Database</title>
</head>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.Date" %>
<%@ page import="java.io.*" %>
<%response.setHeader("Cache-Control", "no-cache");%>
<%response.setHeader("pragma", "no-cache");%>
<%response.setDateHeader("Expires", -1);%>
<body>
<%!
public void ReplacePageInParentFrame(String sUrl)
{
}
%>
<% System.out.println("test"); %>
The current date is <%= new Date() %>.
<h2>Employee Phone Numbers</h2>
<table border="1">
<tr><th>ID Number<th>Name<th>Title</tr>
<%
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc dbc:mydatabase;UID=raymond;PWD=123456");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM Employees");
while(rs.next())
{
String employeeId = rs.getString("EmployeeID");
String employeeName = rs.getString("FirstName");
String employeeExt = rs.getString("Title");

out.print("<tr>");
out.print("<td>" + employeeId + "</td>");
out.print("<td>" + employeeName + "</td>");
out.print("<td>" + employeeExt + "</td>");
out.print("</tr>");
}
con.close();
%>
</table>
</body>
</html>
Hope it helps.
Ray

Ray, I think you've included the wrong code. This is using Dynamic SQL (SELECT * FROM Employees) and not a stored procedure. The earlier code posted above actually shows how to call a stored procedure...
Kyle
Kyle Brown
author
Ranch Hand

Joined: Aug 10, 2001
Posts: 3892
    
    5
Originally posted by Gregk:
I am trying to call a stored procedure in a jsp,
the procedure deletes from a database, taking as a parameter user name.Here is my code, for some reasone it is not working. The error I'm getting is Type OracleCallableStatement was not found.
Here is my code:
<% // the delete link has been clicked
String myName = "";
if(request.getParameter("deleteAll") != null) {
myName = request.getParameter("deleteAll");
Connection conn = coreModel.getConn();
OracleCallableStatement oraStmt1 =
(OracleCallableStatement)conn.prepareCall("{call DS_MOTD_PKG.DELETEALLMESSAGES(myName)}");
oraStmt1.execute();
}
%>

One more thing -- I don't think you actually need to use OracleCallableStatement. When doing JDBC programming, you cal almost always get away with using the Interfaces rather than the implementation classes. See if the following works:

myName = request.getParameter("deleteAll");
Connection conn = coreModel.getConn();
CallableStatement stmt1 =
(CallableStatement)conn.prepareCall("{call DS_MOTD_PKG.DELETEALLMESSAGES(" + myName + ")}");
stmt1.execute();
Kyle
[ January 10, 2002: Message edited by: Kyle Brown ]
[ January 10, 2002: Message edited by: Kyle Brown ]
Madhav Lakkapragada
Ranch Hand

Joined: Jun 03, 2000
Posts: 5040
"Gregk"
Your name doesn't agree with the javaranch guidelines. Please take a moment and re-register after reviewing the guideline at http://www.javaranch.com/name.jsp
thanks for your cooperation.
- satya


Take a Minute, Donate an Hour, Change a Life
http://www.ashanet.org/workanhour/2006/?r=Javaranch_ML&a=81
George Larry
Ranch Hand

Joined: Nov 07, 2001
Posts: 52
Here is an example of something that I've done. This should help-

ps:
Please use the [ code ] and [ / code ] UBB tags when posting source code.
Also, disable the Smilies (See options when posting messages) in such posts.
Thanks.
- satya
[ January 11, 2002: Message edited by: Madhav Lakkapragada ]
Gregk
Greenhorn

Joined: Jan 10, 2002
Posts: 3
Satya,
Sorry about the username. I tried to change it, but when I went to updated profile I didn't see an option to update username. How would I go about changing the name?
Thanks in advance.
Greg
Gregk
Greenhorn

Joined: Jan 10, 2002
Posts: 3
Kyle,
Thanks for your help. About doing this outside the Jsp: I agree with you, that it is never a good idea to stuff all that into a jsp. I am currently working within a Model View Controller environment, however for the type of thing I am doing, It is a small message system, there would not be any pros in doing this outside the jsp, it would just add a level of complexity. However, I did get it to work ,turns out I forgot to import
oracle.jdbc.driver.*. Thanks for your help, and I will probably do this with a bean later on, maybe on the next release.

Ray & Larry,
Thank you very much for all your help. The code really helped.
Greg
Madhav Lakkapragada
Ranch Hand

Joined: Jun 03, 2000
Posts: 5040

Sorry about the username. I tried to change it, but when I went to updated profile I didn't see an option to update username. How would I go about changing the name?

Well, that a feature of the UBB software that we use. You cannot update your username.
You will have to re-register with a new name, so basically (logout, if you are logged in) and then goto the Register page and select a valid name according to the guidelines.
Also, your old account will be locked so that no one else can use it.
Appreciate your cooperation.
Thanks.
- satya
 
Don't get me started about those stupid light bulbs.
 
subject: calling a stored procedure in Jsp
 
Similar Threads
unsupported LOB (calling Java from Oracle)
registerOutParameter for table?
Pass parameter Collection to PL
Help....Pass parameter to PL
java.lang.ClassCastException