6

I'm running in to some problems with generating a persistent HSQLDB and during some troubleshooting I came upon the following behavior.

Needs["DatabaseLink`"]
tc = OpenSQLConnection[
 JDBC["hsqldb", ToFileName[Directory[], "temp"]], Username -> "sa"]
CloseSQLConnection[tc]

The above code generates three files (which will be located in Directory[]). Despite the fact that the connection is closed, two of the files (temp.lck and temp.log) cannot be deleted until the Mathematica kernel has been shut down. Is this 'normal' behavior?

bobthechemist
  • 19,693
  • 4
  • 52
  • 138
  • "DatabaseLink`" connections in Mathematica are done via Java and it is the JRE which actually holds the file locks. To get rid of those locks you can e.g. use Needs["JLink"];UninstallJava[];. After that you should be able to delete the files. There might be ways to remove those locks without closing the JRE with according Java calls, but that I don't know. I think it is an oversight (=bug) in the implementation of the HSQL part of "DatabaseLink`" that a CloseSQLConnection doesn't remove these file locks, so you might want to contact WRI and see what they say... – Albert Retey May 22 '13 at 10:50
  • @AlbertRetey Your suggestion to use UninstallJava[] works well. It'll be interesting to see if WRI has any comment. Thanks. – bobthechemist May 22 '13 at 20:54
  • @AlbertRetey would you care to make your comment into an answer, as this was the solution that worked for me. – bobthechemist Jun 09 '13 at 13:01
  • of course, I just did that. It's good to hear it helped (although I'd rather consider it a workaround than a "solution")... – Albert Retey Jun 10 '13 at 10:12

3 Answers3

5

"DatabaseLink"` connections in Mathematica are done via Java and it is the Java virtual machine which actually holds the file locks. To get rid of those locks you can e.g. use:

Needs["JLink"];
UninstallJava[];

After that you should be able to delete the files (you can do that from Mathematica, if so desired with DeleteFile). There might be ways to remove those locks without closing the JVM with according Java calls to the underlying HSQL libraries, but that I don't know (and have not time to explore).

I think it is an oversight (=bug) in the implementation of the HSQL part of "DatabaseLink`" that a CloseSQLConnection doesn't remove these file locks, so you might want to contact WRI and see what they say...

Albert Retey
  • 23,585
  • 60
  • 104
2

The last paragraph here indicates that the .lck extension is assumed by the OS to be managed by the application (which in this case is Mathematica). The .log is also in that category. The solution is to specifically delete those files after

CloseSQLConnection[tc]

I use the CUD pattern when programming: Create, Use, and Dispose. Mainly for memory allocations and temporary files.

Fred Daniel Kline
  • 2,360
  • 2
  • 20
  • 41
  • I think the problem of the OP is that what you suggest is not possible: Mathematica, or more precisely the JRE it uses for the database connection, still holds a file lock so you can't delete these files. Only when closing Mathematica (or the JRE) these file locks are removed and you can delete them. I think it is probably a bug in "DatabaseLink`" that these files remain locked after CloseSQLConnection[]... – Albert Retey May 22 '13 at 10:49
  • @AlbertRetey, I don't have SQL set up in order to test this, but from what I remember, those files can be deleted after the connection is closed. However, you may be right if the lock is not released by the disconnect. We could settle this if the OP would test it and get back to us. – Fred Daniel Kline May 22 '13 at 12:32
  • The interesting fact about HSQL is that it is all java and comes ready to use with Mathematica. It can be used in a serverless mode much like sqlite but also has a server, which also can be started from within Mathematica. Especially for the serverless mode there is no need for any setup, you can just go along and play with the OPs code (for me it complains about the username setting, so you might want to delete that option). You will find that these files are locked after closing the connection (I just have tried on Windows), but that might depend on OS or filesystem in use... – Albert Retey May 22 '13 at 13:49
  • @FredKline I'm a little behind in responding (I'm supposed to be on vacation) but Albert is correct that CloseSQLConnection[] does not release the lock on an HSQL database. Whether or not this happens with other SQL databases I am not sure (although it sounds like this is a Java <=> Mathematica communication error so presumably not). – bobthechemist May 22 '13 at 20:48
  • @bobthechemist: most other SQL database engines that you can access with DatabaseLink are server based and work completely different, there aren't any files accessable to the client involved so I wouldn't expect such problems for other engines (and that could well be the reason why this wasn't addressed). Exclusions could be the ODBC drivers to access data stored in excel files or access database files via SQL... – Albert Retey May 22 '13 at 21:32
  • I would strongly recommend against taking seriously anything you read on these file extension listing sites. Their main purpose, as far as I can tell, is to sell you mediocre applets such as "registry cleaners". Whether the information is accurate or even meaningful seems to be very much subsidiary to that goal. – Oleksandr R. Jun 10 '13 at 11:13
0

There shouldn't be a need to close the JRE in order to delete the .lck or .log file.

The .lck file exists for the duration of access to database, which starts with the JDBC connection. The purpose of the .lck file is to prevent another process from opening the same database. The .lck and *.log files are deleted when the SQL statement SHUTDOWN is executed and the database is shutdown.

fredt
  • 101