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.

#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:
  • oDb=CreateUnoService(“com.sun.star.comp.db.datasource.SimpleDataSource”)
  • oDb.DataSourceName=“registered_Name_here”
  • oCon=oDb.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.