Ce document en français.
The use of this software subjects you to our Terms Of Use
jdbcDriverOOo is part of a Suite of LibreOffice and/or OpenOffice extensions allowing to offer you innovative services in these office suites.
This extension is the transcription in pure Java of the java.sql.* API to the com.sun.star.sdbc, com.sun.star.sdbcx and com.sun.star.sdb API of UNO.
It allows you to use the JDBC driver of your choice directly in Base.
It embeds the drivers for the following databases:
Thanks to drivers providing an integrated database engine such as: HsqlDB, H2, SQLite or Derby, it is possible in Base to very easily create and manage databases, as easily as creating Writer documents.
You will find the information needed to create a database with these drivers in the section: Connection URL
Being free software I encourage you:
In short, to participate in the development of this extension.
Because it is together that we can make Free Software smarter.
jdbcDriverOOo is a JDBC driver written in Java.
Its use requires the installation and configuration in LibreOffice of a JRE version 11 or later.
I recommend Adoptium as your Java installation source.
If you are using the HsqlDB driver with LibreOffice on Linux, then you are subject to bug #139538. To work around the problem, please uninstall the packages with commands:
sudo apt remove libreoffice-sdbc-hsqldb
(to uninstall the libreoffice-sdbc-hsqldb package)sudo apt remove libhsqldb1.8.0-java
(to uninstall the libhsqldb1.8.0-java package)If you still want to use the Embedded HsqlDB functionality provided by LibreOffice, then install the HyperSQLOOo extension.
On Linux and macOS the Python packages used by the extension, if already installed, may come from the system and therefore may not be up to date.
To ensure that your Python packages are up to date it is recommended to use the System Info option in the extension Options accessible by:
Tools -> Options -> Base drivers -> JDBC driver -> View log -> System Info
If outdated packages appear, you can update them with the command:
pip install --upgrade <package-name>
For more information see: What has been done for version 1.1.0.
It seems important that the file was not renamed when it was downloaded.
If necessary, rename it before installing it.
Restart LibreOffice after installation.
Be careful, restarting LibreOffice may not be enough.
soffice
and using the key combination Ctrl + C
if after stopping LibreOffice, the terminal is not active (no command prompt).This explains how to use an HsqlDB database.
The protocols supported by HsqlDB are: hsql://, hsqls://, http://, https://, mem://, file:// and res://.
This mode of use explains how to connect with the file:// and hsql:// protocols.
In LibreOffice / OpenOffice go to menu: File -> New -> Database
In step: Select database
In step: Connection settings
file:///tmp/testdb;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
file:///c:/tmp/testdb;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
java -cp hsqldb.jar org.hsqldb.server.Server --database.0 file:///tmp/testdb --silent false
java -cp hsqldb.jar org.hsqldb.server.Server --database.0 file:///c:/tmp/testdb --silent false
hsql://localhost/
In step: Set up user authentication
If the connection was successful, you should see this dialog window:
Have fun…
If you want to update an embedded HsqlDB database (single odb file), please refer to the section: How to migrate an embedded database.
It is possible to update the JDBC driver (hsqldb.jar, h2.jar, derbytools.jar) to a newer version.
If you use HsqlDB as database, follow these steps:
SHUTDOWN COMPACT
or SHUTDOWN SCRIPT
.Now your database is up to date.
This driver allows in LibreOffice / OpenOffice Base the management of users, roles (groups) and their associated privileges of the underlying database.
User management of the underlying database is accessible in Base via the menu: Administration -> User administration
The privileges management of the users of the underlying database is accessible in this window by the button: Change privileges
If the privilege is inherited from an assigned role, the checkbox is a three-state type.
The management of the roles (groups) of the underlying database is accessible in Base via the menu: Administration -> Group administration
The management of users who are members of the group of the underlying database is accessible in this window via the button: Group users
The management of roles assigned to the group of the underlying database is accessible in this window via the button: Group roles
This functionality is an extension of the UNO API and will only be available if the underlying LibreOffice / OpenOffice driver allows it.
Certain databases such as hsqlDB, H2, SQLite or Derby allow the creation of the database during connection if this database does not yet exist.
This feature makes it as easy to create databases as Writer documents. Generally it is enough to add the option expected by the driver to the connection URL.
This connection URL may be different depending on the operating system of your computer (Windows, Linux or MacOS).
To create a database, in LibreOffice go to the menu: File -> New -> Database -> Connect to an existing database, then according to your choice:
file:///home/prrvchr/testdb/hsqldb/db;hsqldb.default_table_type=cached;create=true
C:\Utilisateurs\prrvc\testdb\hsqldb\db;hsqldb.default_table_type=cached;create=true
file:///home/prrvchr/testdb/h2/db
C:\Utilisateurs\prrvc\testdb\h2\db
file:///home/prrvchr/testdb/sqlite/test.db
C:/Utilisateurs/prrvc/testdb/sqlite/test.db
/home/prrvchr/testdb/derby;create=true
C:\Utilisateurs\prrvc\testdb\derby;create=true
LibreOffice 7.0.4.2 - Ubuntu 20.04 - LxQt 0.14.1
LibreOffice 6.4.4.2 - Windows 7 SP1
I encourage you in case of problem
to create an issue
I will try to solve it
This driver was written to work around certain problems inherent in the UNO implementation of the JDBC driver built into LibreOffice / OpenOffice, namely:
In order to take advantage of the latest features offered by databases and among others HsqlDB, it was necessary to write a new driver.
Until version 0.0.3, this new driver is just a wrapper in Python around the UNO services provided by the defective LibreOffice / OpenOffice JDBC driver.
Since version 0.0.4, it has been completely rewritten in Java under Eclipse, because who better than Java can provide access to JDBC in the UNO API…
In order not to prevent the native JDBC driver from working, it loads when calling the following protocols:
xdbc:*
xdbc:hsqldb:*
xdbc:sqlite:*
xdbc:mariadb:*
xdbc:...
but uses the jdbc:*
protocol internally to connect.
It also provides functionality that the JDBC driver implemented in LibreOffice does not provide, namely:
The writing of this driver was facilitated by a discussion with Villeroy, on the OpenOffice forum, which I would like to thank, because knowledge is only worth if it is shared…
Using the new version of HsqlDB 2.5.1.
Many other fix…
Added a dialog box allowing to update the driver (hsqldb.jar) in: Tools -> Options -> Base drivers -> HsqlDB driver
Many other fix…
I especially want to thank fredt at hsqldb.org for:
His welcome for this project and his permission to use the HsqlDB logo in the extension.
Its involvement in the test phase which made it possible to produce this version 0.0.3.
The quality of its HsqlDB database.
Now works with OpenOffice on Windows.
An unsupported protocol now displays an accurate error.
A non-parsable url now displays a precise error.
Now correctly handles spaces in filenames and paths.
Many other fix…
Writing the Statement
, PreparedStatement
, CallableStatement
, ResultSet
, ...
services of JDBC (thanks to hanya for MRI which was of great help to me…)
Integration in jdbcDriverOOo of H2 and Derby JDBC drivers in addition to HsqlDB. Implementation of Java Services:
In order to correct possible defects, or incompatibility with the UNO API, of embedded JDBC drivers.
Renamed the HsqlDBDriverOOo repository and extension to jdbcDriverOOo.
Support in Base for auto-incrementing primary keys for HsqlDB, H2 and Derby.
Writing of com.sun.star.sdbcx.Driver. This high-level driver must allow the management of users, roles and privileges in Base. Its use can be disabled via the menu: Tools -> Options -> Base drivers -> JDBC Driver.
Implemented a Java service provider UnoLogger.jar for the SLF4J API to be able to redirect driver logging from the underlying databases to the UNO API com.sun.star.logging.*.
Rewrite, following the MVC model, of the Options dialog accessible via the menu: Tools -> Options -> Base drivers -> JDBC Driver, to allow:
Writing, following the MVC model, administration windows for users and roles (groups) and their associated privileges, accessible in Base via the menu: Administration -> User administration and/or Administration - > Group administration, allowing:
These new features have only been tested with the HsqlDB driver so far.
Integration of SQLite JDBC version 3.42.0.0. I especially want to thank gotson for the many improvements to the SQLite JDBC driver that made it possible to use SQLite in LibreOffice/OpenOffice.
This driver can be wrapped by another driver (HyperSQLOOo or SQLiteOOo) thanks to a connection url now modifiable.
It is possible to display or not the system tables in: Tools -> Options -> Base drivers -> JDBC Driver -> UNO drivers settings -> Show system tables
It is possible to disallow the use of updatable resultset in: Tools -> Options -> Base drivers -> JDBC Driver -> UNO drivers settings -> Use bookmarks
Many corrections have been made to make the extension SQLiteOOo functional.
Integration of MariaDB Connector/J version 3.1.4.
Many other fix…
Integration of H2 version 2.2.220.
Integration of logging in the resultset (ResultSetBase and ResultSetSuper) in order to learn more about issue 156512.
Many other fix…
Support in the creation of tables of the TypeInfoSettings parameter allowing to recover the precision for SQL types:
This is only integrated for the HsqlDB driver at the moment.
packaging
to the extension’s pythonpath
. Thanks to artem78 for allowing this correction by reporting this oversight in issue #4.pip install requirements.txt
INSERT INTO mytable (Column1, Column2) VALUES (data1, data2), (data1, data2), ...
) into a table with an auto-incremented primary key, to retrieve a ResultSet from the rows inserted into the table and therefore gives you access to the auto-generated keys in one go.CREATE TABLE...
, ALTER TABLE...
) that jdbcDriverOOo generates are now logged.java.sql.Statement.getGeneratedKeys()
interface and allows the use of the com.sun.star.sdbc.XGeneratedResultSet interface.java.sql.Statement.getGeneratedKeys()
as requested by JDBC 4.1.IgnoreDriverPrivileges
setting in the Drivers.xcu file.Normally, I managed to cover the entire scope of the UNO API (com.sun.star.sdbc, sdbcx and sdb), which took quite a while, but I didn’t initially think I would get there.
getTypeInfo()
, getTableTypes
and getTablePrivileges()
methods of the java.sql.DatabaseMetaData interface using respectively the TypeInfoSettings
, TableTypesSettings
and TablePrivilegesSettings
properties of the Drivers.xcu file.TablePrivilegesSettings
property is provided by the underlying driver, only the privileges declared in this property will be displayed. This allows for easier use. An improvement request #160516 was made to integrate this functionality into the Base code.The UNO SDBCX API can now be used for creating databases, as is the case for the latest versions of extensions using jdbcDriverOOo. It is possible to create tables, using the UNO API, with the following characteristics:
Using the UNO API to create databases will allow you to use code that is independent of the underlying database.
Clients using the jdbcDriverOOo driver can access features of the underlying JDBC driver through the XDriver.getPropertyInfo() method in order to access the necessary parameter when creating tables and display privileges correctly. These parameters being accessible directly by the driver can be obtained before any connection and therefore allows the creation of the database during the first connection.
Add new languages for internationalization…
Anything welcome…