Database table already open... 1

[dcusick]

What's up all? Quick question for anyone out there... I have a java app that I created that basically create a temporary table, in Access 97, does some manipulations, and then drops the table. This code works fine in VB. However, when I convert it over to Java, I keep getting an error at the end of the logic. This is the error

     ERROR executing [Microsoft][ODBC Microsoft Access Driver] The database engine could not lock table 'tmpDuplicateFunds' because it is already in use by another person or process.

This is happening when I attempt to drop the table. I am using a recordset that references the table right before I drop it, but I am closing the recordset before I call the DROP statement. Any ideas on what could be wrong? Thanks in advance...

Doug

 

[sedj]

I would say that the error message is correct - you must be leaving something open - a ResultSet, Statement or something OR somebody else has locked the table. If you really think your code is solid, then try closing down the Connection, and then explicitly getting another Connection to test whether this is the problem.

 

[dcusick]

Well, you are correct sir. I did as you suggested, and it worked. Any ideas what I am doing wrong??? I realize it's hard for you to say without my code, but I'll give you a quick paraphrase of what I have. I have a single class that configures my connection, runs all the queries, and closes the connection. In my main, I create a new instance of the class, which configures the connection, and then call a method that runs my SQL Statements. I have update and insert statements, and I also open up a ResultSet for a query or two. I have a separate method which runs my SQL statements. The method basically uses the active connection to open up a new Statement. Then it runs the SQL statement that I provide. I return the ResultSet. this is what my method looks like....

    private ResultSet createSafariRS(String strSQL) throws Exception {
        String m = "createSafariRS";
        print(m);
        
        try { 
            safariStmt = safariConn.createStatement();
            return safariStmt.executeQuery(strSQL);
        }
        catch (SQLException se) {
            print(m + "****** ERROR querying " + se.getMessage());
            throw new Exception("Error in " + m);
        }
    }

safariStmt is a private global variable. What I'm kinda thinking here is that I open up the new statement, and then return the ResultSet. I never physically close the statement. Not sure how it works, but would this close automatically after i leave the method? I kinda doubt it, so I actually closed it in my code. I close the resultset that's returned, then I close the statement, then I commit the changes on the connection. I would assume that this would sever all possible ties to the DB. But I'm still getting the error. But taking in your suggestion, if I just close the connection and reopen it, the DROP TABLE works fine. I guess I have to be keeping something on the connection open. Any ideas?? Thanks again.

Doug

 

[sedj]

You must always explicitly close ResultSets and Statements - a Conenction object will not successfully close, or may hang db side, if you don't, because the ResultSet object maintains a reference to the db's internal cursor incase you want to scroll the ResultSet data.

If you are defintely closing all objects, then I would have thought you could drop the table, but there may be some driver-db specific bug/clause where this is not possible.

 

[dcusick]

Thanks for the help. I am basically there at this point. I need one or two more things to go over before I am totally there. Much appreciated sedj. Your post got me up and running.