Share ODBC connexion with all users for Calc

English
, , ,
#1

I try to set configuration by GPO. My users are on a windows RDS. I want every user in calc to be able to request one of our DataWareHouse through ODBC. This ODBC connexion is declare at the server level, so every users can use it.

I’d like them to have this database automatically declared in calc. When I do it myself, it’s at the user level, but now I get too much users and I’d like to know if I could do it through GPO. Is there an odb file I can push to the user (where) or a configuration. Using GPO, I can distribute files to %APPDATA%\LibreOffice\4\user\config\ or C:\Program Files (x86)\LibreOffice\share\config. I didn’t manage to do it yet. It’s not clear to me what I have to put where (xcu, odb, xba…).

Regards,

#2

not sure how far this may help : Admin configuration …
since it’s not really covered by Extension Manager

another track : Good news for Windows Server Administrators: Group Policy template for LibreOffice available - Collabora Online and Collabora Office

#3

[Updated, see below:] No, the .odb will be the named datasource, so your question is at first “how to place a registered Datasource in the predefined/shared profile”, so it is accessible, when I add a new user.
.
First task is to find where your datasources are stored. I’d use the way (also suggested in the thread linked above): grep or search the name you set in GUI in the profile or registrymodifications.xcu
.
That thread contains also several links given by @Regina wich may help to understand the concepts used in the xml-files. They even refer to configuring datasources, but you will not find details there or an example for an odbc-connection.
.
I will also check the german version of the guide for Base, if the topic is mentioned there.
EDIT: Nothing found in the last version.
/EDIT

#4

The following is what I find for a registered datasource, wich is actually an SQLite-database via ODBC-driver in my local registrymodifications.xcu
(I added linebreaks for better reading, it is actually one line for every registered datasource.)

<item oor:path="/org.openoffice.Office.DataAccess/RegisteredNames">
<node oor:name="org.openoffice.SpEXIFDB" oor:op="replace">
<prop oor:name="Name" oor:op="fuse">
<value>SpEXIFDB</value>
</prop>
<prop oor:name="Location" oor:op="fuse">
<value>file:///C:/Users/Wanderer/Documents/EXIFDB/SpEXIFDB.odb</value>
</prop>
</node>
</item>

So I had to update my statement above. You need to put the name of the DataSource in registrymodifications.xcu, but the actual configuration is in the referred .odb-file.
.
The next problem comes with relizing the “local” path, so you need a place wich is equally accessible and working (I had problems with using network-shares, but may be specific to sqlite) or every user will have its own copy of the file wich connects to MySQL/postgres etc.
EDIT: The following source seems to say, we can use $(username) and maybe also %APPDATA% in config-files.
https://de.openoffice.info/viewtopic.php?t=75394
/EDIT
.
If you check the database-folder in the profile you see, it is possible to distribute a database in the profile, as this is already done for the bibliography-files, wich is a .odb connecting to dbase-files.

#5

If you manage to distribute the database document, but registration by xml fails, the following macro code registers a specified database document under a specified name.

Sub Register_Database()
REM path of database document within profile
Const cPath_ODB = "/database/MYDATABASE.odb"
REM data source name pointing to the above file
Const cRegName = "MYDATABASE"

oPS= createUnoService("com.sun.star.util.PathSubstitution")
sURL = oPS.getSubstituteVariableValue("user") & cPath_ODB
if not FileExists(sURL) then
	msgbox "Missing "& sURL, 16, "Macro: 'Register_Database'"
	exit sub
endif
oCtx = createUnoService("com.sun.star.sdb.DatabaseContext")
if oCtx.hasByName(cRegName) then
	if oCtx.getDatabaseLocation(cRegName)= sURL then
		REM do nothing
	else
		oCtx.revokeDatabaseLocation(cRegName)
		oCtx.registerDatabaseLocation(cRegName, sURL)
	endif
else
	oCtx.registerDatabaseLocation(cRegName, sURL)
endif
End Sub
#6

Thanks a lot for your help, there are so much conflicting documentations.

The odb file location doesn’t look to be the bigger trouble for me. I already put it in a local c:\LibreOfficeShare directory and I register this file by hand for all my user without any trouble. I just want to go from “by hand” to “automatically”.

Regarding Collabora ADMX, I though it was a good way. But there are no entry for Base, so no way to add DataSource this way… yet

I tried building an extension, I failed and don’t really know why: it’s not so easy to debug. LibreOffice is a big source code, where to set breakpoint just to follow extension installation. I tried to find logs, but it looks too hard/hazardous/time consuming.

So I have been the HelloWorld extension way. At least this extension works. But I didn’t find a way to show the HelloWorld box at application startup. If I had found, I would have been the macro way that could check if the DataSource is defined and set it if not. Actually, when I set my LibreOffice to start the HelloWorld macro when the ApplicationStart Event happens, it works. I catched the key in my registrymodifications.xcu file and tried to put it in the extension but without succes. So for now, the extension way is not working. But I’m still searching.

Now, if ever I managed with the extension way, I understand all this is just files you can put in the C:\Program Files (x86)\LibreOffice\share directory (or in the user profil one) respecting a really precise sub-directories to store your xcu and xba files, ans others… I found very few documentation about this, and some post saying to not rely on it as extension is the “official” way so the other one can change at any time/update. It’s a shame because: to put specific files to specific places is something GPO knows how to do really well.

If this message leads you to any inspiriting documents or exemples, tell me.

#7

Like most of the settings in Tools>Options, databases are registered in registrymodifications.xcu. For example, the biblio.odb registered as “Bibliography”:

<item oor:path="/org.openoffice.Office.DataAccess/RegisteredNames/org.openoffice.Office.DataAccess:DatabaseDocumentRegistration['org.openoffice.Bibliography']"><prop oor:name="Location" oor:op="fuse"><value>file:///home/andreas/.config/libreoffice/4/user/database/biblio.odb</value></prop></item>

where file:///home/andreas/.config/ is my %APPDATA% path.

#8

I finally managed.

For documentation purpose:

Of course wiki.documentfoundation.org/Documentation/DevGuide/Extensions is always interesting.

Knowing that, it took me a bit of time to find the right extension xcu spelling (I found it here: forum.openoffice.org/en/forum/viewtopic.php?t=4534). Then I had to understand you can’t add multiple oor:component-data in the same xcu file. So I modified my HelloWorld Extension accordingly.

My GPO:

  • Move an odb file on a network share to C:\Program Files (x86)\LibreOffice\share\database
  • Reinstall extension for all users (share) on startup (or through task scheduler if server never restart)

Thanks all for your ideas.

PS: sorry for the links, but I’m new. I chose the 2 best in my opinion.

1 Like

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