Unlock database via macro?

English
#1

My database is private to me (no other user). I am also using LibreOffice Base only as the front-end to tables stored in SQLite (via ODBC). From time to time (possibly through poor coding on my part), when I run a query from a form, I get the response ‘Database is locked’ (presumably by a previous query). I haven’t managed to discover how or why; if I close down LO and reopen, the problem goes away - but I would like to place code somewhere that will simply unlock the database without my having to do that.

Is there a way to do this?

#2

As far as I know, SQLite has no authentication. With some other database, you could organize permissions of database users and groups of users and tell Base that the database requires a log-in with user name and password.
You may store the database on an encrypted drive or wrap it in a zip archive with password protection.

#3

Thank you, but I think you have misunderstood the problem. There is no question of another user, since the whole database is only on my computer. But something I do - but which I have not identified - locks a table and then fails to unlock it. I wish, therefore, explicitly to be able to unlock a table or tables (not basic data tables, but answers to queries which need to be reused?).

#4

If the underlying database does not support user and group privileges (even for a single user), Base can not do anything about it.

#12

You are trying to build a database with a LibreOffice frontend?
You are stuck with SQLite because it is not the right backend?
Your SQLite database is locked (read-only)?
Create a new embedded database of type HSQL.
Copy the tables into the new database.
Add relations.
Add the LibreOffice frontend consisting of forms, reports and office documents.

Extract the HSQLDB and upgrade to HSQL 2.4.
If you need simultaneous multi-user database access, set up a HSQL server from the extracted database.

#13

But to solve a problem with locks by multi-user access seems overkill and bad coding at the same time.
.
For the actual problem I have no solution. If a database tells me, there is a locking problem and gives me an unlock/ignore option they could simply drop locking at all…
.
I found the suggestion to use timeouts in the following link, but did not explore, if we can set theese from LibreOffice or in ODBC config.

#14

I separated the front end from the data tables several years ago, because I read that HSQLDB is unstable with large data sets. I have on the whole no difficulty with the setup I have now (and it is somethimes convenient to manipulate the data through SQLite Studio instead of LibreOffice Base).

Since the database is currently entirely private to me, there is no multi-user issue, and the occasional lock-up is an irritation rather than a major problem.

From Beekeepr Studio (thank you) I get “Always close the database connection properly after operations are completed. This prevents the database from being unnecessarily locked.” I suspect this is where my (sloppy) coding lies. When making a connection within a macro to run a query, I have never explicitly broken that connection at the end of the macro. Perhaps I should. I may experiment with this.

#15

I think when you work with LibreOffice Base there is no reason to open or close a connection. I use always the connection of the database document or of the form in a form-document. This works also when I work with standalone writer form-document. In this form-document I have a connection to two different database documents but LibreOffice takes care for the connections.

This idea works real good a form-document where I use different connections to two different databasources. LibreOffice take real good care of all the connections. In one of that form I do only point to the datasource and not to a table or query but LibreOffice make the connection all does close it. When it close the connection I have not to know there it is a simple database.

#16

Thank you - that is much in line with what I have just written. I must do some further experiments with connections!

#17

Embedded databases are unstable with large data sets. Once you created your embedded HSQLDB with forms, reports and office documents, it is fairly easy to extract the HSQLDB from the Base container and let it grow over the years of usage.
An extracted HSQLDB

  • can be upgraded to recent versions with many more features.
  • supports privileges of users and groups.
  • can be run in server mode for multiple logged in users at the same time.

while still working (almost) perfectly well with the Base frontend.

#18

I don’t think embedded Firebird is unstable. But with embedded there is always a risk because the data is not saved until the Base file is saved.

There is the option to work directly with a Firebird external database without Firebird server installation.
https://help.libreoffice.org/latest/en-US/text/sdatabase/dabawiz02firebird.html?DbPAR=BASE#bm_id641677867638107

#19

I have to say that - possibly because I am consulting the DB in a different way at the moment - the ‘database is locked’ problem has not arisen in the last few days. However, as a result of that last comment by RPG about connections, I have removed from my code the line

if not ocontroller.isconnected then ocontroller.connect

which was prefixed to the construction of all SQL queries (on advice). It has not apparently affected the functioning of these macros, but may have been the source of the problem. For now - while I have still not found a way to unlock from within a macro - I consider the problem to be solved.

#20

if not ocontroller.isconnected then ocontroller.connect

There is one reason to use such a line of code. When you open a databasedocument and want open automatic an embedded form. I do not know if LibreOffice does need it now and maybe you need also to close the connection. But this all you do not asked.

#5

The error The database is locked may be due to how SQLite handles concurrent access. Even if you’re the only user, sometimes open connections or unfinished transactions remain after running forms, queries, or modifying records.

Since there’s no unlock command, you could close and reopen the connection using a macro, equivalent to closing and reopening it without closing Database.

Something like:

Sub ResetConnection
Dim oDBContext As Object
Dim oDatabase As Object
Dim oConnection As Object

oDBContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")

' Change "YourDatabaseName" to the name of the connection in Database
oDatabase = oDBContext.getByName("YourDatabaseName")

If oDatabase.IsConnected Then
oConnection = oDatabase.CurrentConnection
oConnection.close()
End If

' Reconnect
oDatabase.getConnection("", "")
End Sub

You could use it in:

Button on a form or in an automatic event

#6

Are you seeing mention of a ~.lock file perchance that is zero bytes in size and if deleted will unlock a locked database or other document mentioned in the message about that ~.lock file

#7

Thank you, this looks promising BUT … I cannot find a database name that this code will recognize. I get the error message

"BASIC runtime error.
An exception occurred
Type: com.sun.star.container.NoSuchElementException
Message: nnnn.

Where nnnn is the name I have given it, whether it is MyDB-FrontEnd.odb (the LibreOffice file), MyDB-Data.sdb (the SQLite database) or MyDB-Link (the name of the ODBC connection).

Any offers?

#8

Of course, just at the moment I cannot replicate the problem. But I do recollect that there was a .lock file, which was deleted when the app was closed.

#9

“NoSuchElementException” in this situation would usually mean that the “name” of the db does not exist, or is not assigned properly (misspelled). I believe the macro is wanting the name of the LO (frontend) odb, not the name of the SQLite (backend) database (which apparently has been reconciled). May it just be a matter of adding the extension “.odb” in the LO frontend name? (“nnnn.odb”)

#10

Hmm … I have tried all variations of the filename, including the full path, but get the same exception every time.

Ah, I have moved one step further forward. For the name, go to LibreOffice|Preferences|LibreOffice Base|Connections

Then add a new connection, giving it a name and browsing to the MyDB-FrontEnd.odb file location.

When this name is used in the code suggested above, it is read successfully, and I get a new error message: “Property or method not found: IsConnected”

I greatly appreciate everyone’s input, but I need just a little bit more!

#11

When a “property or method not found” occurs, the oDatabase object is not the proper type.

  • Before the guilty If statement in the macro type: msgbox(oDatabase.dbg_properties). When executing the code a message box will pop-up listing the properties of the object. Also use .dbg_methods to list all the methods that apply to the oDatabase object. These are a good place to debug the “property or method” error. If the LO odb is registered, try modifying the macro with the following:
  • oDs=CreateUnoService(“com.sun.star.comp.db.datasource.SimpleDataSource”)
  • oDs.DataSourceName=“registered_Name_here”
  • oCon=oDs.getConnection(“odbc_sqlite_URL_here”)
    or simply by method .currentConnection()
  • oCon.close()

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.