Customised Open Document Macro works first time round, but hangs and/or crashes the second time around

English
, , ,
#1

Hi Everyone:

I’ve created an application (ODB) with a macro that automatically runs when the application is opened: the macro hides the BASE GUI, prompts the user to enter login details that addresses a remote PostgreSQL Database, and then waits for the user to complete his/her work via an open form. When the user closes the form, a flag is switched that closes the application, so the user is supposed never to see the BASE back end.

I’m using Windows 11, as most of the users will be doing. All the relevant macros are stored with the ODB.

This works fine the first time through, and when the application is closed, no LibreOffice apps or background processes are left running in memory. When I try to run the application for a second time though (without rebooting the device etc.), either the BASE back end screen shows with access to the application’s associated macros lost and BASE still running, or the application simply hangs…

Is this behaviour caused by the way I close the application, or is it something else? I’ve tried every which way to close it but always with a simiar result. I’ve followed procedures for closing using discussions on this site ( 49147 ), (17036 ). Currently my code looks like:

Sub CloseBase


'this routine triggers the Close event for Base/LO to ensure a clean exit when the form is closed
'This is controlled through a global variable (keepAppRunning)

Dim oDesktop As Object
Dim oFrame As Object
Dim oDispatch As Object
Dim oConnection As Object


oDesktop = createUnoService("com.sun.star.frame.Desktop")
oFrame = oDesktop.getCurrentFrame()

’ Option to close any connection though I understand this may not be required

IF ThisComponent.CurrentController.isConnected() THEN

    oConnection = ThisComponent.CurrentController.ActiveConnection
    'disconnect the connection...
    oConnection.Close()

END IF


' Option 1 Close current component.

ThisComponent.close(true)
WAIT 400

' Option2 Close all documents and exit LibreOffice

oDesktop.terminate()

' Option3 Close the current document and potentially exit if it's the last one

' oDispatch = createUnoService("com.sun.star.frame.DispatchHelper")
' oDispatch.executeDispatch(oFrame, ".uno:Quit", "", 0, Array())

End Sub

This closes the application correctly and continues to work as expected until I attach the custom macro for Document Open; after that the app stops functioning the second time it is opened.

I’d be grateful for any guidance on this matter! I’m so close to finishing…

Best Wishes

CB

#2

Have had the same problem. On different systems the behavior is also different. So I have decided not to execute such a macro.

  • If macro editor is open for the *.odb-file the editor will crash when trying to close the database file by macro when the last form has been closed.
  • If there is only a *.odb-file open the database file won’t be really closed well: Some systems hang…

So: The only time it is running well: Other components like Writer, Calc … has to be opened. Then the Base file will be closed well.

See: Closing StarDesktop by macro from any document leads to crash of LO

#3

Hi RobertG

Thank you for your prompt reply - and for that link to the discussion that I mostly understood! This is not good news, though it means that I can stop beating myself up about it.

In my case, the user never gets to open the macro editor as all they ever see is a login dialog and a form screen - or at least that was the plan…

I thought my application was almost ready to go, though it seems now I must have a rethink. Do you have any suggestions for alternative strategies to exit BASE and so prevent a user from editing the form or accessing the data directly?

I will experiment…

Best Wishes,

CB

#4

Databases I am creating for other people open and directly after opening the forms will start. You could switch from one form to another by listbox. There are no icon bars, no status bar, no menu bar available. But when the user closes the form Base appears.

There is no way to get the user out of the forms, tables, queries and reports, because you could also change to the main window of Base by Alt → Tab (here, on Linux with KDE).

So the only possibility is to create standalone forms, which connect to the Base file.

#5

… or “start” forms (Writer, Calc), which implement the user interface and, if necessary, open .odb files in invisible mode (?)

#6

Don’t know if invisible Base window won’t be shown in the system as an open window.

If you will do this - how do you close this invisible window?

#7

I meant the usual course of action.
Macro for a Calc or Writer document:

Global oDBDoc as Object

Sub OpenDBDoc()
  Dim url
  Dim props(0) As New com.sun.star.beans.PropertyValue
  If oDBDoc Is Nothing Then
    url = ConvertToUrl("C:\Temp\Celsius.odb") 
    props(0).Name="Hidden"  
    props(0).Value=True
    oDBDoc =StarDesktop.LoadComponentFromUrl(url,"_default",0,Props)
  End If  
End Sub

Sub CloseDBDoc()
  If Not (oDBDoc Is Nothing) Then
    oDBDoc.close True
    oDBDoc = Nothing
  End If  
End Sub

Are there any pitfalls?

#8

Hi Guys

Thank you so much for your help. Bear with me: compared to you guys I’m a beginner!

Okay:

1. Closing App with LibreOffice in Memory
I loaded a Calc document, then with that document still open I ran my application by clicking on the ODB file icon: the application runs as expected, but when the form is closed (using the code in my original message), the same errors occur when I try to start the app again i.e. when I try to restart, the system hangs or the BASE form appears with BASIC code running and the app’s macros unavailable.

