Assigning DataSourceName to mailmerge locks .ODB and fails to unlock later

[Wolfling]

The following partial VB script assigns a DataSourceName to a mailmerge template and then merges to create a new document. At the moment that the DataSourceName is assigned, an OS level read-write lock is applied to the .ODB file - prohibiting other users from using it. Note that even after the original template is disposed, the file lock remains until Writer is completely closed and the desktop instance is unloaded.

Environment: Windows Server 2019. LibreOffice 7.3.7.2 (x64)

The kludge/workaround is to perform the merge, save the output document, completely close the Desktop, and then re-load it with the final document.

I’m currently considering using a headless instance to perform the mailmerge on another machine, and then only ever open the final document on the user’s machine.

Any other suggestions?

sName = Substitute(strFilename, “\”, “/”)
Set objMailMerge = oSM.createInstance(“com.sun.star.text.MailMerge”)
objMailMerge.DocumentURL = sName
objMailMerge.DataSourceName = “CSV”
objMailMerge.Command = “MailMergeData”
objMailMerge.CommandType = 0
objMailMerge.OutputType = 4
objMailMerge.SaveAsSingleFile = 1
Set newDoc = objMailMerge.Execute(Array())
objMailMerge.dispose
oDoc.dispose
newDoc.getCurrentController().getFrame().getContainerWindow().setVisible (True)
Set oDoc = newDoc

[mikekaganski]

@Wolfling

partial VB script

How does it connect to a LibreOffice instance - is the instance running already, and does it keep running after the VBS ends?

@Wolfling

sName = Substitute(strFilename, “\”, “/”)

Note that this substitution is not enough, and would fail in all but most trivial cases. The proper file URL is percent-encoded, has the schema, and not so trivial to create.

For LibreOffice Basic, it’s easy to use the built-in ConvertToURL Function; for other situations (like VBS), you might want to use the com.sun.star.util.URLTransformer service, like in this VBS code:

  Set oSM = CreateObject("com.sun.star.ServiceManager")
  Set oURLTransformer = oSM.createInstance("com.sun.star.util.URLTransformer")
  Set oContext = oSM.getPropertyValue("DefaultContext")
  Set oReflection = oContext.getValueByName("/singletons/com.sun.star.reflection.theCoreReflection")
  dim url
  oReflection.forName("com.sun.star.util.URL").createObject url
  url.Complete = "c:\foo\bar\baz.abc"
  oURLTransformer.parseSmart url, ""
  MsgBox url.Complete

[Wolfling]

LibreOffice is started using CreateObject:

  Set oSM = CreateObject("com.sun.star.ServiceManager")
  Set oDesk = oSM.CreateInstance("com.sun.star.frame.Desktop")

The document is opened thus:

  Set oDoc = oDesk.loadComponentFromURL(strFilename, "_blank", 0, OpenParam)

I usually set the OpenParam so that the document opens hidden. I make the document visible later on if its appropriate for the end user to see it. Some documents just ‘open and print’, but some are ‘open and display for the end user to edit’. For the ‘open and print’ ones, LibreOffice is closed after they’re finished.
I do this to ‘force’ close the document:

  oDoc.setModified False
  oDoc.dispose
  Set oDoc = Nothing

And then, this is how I terminate LibreOffice:

  oDesk.Terminate
  Set oDesk = Nothing
  Set oSM = Nothing

Thanks for the code on converting Windows filenames to URLs. In my application, the filenames are tightly controlled, so there’s never a scenario where a simple substitution wouldn’t work, but its still better to have more elegant code. I’ll modify my code to use ConvertToURL.

[mikekaganski]

As explained in the MailMerge Service documentation, and in the DataAccessDescriptor Service documentation, the DataSourceName (and other properties) define a connection that the MailMerge will be using (i.e., such a connection will be established). In turn, the connection has own behavior, as explained in wiki. It may be reused, it is pooled, so to disconnect, you need to use the connection methods.

[Wolfling]

Thank you for this information. It helps me understand why the connection is not being dropped even when I dispose of the MailMerge object. Please forgive my ignorance. I am quite new to Star Basic.

I’ve spent some time looking at the DataAccessDescriptor Service documentation. It appears that I need to explicitly close() the connection object, but I don’t really understand how to instantiate it. I know what it is called (I have named it CSV.odb).

Any chance you could direct me to some sample code that might point me in the right direction?

[mikekaganski]

The DataAccessDescriptor Service provides the ActiveConnection property, which is already instantiated connection object that you need.

[Wolfling]

Thanks again for the information. I’m making some progress, but still have no elegant solution.

My objMailMerge object has an ActiveConnection property, but it returns Null.

I’ve tried enumerating the DatabaseContext objects with the matching DataSourceName and explicitly closing any the connections. I’ve tried the getConnection property of the ConnectionPool so that I can get the physical connection and close it. It didn’t work. However, this is also a difficult thing to test. If I use the getConnection method to see if I’ve successfully closed the physical connection, the connection pool re-opens the connection and sends me a fresh one, so the IsClosed property always returns false.

I tried another plan - which was to turn off connection pooling entirely, but the behaviour still manifests.

I’ve also tried unregistering the database from Writer entirely, but no luck. The OS level filelock remains in place until Writer is completely closed.

My original kludge is working for now. Just underperforms a bit as it has to start the desktop twice to complete the task.

[mikekaganski]

Try this after completion:

dbc = oSM.createInstance("com.sun.star.sdb.DatabaseContext")
csv = dbc.getByName("CSV")
csv.DatabaseDocument.dispose()

[Wolfling]

Hello again. Still no joy. I am content to leave this problem unresolved. Whilst my solution is a kludge, it works ok, and I’m not getting any negative feedback from the customers.