Whats the effect of "Connection Pooling"?

English
#1

In LO you could set values for connection pooling: Tools → Options → LibreOffice Base → Connections.

It has nothing to do with holding a connection for example to a MySQL server somewhere in the www. This kind of connections will be limited by the server, not by a driver of LO.

Connection pooling seems to be used in a multi user environment. A connection from one user to the database will be saved for the pooling delay. So another user could use this connection and mustn’t establish a new connection.

I don’t know if I understood it right but I couldn’t find any reason to enable connection pooling. Could be it is impossible to reproduce any difference in behavior of database drivers for a single-user system.

I want to describe this in next Base Handbuch in German, but the help for this “feature” isn’t very helpful.

1 Like
Base will not ask for db credentials after closing and reopening
#2

In Addition from LO-online-help:

Connections

#3

Hello,
Have no practical experience pooling with LO Base, but here is a link to some further information > Connection Pooling

#4

Thanks - but its the same link I have posted in the question …

#5

Did not pay enough attention.

#6

Since 15 years I ignore this feature. However, I doubt that it has anything to do with a multi-user environment because LO is frontend to one user only. If I dare to speculate, connection pooling comes into play when the office user uses mail merge, linked database ranges, forms and the data source window in the same session.

1 Like
#7

And this is what I read in the description:

When the Connection object is closed, the physical connection is dropped, thus the overhead of opening, initializing, and closing the physical connection is incurred for each client session. A connection pool solves this problem by maintaining a cache of physical database connections that can be reused across client sessions.

I could open a database file and go to “tables”. Connection will be established. I’m asked for a password to external databases.
I could close this file, but don’t close LO. When I open the file again the connection is already there. When I open Writer and let me show a table of the database it will also be shown without problems. So the connection will never be closed up to the moment I close LO. And this is the same with connection pooling disabled or enabled.

Seems the connection pool don’t has any effect for the database connection.

#8

Since LO 7 I observe that database connections remain open when the database document is closed regardless of connection pooling being disabled.

#9

Did quick recent test with PostgreSQL and this was not true there. Took brief look at MySQL and same result - client connection removed when all of LO closed.

Version: 7.3.2.2 / LibreOffice Community
Build ID: 49f2b1bff42cfccbd8f788c8dc32c1c309559be0
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

.
Edit:
Tested with macro from Calc to MySQL.
.
With macro using a connection without a data source, the connection was made to the server and the connection was closed as soon as the macro ended.
.
With a macro using a data source (registered or URL) the connection remained open until all LO was closed.
.
Still find no difference in either test with connection pooling disabled or enabled.

#11

By the way: @Villeroy wrote about the database document, you about whole LO. Both is right. The connection will only be closed when LO is closed, not when only the database document will be closed.

#12

It will be closed if that is all that is currently opened and it was not stated what was or wasn’t opened.
.
Regardless, it is still a security issue if other parts of LO cause the Base connection (including user & PW) to remain open and active.

#13

This is the behavior of LO for all versions I could test, oldest here LO 6.1.5 for testing.
I will report a bug for this and will have a look at the following discussion …
Bug 148678

#10

It appears part of the problem is the lack of Base not severing a server connection until ALL of LO is closed. That seems to be a security issue. As noted in the last comment I posted, this is not true when using a macro for connection without a data source.

After reading the pooling wiki again and again, is where it came about to test without a data source (based upon info in this post > Connect to mssql (ODBC) database via Macro and noted AdrewBase document). Another read of the wiki and this seemed relevant:

A client obtains a pooled connection from the com.sun.star.sdbc.ConnectionPool service calling getConnectionWithInfo() at its interface com.sun.star.sdbc.XDriverManager and uses it just the same way it obtains and uses a non-pooled connection.

So instead of using:

oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")

just changed to:

oManager = CreateUnoService("com.sun.star.sdbc.ConnectionPool")

and re-rested.

Now instead of the connection being closed when the macro ended, when pooling was on it remained open for amount of time specified in the pool for the driver.

Do not have much more on this and can not say where or what benefits it may provide but it does show a difference between pooling on & off.

2 Likes

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