Python: HSQL DB remains open after closing? (jaydebeapi)

Question

Task I am writing a Python script that makes some changes in a HSQL-DB and then rexecutes a java-programm via the command line, which works with the data in the DB. I am using jaydebeapi for altering the data in Python.

Problem The java program fails because it cannot obtain a DB lock. This is despite me having closed the cursor and connection as stated in the documentation.

java.sql.SQLException: Database lock acquisition failure: lockFile: org.hsqldb.persist.LockFile@2b59e8f5[file =H:\Benutzer\Markus\Dokumente\Uni\IG_Ue\JAG3D_projects\project1\jag3d_project.lck, exists=true, locked=false, valid=false, ] method: checkHeartbeat read: 2021-01-13 21:03:33 heartbeat - read: -8526 ms.

Minimum working example

import jaydebeapi
import os

JAG3DAppCMD_path = r"custom_jag3d\JAG3DAppCMD.jar"
db_cmd_path = r"JAG3D_projects\project1\jag3d_project"

# Execute java program BEFORE DB was connected - WORKS
os.system(r'java -jar ' + JAG3DAppCMD_path + ' ' + db_cmd_path + ' TRUE')

# establish DB connection
UserName = "SA"
Password = ""
Java_Class = "org.hsqldb.jdbcDriver"
HSQL_Driver_Path = r"hsqldb-2.5.1\hsqldb\lib\hsqldb.jar"
Database = r"jdbc:hsqldb:file:JAG3D_projects\project1\jag3d_project"

conn = jaydebeapi.connect(Java_Class, Database, [UserName, Password], jars=HSQL_Driver_Path)
curs = conn.cursor()

# close DB
curs.close()
conn.close()

# Execute java program AFTER DB was connected - DOES NOT WORK
os.system(r'java -jar ' + JAG3DAppCMD_path + ' ' + db_cmd_path + ' TRUE')

Additional information When the DB connection is established a .log and .lck file are created as well as a .tmp folder. Those items are still present after I close the connection. When I close the python console they are deleted. I tried to manually delete the .lck file in my python script but I cannot as it is still used by a different process:

PermissionError: [WinError 32] The process cannot access the file because it is being used by another process: 'JAG3D_projects\\project1\\jag3d_project.lck'

Any help is aprreciated!

score 0 · Accepted Answer · answered Jan 14 '21 at 00:42

Your observations indicates that after the first connection via Python is closed, the database is still open.

The database is a resource that stays open once the first connection is made and it does not close automatically (by default). The .lck file prevents any other process from accessing the database.

You simply execute the SQL statement, SHUTDOWN at the end of the first set of changes. This closes the database in a clean manner.

Python: HSQL DB remains open after closing? (jaydebeapi)

1 Answers1