Hi everyone,
I'm using JSP's to call a Stored Procedures in Oracle.
What I want is to lock the record when someone choose to update the
information.
The JSP_TESTS_SELECT_FOR_UPDATE(?,?) will return the name of the employee
(to update).
I think that everything is ok.
I set the autocommit to False.
But it's still possible to 2 diferent persons choose the same record ...
Can anyone help me,
thanks in advance,
José Castro
___ pdate_v1_2_sp.jsp ___
<!doctype html public "-//w3c/dtd HTML 4.0//en">
<html>
<body bgcolor=#FFFFFF>
<font face="Helvetica">
<form method="post" name="pdateForm" action="pdate_v1_2_sp.jsp">
<%@ page import="
weblogic.db.jdbc.*,
java.sql.*
" %>
<%!
Connection conn = null;
public Connection getCon()
{
try
{
Class.forName("weblogic.jdbc.pool.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:weblogic:pool:oraclePool");
conn.setAutoCommit(false);
}
catch (Exception e) {}
return conn;
}
%>
<%
try
{
conn = getCon();
if (conn != null)
{
Statement stmt = conn.createStatement();
stmt.execute("select * from JSP_TESTS");
ResultSet ds = stmt.getResultSet();
String myURL = request.getRequestURI();
String person = request.getParameter("person");
if (person == null)
{
%>
<head>
<title>Interactive Query</title>
</head>
<h2>
<font color=#DB1260>
Interactive Query
</font>
</h2>
<table border=1 cellpadding=5>
<th>Employee no</th>
<th>Name</th>
<%
while (ds.next())
{
String ename = "<a href=" + myURL + "?person=" +
ds.getString("employee_id") + ">" +
ds.getString("employee_id") + "</a>";
%>
<tr>
<td><%= ename %></td>
<td><%= ds.getString("name") != null ? ds.getString("name") :
" " %></td>
</tr>
<%
}// End while (ds.next())
%>
</table>
<%
ds.close();
}// End (person == null) Then
else
{
// Prepare the statement to call the Stored Procedure
CallableStatement cstmt = conn.prepareCall("{call
JSP_TESTS_SELECT_FOR_UPDATE(?,?)}");
cstmt.setString(1, person);
cstmt.registerOutParameter(2, Types.VARCHAR);
// Calling the procedure in the database
cstmt.execute();
%>
<head>
<title>Update Fields</title>
</head>
<h2>
<font color=#DB1260>
Update Fields
</font>
</h2>
<p><b>Employee Information</b></p>
<table border=0 cellpadding=5>
<tr>
<th>No</th>
<th>Name</th>
</tr>
<tr>
<td><%= person %> </td>
<td><INPUT maxLength=15 name=name value=<%= cstmt.getString(2) %>
style="HEIGHT: 22px; WIDTH: 60px"></td>
</tr>
</table>
<input type="Hidden" name="employee_id" value=<%= person %> >
<p>
<font face="Helvetica"><input type="submit" value="Update Information"
name="Submit"></font>
<%
}// End (person == null) Else
conn.close();
}// End (conn != null) Then
else
{
out.print("Sorry. Database is not available.");
}
}// End Try
catch (Exception e)
{
out.print("Exception: " + e);
}
%>
<p>
<font size=-1>Copyright (c) 1999 by BEA Systems, Inc. All Rights Reserved.
</font>
</font>
</body>
</html>
___ JSP_TESTS_SELECT_FOR_UPDATE ___
Procedure JSP_TESTS_SELECT_FOR_UPDATE (
nEmployee_id IN
JSP_TESTS.EMPLOYEE_ID%TYPE,
vName OUT
JSP_TESTS.NAME%TYPE
)
IS
BEGIN
SELECT name
INTO vName
FROM JSP_TESTS
WHERE employee_id = nEmployee_id
FOR UPDATE;
END;
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
Some relevant FAQs on JSP/Servlets can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets