Here is some code which proves my previous statement:
package com.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.http.*;
import javax.servlet.*;
public class TestServlet extends HttpServlet {
public void doGet (HttpServletRequest request, HttpServletResponse
response)
throws ServletException
{
runTest();
}
public static void main(String[] args) {
TestServlet test = new TestServlet();
test.runTest();
}
public void runTest() {
System.out.println("Running test...");
Connection connection = null;
Connection connection2 = null;
try {
Class.forName("org.sqlite.JDBC");
connection =
DriverManager.getConnection("jdbc:sqlite:webapps/test/
test.db");
connection2 =
DriverManager.getConnection("jdbc:sqlite:webapps/test/
test.db");
ResultSet rs = connection.prepareStatement("SELECT *
FROM
TEST").executeQuery();
System.out.println("Successfully obtained ResultSet
1.");
ResultSet rs2 = connection2.prepareStatement("SELECT *
FROM
TEST2").executeQuery();
System.out.println("Successfully obtained ResultSet
2.");
} catch (Exception e) {
System.out.println("An error occurred, details: " +
e.getMessage());
} finally {
try {
if (connection2 != null) {
connection2.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException sqle) {
System.out.println("Error closing connection: "
+
sqle.getMessage());
}
}
System.out.println("Test complete.");
}
}
Output when run as a standalone app (execute main method) using the
sqlitejdbc-v042-nested.jar driver:
Running test...
Successfully obtained ResultSet 1.
Successfully obtained ResultSet 2.
Test complete.
Output when run as a servlet in Tomcat (execute doGet method) using
the sqlitejdbc-v042-native.jar driver:
Running test...
Successfully obtained ResultSet 1.
Successfully obtained ResultSet 2.
Test complete.
Output when run as a servlet in Tomcat (execute doGet method) using
the sqlitejdbc-v042-nested.jar driver:
Running test...
Successfully obtained ResultSet 1.
An error occurred, details: database is locked
Test complete.
I hope this helps
Regards
Sam
On Mar 10, 12:18 pm, Buff <[EMAIL PROTECTED]> wrote:
> Hi David,
>
> I understand that SQLite does not support concurrent writes but I am
> not trying to write to the database at all. I am seeing this problem
> with two select statements, i.e.
>
> rs = conn1.prepareStatement("select * from x;").executeQuery();
> rs.next();
> conn2.prepareStatement("select * from y;").executeQuery();
>
> but again only when running as a servlet in Tomcat. I'm sure it is
> difficult to test an application which you are not familiar with but
> this does look like a defect in the nested Java JDBC driver to me as
> the SAME code works when using the native windows driver.
>
> Sam
>
> On Mar 7, 8:44 pm, "David Crawshaw" <[EMAIL PROTECTED]> wrote:
>
> > > It would be great to see a fix for this in the next release David or
> > > an explanation as to why it doesn't work.
>
> > You're using programs I have no experience with and I do not have the
> > time to learn. But I can guess:
>
> > SQLite does not support concurrent writes.
>
> > You can do this with server-based DBMSs (such as MySQL, PostgreSQL, MS
> > SQL, Oracle, etc);
>
> > rs = conn1.prepareStatement("select * from x;").executeQuery();
> > rs.next();
> > conn2.prepareStatement("insert into y ...").executeUpdate();
>
> > You cannot do this with SQLite. The thread with conn2 will block until
> > conn1 closes its select statement. If more than 3000ms passes, conn2
> > will throw a "db is locked". Sloppy code leaves selects open for the
> > long term in some threads, because they're used to a DBMS do the dirty
> > work for them. I suspect that is what is happening here. When someone
> > is writing to the file, no-one can be reading it.
>
> > d.
--~--~---------~--~----~------------~-------~--~----~
Mailing List: http://groups.google.com/group/sqlitejdbc?hl=en
To unsubscribe, send email to [EMAIL PROTECTED]
-~----------~----~----~----~------~----~------~--~---
