-1

My application reads a html table and then a script (TableToCSV) converts it into a .csv format. After that I convert that .csv into a sqlite database. After that I run queries on the database. Problem is that upon executing, it shows that SQLITE_BUSY; database file is locked.

Whats the reason of this and how can I fix this?

Here is my code - 

        final JFileChooser  fileDialog = new JFileChooser();
    JButton btnInputFile = new JButton("Input File");
    btnInputFile.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent arg0) {

            int returnVal = fileDialog.showOpenDialog(rootPane);
            if (returnVal == JFileChooser.APPROVE_OPTION) {
               java.io.File file = fileDialog.getSelectedFile();

               String name = file.getName();
               name = name.substring(0, name.lastIndexOf("."));
               name += ".html";
               File newFile = new File(file.getParentFile(), name);
               if (file.renameTo(newFile)) {
                   try {
                    TableToCSV tableToCSV = new TableToCSV(newFile, ',', '\"', '#', CSV.UTF8Charset );
                    System.out.println("action");
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
               }

               try
               {
                BufferedReader br=new BufferedReader(new FileReader("v.csv"));
                String line;

                while((line=br.readLine())!=null)
                {
                    System.out.println(line);
                    String[]value = line.split(",");
                    System.out.println(value.length);

                    String sql = "INSERT into main ([Ticket #], Status, Priority, Department, [Account Name]) "
                     + "values ('"+value[0]+"','"+value[1]+"','"+value[2]+"','"+value[3]+"','"+value[4]+"')";

                     PreparedStatement pst = DatabaseConnection.ConnectDB().prepareStatement(sql);
                     pst.executeUpdate();

                }
                br.close();

               }

               catch(Exception e)
               {
                JOptionPane.showMessageDialog(null, e);
               }

            }

        }
    });

UPDATE - DatabaseConnection.java

public class DatabaseConnection {
Connection conn = null;
Statement stmt = null;

public static Connection ConnectDB() {

    try {
        Class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:database.db");
        conn.setAutoCommit(true);
        return conn;
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
        return null;
    }
}
}
satnam
  • 1,457
  • 4
  • 23
  • 43
  • Close your `PreparedStatement` and try again. Also in Java convention methods start with lowercase (`ConnectDB()`). – m0skit0 Mar 11 '14 at 19:56
  • Tried closing it just like bradvido said below. The error persists. I've also closed connections everywhere else – satnam Mar 11 '14 at 20:23
  • Possible duplicate of [SQLITE\_BUSY The database file is locked (database is locked) in wicket](http://stackoverflow.com/questions/8559623/sqlite-busy-the-database-file-is-locked-database-is-locked-in-wicket) – Stephan Jan 02 '16 at 18:32

1 Answers1

0

It's probably due to you having multiple open references to the sqlite database. I'd start by closing your PreparedStatement in a finally block inside your while loop.

PreparedStatement pst = null;
try{   
  pst = DatabaseConnection.ConnectDB().prepareStatement(sql);
  pst.executeUpdate();
}finally{
  if(pst != null) {
    pst.close();
  }
}

You should also close the database connection at the end of everything. If that doesn't fix it, then please explain more about how you "convert that .csv into a sqlite database"

bradvido
  • 2,743
  • 7
  • 32
  • 49
  • Tried that, error persists. Also I've closed connections everywhere else. I'm converting v.csv to my database, with the same code that is mentioned above. – satnam Mar 11 '14 at 20:25
  • Also I tried printing a string "test" after pst.executeUpdate();. It didn't print. Which I think means that this particular connection is runnning into problems not some other – satnam Mar 11 '14 at 20:28
  • 1
    we'd have to know more about what `DatabaseConnection.ConnectDB()` does – bradvido Mar 11 '14 at 21:58
  • You also need to close the connection returned by `DatabaseConnection.ConnectDB()` -- and move it out of your loop because it creates a new connection every time it's called. – bradvido Jun 08 '15 at 15:13