dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5

Thread: SQLITE .db file returns "in use" when connection is closed and Detach say no database

  1. #1
    Join Date
    Jan 2019
    Location
    Java 1.8
    Posts
    12

    SQLITE .db file returns "in use" when connection is closed and Detach say no database

    I wasn't sure whether to post to the Java forum or this Database forum but I think this is a DB issue... maybe you can help.

    I have an embedded SQLite database in my Java application. When I want to delete the database (which in SQLite is a file), I cannot because I receive a message the file "is in use" though I cannot find that it is. I've spent better than 1/2 a day checking all connection strings, and have even run the DETACH DATABASE 'databaseName'. Even though the connection is null and DETACH command returns no database, for some reason the file is returning in use and won't delete.

    The code for deleting the file:
    Code:
    if (Files.exists(selectedFile)) {
                            Files.delete(selectedFile);
                        }
    The code for detaching the database which should disconnect the db if it isn't (according to the SQLite information on the web). 'database' is a parameter be passed of the database filename (e.g. myDatabase.db)
    Code:
    Connection dConn = null;
            try{
                dConn = DBConnection.getConnection();
                if(conn != null) {
                    PreparedStatement ps = dConn.prepareStatement("DETACH DATABASE '" + database + "'");
                    ps.execute();
                }
                dConn.close();
            }catch(SQLException ex){
                ex.printStackTrace();
                DisplayFailureMessage("Detachment failed", "Detach failed");
            }finally{
                if(dConn != null)
                    dConn.close();
            }



    Thanks
    Last edited by SVOhio; March 21st, 2019 at 11:18 AM. Reason: clarification of issue - updated wrong response

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    12,973

    Re: SQLITE .db file returns "in use" when connection is closed and Detach say no data

    Probably the DbConnection object (assuming DbConnection is a class member, not a static object) is holding the connection open. Try checking if it is open and then call DbConnection.close().

  3. #3
    Join Date
    Jan 2019
    Location
    Java 1.8
    Posts
    12

    Re: SQLITE .db file returns "in use" when connection is closed and Detach say no data

    Quote Originally Posted by Arjay View Post
    Probably the DbConnection object (assuming DbConnection is a class member, not a static object) is holding the connection open. Try checking if it is open and then call DbConnection.close().
    Thanks for your time with this, I appreciate it!

    It is static and this is the first I've worked with DB connections, so I apologize that I may be a little naive. I thought static was keeping the connection to a single instance per se. Below is a connection class that was shared when reviewing some tutorials. Would you have a suggestion here to better manage connections with this connection class? It sounds as if I should create some sort of tracking methods to manage the connections. Your thoughts or good source recommendation?

    Otherwise, I've debugged all my connections (e.g. Connection conn = DBConnection.getConnetion()) and validated they are being closed. I could have missed something but I have checked.

    Code:
    import researchApp.GlobalVariables;
    
    import javax.swing.*;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    
    public class DBConnection {
        private static final String SQLhome = "jdbc:sqlite:" + GlobalVariables.ResearchDB_PATH;
        public static  Connection getConnection() throws SQLException{
            try{
                Class.forName("org.sqlite.JDBC");
                return DriverManager.getConnection(SQLhome + DatabaseSource.getDatabase());
    
            }catch(ClassNotFoundException ex){
                ex.printStackTrace();
                JOptionPane.showMessageDialog(null, "DBConnection: " + DatabaseSource.getDatabase() + "could not be found." +
                        ex.toString());
                return null;
            }
        }
    }
    I also discovered while testing the following code that it returns an SQLException "database not connected" even though I'm checking for it. Initially, I was checking for 'conn == null' only, which most examples do. So I added the 'conn.isClosed' and it worked. The problem I'm facing is that all my methods connect this same exact way with only the 'conn == null' check without issue but this one method code returns 'database is closed'. So is there a difference between Close() and null on a DB connection? Otherwise, while would you check for != null or == null. I guess I'm a little confused.

    Code:
    private void initializeAuthorBox() throws SQLException{
            ObservableList<String> authors = FXCollections.observableArrayList();
            String strAuthor;
    
            try {
                String authorQuery = "SELECT a.AuthorID, a.FirstName, a.Middle, a.LastName, a.Suffix FROM Author as a";
    
                //Connection conn = DBConnection.getConnection();
                if(conn == null || conn.isClosed())
                    conn = DBConnection.getConnection();
                if(conn == null){
                    DisplayFailureMessage("The database 'conn' is null.", "Connection Failure: initializeAuthorBox");
                    System.exit(0);
                }
                PreparedStatement ps = conn.prepareStatement(authorQuery);
                ResultSet rs = ps.executeQuery();
    
                while(rs.next()){
                    strAuthor = ConcatenateAuthor(rs.getString("FirstName").trim(), rs.getString("Middle").trim(),
                            rs.getString("LastName").trim(), rs.getString("Suffix"));
                    if(!authors.contains(strAuthor + " - id: " + rs.getString("AuthorID"))){
                        authors.add(strAuthor + " - id: " + rs.getString("AuthorID"));
                    }
                }
                ps.close();
                rs.close();
                conn.close();
                this.cbxAuthors.setMaxHeight(30);
                this.cbxAuthors.setEditable(false);
                this.cbxAuthors.setItems(authors);
                this.cbxAuthors.setVisibleRowCount(5);
    
            }catch(SQLException ex){
                ex.printStackTrace();
                DisplayFailureMessage(ex.toString(), "Error: InitializeAuthorBox");
            }finally{
                if (conn != null) {
                    conn.close();
                }
            }
        }
    Last edited by SVOhio; March 21st, 2019 at 11:18 AM. Reason: updated

  4. #4
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    12,973

    Re: SQLITE .db file returns "in use" when connection is closed and Detach say no data

    As a test try to run some self contained code that opens a db, runs a query, cleans up and then tries to delete the db.

    I'm not a Java expert so I lifted some code from stack overflow - https://stackoverflow.com/questions/...lite-with-java.

    In this example, notice how not only is the connection cleaned up, but also the command and the result set? If these aren't cleaned up, then the driver may keep a handle to the db file open so you can't delete the file.

    Give this code a try (adapted to your db) and then try to delete the db file. If you can't do it, it could mean the connection or drivermanager clas is not releasing the db file.

    Try this code in a separate test app just to make sure you don't have an unknown bit of code connecting to the db.

  5. #5
    Join Date
    Jan 2019
    Location
    Java 1.8
    Posts
    12

    Re: SQLITE .db file returns "in use" when connection is closed and Detach say no data

    Hi Arjay!

    Thanks for the help and suggestion. I am actually handling every connection in the manner of the link you found. That being said, I think I did solve my problem by adding 1) a static Connection variable, 2) a static void close() method and 3) an if( == null || .isClosed()) to my DBConnection class. Prior to the attempt to delete the file I have a ClearConnection() class with a try/catch to perform a DBConnection.close().

    This approach seems to be working with the exception of not cleaning up a couple GUI controls. I don't know if this a good java practice but I also never found a java pattern to such a scenario - though I'm sure something similar is practiced.

    I appreciate very much your time and effort with my issue and giving suggestions to help me learn.

    Code:
        private void ClearConnection(){
            try{           
                DBConnection.close();
                ResetGlobalVariables();
            }
            catch (SQLException e){
                    e.printStackTrace();
                    LoggerWrapper.getInstance().myLogger.severe("Error: ClearConnection");
            }
        }

    Code:
    public class DBConnection {
    
        private static final String SQLHome = "jdbc:sqlite:" + GlobalVariables.ResearchDB_PATH;
        private static Connection con;
        public static  Connection getConnection() throws SQLException{
            try{
                Class.forName("org.sqlite.JDBC");
                if(con == null || con.isClosed())
                    con = DriverManager.getConnection(SQLHome + DatabaseSource.getDatabase());
                return con;
    
            }catch(ClassNotFoundException ex){
                ex.printStackTrace();
                LoggerWrapper.getInstance().myLogger.severe("DBConnection: " + DatabaseSource.getDatabase() + "could not be found." +
                        ex.toString());
                return null;
            }
        }
        public static void close() throws SQLException{
            if(con !=null) con.close();
        }
    }

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)