Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
    Bookmark Topic Watch Topic
  • New Topic

calling a stored procedure in Jsp

 
Gregk
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Report post to moderator
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
Posts: 3892
5
  • Mark post as helpful
  • send pies
  • Report post to moderator
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
 
Ray Smilgius
Ranch Hand
Posts: 120
  • Mark post as helpful
  • send pies
  • Report post to moderator
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
 
Kyle Brown
author
Ranch Hand
Posts: 3892
5
  • Mark post as helpful
  • send pies
  • Report post to moderator
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
Posts: 3892
5
  • Mark post as helpful
  • send pies
  • Report post to moderator
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
Posts: 5040
  • Mark post as helpful
  • send pies
  • Report post to moderator
"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
 
George Larry
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Report post to moderator
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
Posts: 3
  • Mark post as helpful
  • send pies
  • Report post to moderator
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
Posts: 3
  • Mark post as helpful
  • send pies
  • Report post to moderator
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
Posts: 5040
  • Mark post as helpful
  • send pies
  • Report post to moderator

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
 
    Bookmark Topic Watch Topic
  • New Topic