File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Servlets and the fly likes DATABASE LOCKED???? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "DATABASE LOCKED????" Watch "DATABASE LOCKED????" New topic
Author

DATABASE LOCKED????

Rui Ferns
Greenhorn

Joined: May 06, 2001
Posts: 24
Hi all,
I'm getting an error saying :
java.sql.SQLException: [Microsoft][ODBC Microsoft Access 97 Driver] Couldn't update; currently locked by user 'admin' on machine 'RUI'.
What my program does is,I have a JSP page that opens a connection with the database and retrieves fields from a products table in my database and stores them in forms. This allows users to edit/delete/add entries to the database. When the user clicks the submit button it should invoke the Updatetable.jsp page.
The Updatetable.jsp page creates another connection and either updates/deletes/adds entries to the same products table depending on what the users choice was.
When I click the submit button, i get the error at the top of this e-mail.
Does anyone know why this is happening?
Thanks Rui
Karthik Guru
Ranch Hand

Joined: Mar 06, 2001
Posts: 1209
make sure that u have'n kept the Access Db open.
try closing the connections once u are done with retrieving and
open a new one for editing and close the same again.
Rui Ferns
Greenhorn

Joined: May 06, 2001
Posts: 24
Hi,
I open a connection,create a statement and resultset.Retrieve the info and use a while loop to retrive each row and place it in form fields so the user can edit the info. then i close the statement,connection and result set. The submit button takes them to updateprod.jsp where a new connection is opened and the info in the form fields is updated to the database and then it returns. I've pasted the code below, this code is used to retrieve the info and put it in form fields. Ive also pasted the file which should update the info.Can you have a look to see if im doing anything wrong? thanks rui
=================================================================
Update.jsp
=================================================================
<%@ page import ="java.sql.*" %>
<html>
<head>
<title>Displaying Records</title>
</head>
<body>
<%
Connection connection = null;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
connection = java.sql.DriverManager.getConnection("jdbc dbc:rui1","admin1","rui1");
Statement statement = connection.createStatement();
ResultSet columns = statement.executeQuery("SELECT * FROM Products");
%>
<table border="0" cellspacing="0" width="100%">
<TR>
<td><font color="blue">MODEL</font></td>
<td><font color="blue">MAKE</font></td>
<td><font color="blue">DESCRIPTION</font></td>
<td></td>
<td></td>
<%
while(columns.next())
{
String model = columns.getString("Model");
%>
<TR>
<form name="update" method="post" action="UpdateProd.jsp">
<TD><input type="text" name="model" size="15" value="<%="" +model%>"></TD>
<TD><input type="text" name="make" size="15" value="<%="" +columns.getString("Product")%>"></TD>
<TD><input type="text" name="description" size="15" value="<%="" +columns.getString("Desc")%>"></TD>
<TD><input name="update" type="submit" alt="update" value="Update"></TD>
<TD><input name="delete" type="submit" alt="delete" value="Delete"></TD>
<input type="hidden" name="index" value="<%="" +model%>">
</form>
</TR>
<%
}
statement.close();
columns.close();
connection.close();
%>
<TR>
<form name="update1" method="post" action="UpdateProd.jsp">
<TD><input type="text" name="model" size="15"></TD>
<TD><input type="text" name="make" size="15"></TD>
<TD><input type="text" name="description" size="15"></TD>
<TD><input name="add" type="submit" value="Add"></TD>
<TD></TD>
</form>
</TR>
</TABLE>
</table>
</table>
<br>
</body>
</html>
=================================================================
UpdateProd.jsp
=================================================================
<%@ page import ="java.io.*"%>
<%@ page import ="java.sql.*" %>
<%@ page import ="java.util.*" %>
<%
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection connection = java.sql.DriverManager.getConnection("jdbc dbc:rui","admin","rui");
%>
<%
if((request.getParameter("update")) != null)
{
String index = request.getParameter("index");
connection.setAutoCommit(false);
PreparedStatement updateProduct = connection.prepareStatement("UPDATE Products SET Product = ? WHERE Model = ?");
updateProduct.setString(1, request.getParameter("make"));
updateProduct.setString(2, index);
updateProduct.executeUpdate();
PreparedStatement updateModel = connection.prepareStatement("UPDATE Products SET Model = ? WHERE Model = ?");
updateModel.setString(1, request.getParameter("model"));
updateModel.setString(2, index);
updateModel.executeUpdate();
PreparedStatement updateDesc = connection.prepareStatement("UPDATE Products SET Desc = ? WHERE Model = ?");
updateDesc.setString(1, request.getParameter("desccription"));
updateDesc.setString(2, index);
updateDesc.executeUpdate();
connection.commit();
connection.setAutoCommit(true);
}
%>

