statement is not executing on setString

The following code works:

import java.sql.*;

import org.sqlite.SQLiteJDBCLoader;

public class TestSqlite2 {
    static private Connection        conn;
    static private PreparedStatement prep;
    static private Statement         stat;

static private void deinit() throws Exception {
        conn.close();
    }

static private void fillDb() throws Exception {
        prep.setString(1, "Gandhi");
        prep.setString(2, "politics");
        try {
            prep.executeUpdate();
        } catch (Exception e) {
        }
        /*
        try {
            prep.executeUpdate();
        } catch (Exception e) {
        }
        */
        prep.setString(1, "Turing");
        prep.setString(2, "computers");
        prep.executeUpdate();
        prep.setString(1, "Wittgenstein");
        prep.setString(2, "smartypants");
        prep.executeUpdate();
    }

static private void init() throws Exception {
        conn = DriverManager.getConnection("jdbc:sqlite:test.sqlite");
        prep = conn.prepareStatement("" +
                                     "INSERT INTO people\n" +
                                     "(name, occupation)\n" +
                                     "VALUES (?, ?)\n" +
                                     ";");
        stat = conn.createStatement();
        stat.executeUpdate("DROP TABLE IF EXISTS people;");
        stat.executeUpdate("CREATE TABLE people (\n" +
                           "    id INTEGER PRIMARY KEY,\n" +
                           "    name TEXT UNIQUE,\n" +
                           "    occupation TEXT\n" +
                           ");"
                           );
    }

public static void main(String[] args) throws Exception {
        Class.forName("org.sqlite.JDBC");
        init();
        fillDb();
        showPeople();
        System.out.println(String.format("running in %s mode", SQLiteJDBCLoader.isNativeMode() ? "native" : "pure-java"));
    }

static private void showPeople() throws Exception {
        ResultSet rs;

rs = stat.executeQuery("SELECT\n" +
                               "    name,\n" +
                               "    occupation\n" +
                               "FROM people;"
                               );
        while (rs.next()) {
            System.out.println("name = " + rs.getString("name"));
            System.out.println("job  = " + rs.getString("occupation"));
        }
        rs.close();
    }
}

I use it with the jar I got from:
www.xerial.org/trac/Xerial/wiki/SQLiteJDBC

When I remove the comment and try to insert the first record two times, I get the following error with the next setString:

This is just to show the problem. I hit on the problem when inserting more as 2000 records, but this is a little easier to demonstrate. ;-)

What is happening here and how to solve it?

Although it shouldn't be necessary, have you tried setting the values again before executing the query? It's possible that driver doesn't retain them. Also the code "catch (Exception e) {}" is a bad practice. You should always handle your exceptions.

From your CREATE TABLE statement I observe that the name column is Unique! This will prevent subsequent inserts with same value!

Scott Selikoff wrote:Although it shouldn't be necessary, have you tried setting the values again before executing the query? It's possible that driver doesn't retain them. Also the code "catch (Exception e) {}" is a bad practice. You should always handle your exceptions.

I did not, but that is not the problem, because this is only to show the problem. To be really sure I changed the code:

Still the same problem.

Also in my real program I handle the exception. Here I am just generating the exception to show the problem.

Madhan Sundararajan Devaki wrote:From your CREATE TABLE statement I observe that the name column is Unique! This will prevent subsequent inserts with same value!

The problem is not that I can not insert the same twice (that I am doing on purpose to show the problem), the problem is that after the exception isgenerated, the statement setSting generates an exception "statement is not executing".

I believe, one possible reason could be, the preparedstatement object could be in an inconsistent state after an exception and is being prevented from being executed.

Madhan Sundararajan Devaki wrote:I believe, one possible reason could be, the preparedstatement object could be in an inconsistent state after an exception and is being prevented from being executed.

Okay, when doing the prep = conn.prepareStatement again, there is no problem. But this still leaves me with my real problem. I did get the "statement is not executing" after inserting a little more as 2.000 records. The way I 'solved' it was by not inserting more as a 1.000 records pro program run. Now I do the prep = conn.prepareStatement again every 1000 inserts. But why is this necessary?

Do you commit after execution? If not, you should and then repeat the cycle (create prepared statement, set values, etc... ) for the required number of records.