2. Using a Start form in Calc
Then I created a Calc document with two buttons (DBStart and DBStop) addressing the subs in sokol92’s code: the OpenDBDoc code runs as expected when the properties are to set “Hidden” and “False”, as my app’s own open-doc code hides the BASE Window on start up.

With my app form open, I execute the CloseDBDoc sub using the button in the Calc document, and the system then hangs…

3. StandAlone forms
If by StandAlone forms, you mean the creation of a Writer-style form, I tried this earlier in my own app’s development: using this method the form loads before the login dialog executes (The login dialog is necessary because I have to detemine the user’s language before displaying the screen)…

I’m using:
Windows 11
LibreOffice v24.8.03 (X86_64)

I am now working out if I can leave the user with the BASE Window open when the form is closed, with the remote database connected with readonly permissions. But this still leaves my code vulnerable to change by the user.

I am hoping you guys have a better solution?

Best wishes and thanks again,

CB

#9

The problem is synchronous execution of code, which doesn’t allow to do some destructive actions (closing document) in the middle of some other actions.

Note that there exist some ways to do something asynchronously.

  1. Dispatchers may handle a special SynchronMode boolean argument in their dispatch calls.

  2. There is AsyncJob service, which could be interesting.

1 Like
#10

I was first introduced to this technique through this post by @hanya.

#11

This is where the problem starts…
waits for the user to complete his/her work via an open form. When the user closes the form, a flag is switched that closes the application

Anything that prevents the Open Document event to finish running can cause a crash or become unresponsive.
Closing the document in the Open Document event or before the event finishes can do the same.

Sample database:
TestCloseDatabase.odb (13.5 KB)

The code in the sample database for hiding, minimizing/disabling and opening the form is commented out. This will allow testing the form to ensure it works with your OpenOffice/LibreOffice installation and operating system.

The code has only been tested in windows with AOO/LIBO version 4.1 and higher.
Your mileage may vary for other AOO/LIBO installations and operating systems.

If the only office application open is your database, closing the form will close the database without opening the start center thereby completely closing the office.
If there are other applications (writer, calc, base, ect) open, closing the form will only close the database.

The open document event presents 2 options.
Hide the base application…
Or
Minimize and disable the base application.

Pros and cons
If base is hidden users will not see or can interact with the base window. However, if there is 1 or more other office applications open, closing the last other application will also close the hidden base application. Which might not be desirable.

If base is minimized and disabled, closing other applications will not close the database.
Users can restore, maximize move and size the base window. However, they will not be able to interact with the base window nor will they be able to close it.

Macros for closing base

Sub CloseComponent(poComponent As Object)
'poComponent	ThisDatabaseDocument

Dim oComponentsEnum As Object, oComponent As Object
Dim i As Long, sUNO As String
oComponentsEnum = StarDesktop.getComponents().createEnumeration()
If (Not IsNull(oComponentsEnum)) Then
	Do While oComponentsEnum.hasMoreElements()
		On Local Error Goto errLocal
		oComponent = oComponentsEnum.nextElement()
		If Not isDatabaseComponent(oComponent) Then
			i = i + 1
		End If
		errLocal:
		On Local Error Goto 0
	Loop
End If

'Close the connection here if needed

'The application window must be visible
'poComponent.CurrentController.Frame.ContainerWindow.setVisible(True)
' OR
'The application window must be enabled
poComponent.CurrentController.Frame.ContainerWindow.setEnable(True)
Wait 300

If i = 1 Then
	sUNO = ".uno:Quit"
Else
	sUNO = ".uno:CloseDoc"
End If

unoQuitCloseDoc(poComponent, sUNO)
End Sub

Function getModuleIdentifier(oComp As Object) As String
Dim oModuleMgr As Object
oModuleMgr = createUnoService("com.sun.star.frame.ModuleManager")
getModuleIdentifier = oModuleMgr.identify(oComp)
End Function

Function isDatabaseComponent(oComp As Object) As Boolean
Dim sIdentifier As String
sIdentifier = getModuleIdentifier(oComp)
If sIdentifier = "com.sun.star.report.ReportDefinition" Then isDatabaseComponent = True : Exit Function
If Instr(sIdentifier, ".sdb") > 0 Then
	If sIdentifier <> "com.sun.star.sdb.OfficeDatabaseDocument" Then
		isDatabaseComponent = True
	End If
End If
End Function

Sub unoQuitCloseDoc(oDoc As Object, sUNO As String)
Dim oParser As Object, oFrame As Object, oDispatcher As Object
Dim aURL As New com.sun.star.util.URL
Dim arArgs() As New com.sun.star.beans.PropertyValue
If IsNull(oDoc) Then Exit Sub
If sUNO <> ".uno:Quit" Then
	If sUNO <> ".uno:CloseDoc" Then Exit Sub
End If
oDoc.setModified(False)
oParser = createUnoService("com.sun.star.util.URLTransformer")
aURL.Complete = sUNO
oParser.parseStrict(aURL)
oFrame = oDoc.CurrentController.Frame
oDispatcher = oFrame.queryDispatch(aURL, "_self", com.sun.star.util.SearchFlags.NORM_WORD_ONLY)
oDispatcher.dispatch(aURL, arArgs())
End Sub

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