Error converting from MySQL to SQLite

Question

It hasn't been too much trouble but there's an error I keep getting from an update from user clicking a button.

Originally in MySQL this was an enum('Y','N') value, but as SQLite doesnt use enums or booleans, I've decided to use an int with 1 and 0 respectively.

How can this be solved?

if(e.getSource()==boughtB){
    String cigName;
    cigName = Habanos2.selection;
    String humiString;
    int humino;

    Connection con = null;
    System.out.println(cigName);

    try{
        ResultSet humiRS1 = HabMenu.getGenericRS("select * from cigar where name = '"+cigName+"'"); 
        if(humiRS1.next()){
            humiString = humiRS1.getString("humi_no");

            humino = Integer.parseInt(humiString);
            System.out.println("humino"+humino);

            Class.forName("org.sqlite.JDBC").newInstance();

            con = DriverManager.getConnection("jdbc:sqlite:cigardb.db");
            Statement s = con.createStatement();
            humino++;
            String humiNoS = Integer.toString(humino);
            s.executeUpdate("update cigar set humi = 1 where name ='"+cigName+"'" );
            s.executeUpdate("update cigar set humi_no = "+humiNoS+"where name ='"+cigName+"'");

            Habanos2Frame.myHumi.setText("");
            ResultSet humiii = HabMenu.getGenericRS("select * from cigar where humi = 1");

It produces this error:

java.sql.SQLException: database locked at org.sqlite.DB.execute(DB.java:270) at org.sqlite.DB.executeUpdate(DB.java:281) mysql humi error humiNoS = at org.sqlite.Stmt.executeUpdate(Stmt.java:103) at cigarDiary.buttonBar$ButtonHandler.actionPerformed(buttonBar.java:207) at javax.swing.AbstractButton.fireActionPerformed(Unknown Source) at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source) at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)

Have you tried opening the DB from the command line? If the DB is not closed properly, it remains locked, and that could be the reason you're getting the error. Check this other question: http://stackoverflow.com/questions/151026/how-do-i-unlock-a-sqlite-database . And you might want to read the SQLite documentation about locking: http://www.sqlite.org/lockingv3.html — Augusto, Oct 03 '11 at 09:55

score 1 · Accepted Answer · answered Oct 05 '11 at 10:17

Ok the way I solved this was by closing each ResultSet (rs.close) and Connection (con.close) after I was finished using it. This is something that MySql doesnt seem to mind but SQLite does. It wont allow you to perform an update while a resultset and its connection are stilll open.

Error converting from MySQL to SQLite

1 Answers1