<%
connection.close();
%>
<jsp:forword page="Update.jsp"/>
=================================================================
c cannata
Greenhorn

Joined: May 08, 2001
Posts: 3
I had the same thing happen to me. You need help e-mail me back and I will send you my entire code for my servlets and comment it out for you.
cc
Rui Ferns
Greenhorn

Joined: May 06, 2001
Posts: 24
Hi,
that would be great. I really appreciate the help.
Thanks again.
Let me know if u get this message.
My e-mail is ruaidhri_fernandes@yahoo.com
Originally posted by c cannata:
I had the same thing happen to me. You need help e-mail me back and I will send you my entire code for my servlets and comment it out for you.
cc

Maky Chopra
Ranch Hand

Joined: Apr 11, 2001
Posts: 149
most probably, you have opened the database using the MS Office application (Access).. If so, close it and see if your problem goes away.
If not, close all your applications (server / browser etc..) and see if there is a file called <yourdatase>.ldb If so, delete this file and try your application.
Rui Ferns
Greenhorn

Joined: May 06, 2001
Posts: 24
Hi,
thanks for the reply.
Ive deleted that file rui.ldb.
The same error occurs and another .ldb file is created.
When I hit the update button, the first error message i got was
"Internal Servlet Error:
javax.servlet.ServletException:
Root cause:
java.lang.NullPointerException:
"
Then when i hit the back button and hit update i get the error message
"javax.servlet.ServletException: [Microsoft][ODBC Microsoft Access 97 Driver] Couldn't update; currently locked by user 'admin' on machine 'RUI'."
Root cause:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access 97 Driver] Couldn't update; currently locked by user 'admin' on machine 'RUI'.
Still,stuck on this.
Maky Chopra
Ranch Hand

Joined: Apr 11, 2001
Posts: 149
the .ldb file will be created whenever you access the database.. Why did you get the internal error the first time ? Does this happen every first time ??
Rui Ferns
Greenhorn

Joined: May 06, 2001
Posts: 24
Hi,
I don't know why i'm getting the first error.
Yes it happens every first time.
If I shutdown the server and restart it, when I load the Update.jsp page for the first time and hit the update button i get the first error message. Then if I load it again I get the 2nd error message saying the database table is locked.
Is there anyway of getting more meaningful error messages?
Do you know what the problem is?
thanks R

Originally posted by Mak Bhandari:
the .ldb file will be created whenever you access the database.. Why did you get the internal error the first time ? Does this happen every first time ??

Rui Ferns
Greenhorn

Joined: May 06, 2001
Posts: 24
Hi,
thanks for the reply.
Still having the same problem.
my e-mail is ruaidhri_fernandes@yahoo.com
Originally posted by c cannata:
I had the same thing happen to me. You need help e-mail me back and I will send you my entire code for my servlets and comment it out for you.
cc

Maky Chopra
Ranch Hand

Joined: Apr 11, 2001
Posts: 149
try
connection = DriverManager.getConnection("jdbc dbc:rui1","","");

[This message has been edited by Mak Bhandari (edited May 08, 2001).]
Rui Ferns
Greenhorn

Joined: May 06, 2001
Posts: 24
Hi,
Since your last mail, i've been trying loads of things and it works now. Your right in what you told me to change.
I wasn't using DriverManager and now I am in the two files.
Thats a relief off my shoulders.
Thanks for being so patient.
I appreciate the help.
If u ever need any help, let me know.
Thanks
rui
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: DATABASE LOCKED????