Standalone Form with non-embedded database

[nexturejohn]

Hi, I am starting a new question, as an earlier question has developed into 2 main different approaches. In the original question (link) I was looking for a clean closedown of LibreOffice when a Form closes. One of the suggested solutions by Mr Ratslinger was instead to use a standalone Form, based on original suggestions from DACM in 2009 (link).

The original Base connection was from file “connection_asmedb.odb” which connected to a Firebird 3.0 database called “ASMEDB.fdb”

Connection was through:

jdbc: firebirdsql:oo:localhost:c:\d\libre\ASMEDB.FDB?charSet=UTF-8
JDBC driver class: org.firebirdsql.jdbc.FBDriver
user: webusrjhn
password: whatever

I have followed the instructions (link) and generated “connection_asmedb.odt”, and managed to get this to work except for automatically connecting to the non-embedded Firebird database ASMEDB.FDB. The macro I used previously is included below.

I have tried binding this macro using Customize to various startup events:

Start Application, Document created, Open Document, Activate Document, New Document, Document loading finished.
It happily ignores them all and keeps prompting me for the password of “webusrjhn”.

How can I automatically and transparently connect to the database without having to provide the password each
time?

Sub connect_to_database
' bound to Open Document Event (see Customize - Events - Open Document)
' run once when connection_asmedb.odb file opened

	Dim sURL as string
	Dim oArgs(0) As New com.sun.star.beans.PropertyValue
	Dim oDocStatus as object
	Dim oIhandler
	
	oIhandler = CreateUnoListener("InteractionHandler_", "com.sun.star.task.XInteractionHandler")

'	sURL = "file:///C:/d/libre/connection_asmedb.odb" 			 'Windows
'	sURL = "file:///home/nexturejohn/Databases/connection_asmedb.odb"	 'Lubuntu
	sURL = ThisComponent.getLocation()
'	MsgBox "sURL apparently is =  " & sURL
	
	oArgs(0).Name = "InteractionHandler"
	oArgs(0).Value = oIhandler
	
	oDocStatus = StarDesktop.loadComponentFromURL(sURL, "_default", 0, oArgs)
	oDocStatus.DataSource.Password = "whatever"
End Sub

[Ratslinger]

Hello,

Can’t see a method in which the automatic sign in will work from anything other than a Base file. This problem is the availability of ThisDatabaseDocument.

It is a catch 22. This is needed for the connection but is not available outside of Base until the connection is made.

For me the code presented errors in Writer on:

oDocStatus.DataSource.Password = “whatever”

DataSource is not available until there is a connection.

Edit 2020-02-13:

As noted in the following comments, you can apply the user name and password to the connection string. However even if ‘Password required’ is the unchecked there is a bug which does not change the setting in content.xml. See this post → to reset password to default type

Thanks to @nexturejohn for this.

[nexturejohn]

The problem is that the .odt Form first reads the .odb file, which says that a JDBC connection has to be made to find the database, so it prompts for the username and password. Once a connection is established, then the Form starts up and uselessly tries to execute the connect_to_database macro, which by now is not necessary, as the whole point of this macro was to automatically connect to the non-embedded database!

Can you think of a way to configure the .odb file so that the username and password can be hardwired somewhere within? I tried setting db:is-password-required=“false” in the content.xml file, which stopped it asking me for the password, but then the connection was refused at the other end, i.e. the database we are trying to connect to. Can you think of any way to configure this database so that it will always accept a request from the .odt Form?

Otherwise it looks as if I will have to convert the .odb file back to include an embedded database.

[Ratslinger]

The only method I can see at this point is in the Base file, remove the requirement to enter password. Then in the connection string add the user name and password:

firebirdsql:oo://localhost//var/lib/firebird/3.0/data/YOURDB.fdb?user=your_login&password=your_password&charSet=UTF-8

This still presents a login box but simply hitting enter works.

[nexturejohn]

Ratslinger, you are a genius! With a slight tweak of your input, I have managed to automatically connect the stand-alone .odt Form to a non-embedded database, without any login or password prompts at all. It simply connects. It also works for the .odb Base file, so we can eliminate the use of the “connect_to_database” macro. And it works both on Windows and on Lubuntu!

Here is what needs to be done.
Edit the content.xml file in the .odb file, and substitute the section between db:connection-data and </db:connection-data> as follows (this is the Windows version, but Lubuntu is the same except for the file naming convention):

[nexturejohn]

old:

<db:connection-data><db:connection-resource xlink:href="jdbc:firebirdsql:oo:localhost:c:\d\libre\ASMEDB.FDB?charSet=UTF-8" xlink:type="simple"/><db:login db:user-name="webusrjhn" db:is-password-required="true"/></db:connection-data>

new:

<db:connection-data><db:connection-resource xlink:href="jdbc:firebirdsql:oo:localhost:c:\d\libre\ASMEDB.FDB?user=webusrjhn&amp;password=whatever&amp;charSet=UTF-8" xlink:type="simple"/><db:login db:is-password-required="false"/></db:connection-data>

This cannot be done completely in the GUI when setting up the connection to JDBC, some editing of the content.xml file is necessary to specify that the password is not required.

So this is now a general solution to allow a stand-alone Form to use a non-embedded database!
Perhaps you would like to suitably edit your “Answer” so that we can close this topic, thanks again for all your support.

[Ratslinger]

@nexturejohn,

Thank you. Forgot about that even though had answered that years ago. Will correct answer.

[nexturejohn]

Just done a further test, and if when first creating the JDBC connection from the Base GUI, you provide the username and password in the connection string as shown above, and then when prompted for username and password you leave these blank, then it will connect seamlessly, and you don’t need to edit the content.xml file

firebirdsql:oo:localhost:c:\d\libre\ASMEDB.FDB?user=webusrjhn&password=whatever&charSet=UTF-8