Do You Want The Wizard To Register The Database In Libreoffice

Copyright

This certificate is Copyright © 2021 past the LibreOffice Documentation Squad. Contributors are listed beneath. You may distribute information technology and/or modify information technology under the terms of either the GNU General Public License (https://world wide web.gnu.org/licenses/gpl.html), version three or later, or the Artistic Commons Attribution License (https://creativecommons.org/licenses/by/4.0/), version four.0 or later.

All trademarks within this guide belong to their legitimate owners.

Contributors

To this edition

Pulkit Krishna

Dan Lewis

Jean Hollis Weber

To previous editions

Martin Fox	Robert Großkopf	Pulkit Krishna
Jost Lange	Dan Lewis	Hazel Russman
Jochen Schiffers	Jean Hollis Weber

Feedback

Please straight any comments or suggestions nearly this certificate to the Documentation Team's mailing listing: documentation@global.libreoffice.org.

Annotation

Everything you send to a mailing list, including your e-mail address and whatsoever other personal data that is written in the message, is publicly archived and cannot exist deleted.

Publication date and software version

Published December 2021. Based on LibreOffice 7.2 Community.
Other versions of LibreOffice may differ in appearance and functionality.

Introduction

The basics of creating a database in LibreOffice are described in Affiliate 8 of the Getting Started Guide, Getting Started with Base.

The database component of LibreOffice, called Base of operations, provides a graphical interface for working with databases. In addition, LibreOffice contains a version of the HSQL database engine. This HSQLDB database can be used by merely a single user. The entire data fix is stored in an ODB file that has a file locking mechanism in the configuration folder when opened by a user.

Since LibreOffice four.two, an internal Firebird database has been available in addition to the internal HSQLDB one. The Firebird database is included in "experimental features" in version half-dozen.4 due to its instability (though it has improved much). The database examples in this book keep to refer to HSQLDB, but are customized so that virtually functions are direct transferable to Firebird. If necessary, alternatives in Firebird are shown.

Creating a new database using the internal HSQL engine

If a database with multiple users is not planned, or if you wish to gain some initial experience with a database, the internal database engine will suffice. It is possible at some after stage to transfer the database to an external HSQLDB environment, where multiple users tin have concurrent access to the database on the HSQLDB server. This is described afterwards in this chapter.

To create an internal database from the LibreOffice Start screen, click the Database button; or, from anywhere in LibreOffice, utilize File > New > Database. The Database Wizard (Figure 1) opens.

Figure 1: Step 1 of Database Wizard: Select database

Select Create a new database. By default, this is an embedded HSQLDB database. An internal Firebird database is besides available; see Circumspection note on next page.

The other options serve to open an existing file or create a connection to an external database such every bit an address volume or a MySQL database.

Choose Next>> to proceed to Stride 2 of the Database Wizard (Figure 2).

Figure 2: Step 2 of Database Wizard: Salve and Proceed

A database registered with LibreOffice can be used by other components of LibreOffice, for example for mail merge letters in Writer. It is recommended that all databases should be registered when created, but the final option belongs to the reader.

Select Open the database for editing and deselect Create tables using the table magician. The latter selection was covered in Chapter 1; the rest of this book does not use the wizards for creating tables, queries, and so on.

Click Finish to salve the database. A standard Salvage As dialog opens, requesting a proper name and location for the *.odb file, which is ready for the entry of records from the internal database, and the storage of queries, forms, and reports. Unlike in the other parts of LibreOffice, the file is saved before yous have fabricated any visible entries.

Caution

If Experimental Features are turned on, then if an existing internal HSQLDB database is opened, a message may be displayed, asking if you wish to migrate the database to Firebird now.

Caution is advised: Do not merely confirm. Be extremely conscientious here! Click Later. When yous are ready to migrate the database, follow this procedure:

i) Back upwards the HSQLDB database file.

2) Adjust functions as far every bit possible according to the list on this wiki folio:
https://wiki.documentfoundation.org/Documentation/FirebirdMigration

iii) Copy views that tin can non exist direct converted from the SQL lawmaking and save them equally queries.

4) Table Names and Column Names in Firebird can be a maximum of 31 characters long. If necessary, arrange to a shorter proper noun.

5) Pure text tables (integrated *.csv table, etc.) are not possible under Firebird, and so they must exist placed elsewhere.

Accessing external databases

An external database must exist before it can be accessed. If access to a database is desired, the database must be prepare to let network connections with a specific user name and password before external programs tin can connect to it.

When such a database is properly set, you may, depending on the available connection software (the database driver), create tables, input data, and query data.

Click on File > New > Database to open up the Database Sorcerer (Figure one) and select Connect to an existing database. The list of bachelor database types varies according to operating system and user interface, only the post-obit should always be available:

• dBase

• JDBC

• MySQL

• ODBC

• Oracle JDBC

• PostgreSQL

• Spreadsheet

• Text

• as well every bit various types of address books.

The connection options vary according to the type of database selected. You tin change the options after the *.odb file is created.

Some database types (for example, a connection to spreadsheets) practice not allow new data to be entered. These are used but to search for or written report on existing data. The descriptions in the post-obit chapters deal exclusively with LibreOffice Base using the internal HSQLDB database. Most of the design work can be extended to databases that use MySQL, PostgreSQL, and and then on.

Here are a couple of brief examples of how you can connect with an external database.

MySQL/MariaDB databases

Base of operations can connect to MySQL and MariaDB databases by three methods. The simplest and fastest way is direct connection with the MySQL connector. The other two are connection using ODBC or JDBC.

Note

In MySQL and MariaDB, it is possible to enter and change information in tables without a primary cardinal field. The GUI of Base displays these tables, but offers no input or modification options.

If you lot want to use tables without a chief cardinal, you tin utilize Tools > SQL instead, or inside forms via macros, to supply the tables with data.

Create a user and a database

After MySQL or MariaDB has been installed, practise the following steps in the sequence described.

i) The ambassador account in MySQL is called root. Linux users should annotation that this is not the root user of the Linux operating system. The root user must be assigned a countersign directly after installation, if this was not washed before installation.
mysql -u root -p

To begin with, no password is set, so just printing Enter. An entry prompt appears:
mysql>

All the post-obit entries are fabricated at the MySQL console. The passwords tin be different according to whether the prompt comes from the local computer (localhost) or a different computer which is acting every bit a MySQL server (host).
SET Password FOR root@localhost=PASSWORD('Password');
SET Password FOR root@host =PASSWORD('Password')

For Windows users, the second line reads:
Set up PASSWORD FOR root@'%'=Countersign('Password');

2) As a security measure out, all current anonymous users are deleted.
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.db WHERE User='';
Affluent PRIVILEGES;

3) A database chosen libretest is created.
CREATE DATABASE libretest;

4) All rights to the libretest database are granted to the user lotest, who will log in with the password libre.
GRANT ALL ON libretest.* TO lotest IDENTIFIED BY 'libre';

Now the database is available and tin be continued to every bit follows.

Straight MySQL connection using an extension

Starting with LibreOffice half dozen.two, the straight connection from Base to MySQL / MariaDB has been integrated into LibreOffice. Installing an extension is no longer required.

MySQL connection via JDBC

General access to MySQL is via JDBC or ODBC. To be able to use JDBC, it is necessary to install mysql-connector-java.jar. This Java Archive file is all-time copied into the same binder where the current java version used in LibreOffice is located. This is probable to be a subfolder like ...javapath.../lib/ext for a Linux installation.

Alternatively the appropriate folder containing the Java archive tin can be set through Tools > Options > LibreOffice > Advanced > Coffee > ClassPath.

MySQL connection via ODBC

To connect via ODBC, you must of class accept ODBC software installed. Details of how to practise this are not given here.

Later on installation of the software, it may happen that LibreOffice refuses the service because it can't find the libodc.so library. In almost systems, libodbc.then.ii will exist present. You will need to make a link to this file in the same folder with the name libodbc.then.

In the odbcinst.ini and odbc.ini, which are necessary for the organisation, you need to make entries like to the following:

odbinst.ini

[MySQL]

Clarification = ODBC Driver for MySQL

Driver = /usr/lib/libmyodbc5.so

odbc.ini

[MySQL-examination]

Description = MySQL database test

Commuter = MySQL

Server = localhost

Database = libretest

Port = 3306

Socket =

Option = 3

Charset = UTF8

In Linux systems, these two files are located in the /etc/UnixODBC folder. If you do not enter the grapheme set that y'all are going to use, at that place can exist problems with umlauts fifty-fifty if the setup is the aforementioned in MySQL/MariaDB and in Base of operations.

Details for the connection parameters tin can be establish in the MySQL Handbook.

Connecting to a MySQL database with the Database Wizard

To access an existing MySQL database with a straight connection, follow these steps.

At Step 1 of the Database Wizard, select Connect to an existing database. From the list of database formats on the pull-down menu (Figure 3), select MySQL. Click Next>>.

Figure iii: Connecting to an existing MySQL database

At Pace 2 of the Database Wizard (Effigy 4), select to connect using ODBC or JDBC or direct.

Figure 4: Step ii of Database Wizard: Fix MySQL connectedness

Direct connectedness

Straight connection is best for both speed and functionality. At Stride 3 (Figure 5), fill in the required information.

Figure 5: Footstep iii of Database Wizard: Enter required information for the connection

The database name must be known. If the server is on the same calculator equally the user interface in which the database is to be created, you tin can select localhost equally server. Otherwise yous can utilize an IP address or, according to the network structure, the name of the computer or even an Internet address. It is thus possible for Base to admission a database which is on someone's home page.

When working with Base of operations over the Internet, you demand to be aware of how the connection is fix. Is the connectedness secure? How is the password transmitted?

Any database accessible over the Net should be protected by a specific username with a password. This provides a directly style of testing whether the connexion should go ahead. A corresponding user needs to be set up in MySQL or MariaDB for the named server.

Figure 6: Step 4 of Database Wizard: Set user hallmark

At Step 4, provide a username and select Password required. Click the Test Connectedness button to launch hallmark with the given user name. Subsequently inbound the password, yous are informed whether the connection was successful. If, for example, MySQL is not currently running, yous will get an error bulletin.

Notation

On each subsequent occasion that the database file is accessed, the dialog below appears when y'all first access the MySQL database.

Click Next>> to display Step 5 of the Database Wizard (Figure seven). Select No, practise non annals the database and Open the database for editing. Click Stop.

Figure vii: Step 5 of Database Wizard: Decide how to proceed subsequently saving the database

In this example, the database volition non be registered, equally it is just existence congenital for tests. Registration is only necessary if other programs such equally Writer are to access the records, for instance for a mail merge.

The wizard ends the connectedness setup past saving the database connection. The Base file is created and a view of the tables of the MySQL database is opened (Figure viii). The database tables are displayed under the name of the database itself.

At this phase the *.odb file contains only the connection information that will be read each time the database is launched, and so that the tables in the MySQL database can be accessed.

Some drivers will prove only the libretest database for which the connexion was ordered. Other drivers besides evidence other MySQL or MariaDB databases on the same server. What will be seen is totally dependent upon the specific driver and operating system existence used.

Even with drivers showing just one database, admission to other tables for queries is possible if the database user (lotest in the above example) tin access the records with their password. Unlike the previous native LibreOffice drivers, this ane does not provide write access to other MySQL databases on the same server.

Note

The statements in the above paragraph are dependent upon the operating system and driver that is used. Open SUSE will requite the results seen. Ubuntu, using either the latest MySQL commuter or directly connecting to the MySQL server, will allow visual and write access to all databases for which the user has access rights. Other operating systems and drivers may give different results.

Effigy viii: View of the open database file with table overview and in the footer of the designation of the commuter used MySQL (Native), the database proper name libretest, the user of the database lotest and the server on which the database is running, localhost.

In contrast to Base of operations's internal database, queries in MySQL require the database name for defining the tables. For example:

... FROM "test"."Course" As "Class",...

It was necessary to give the combination of database proper name and table name an alternative name (alias) using "Every bit". Several versions agone, the apply of As was dropped with the allonym written without it. For example:

... FROM "test"."Grade" "Form",...

Tables can be created and deleted in the database. Automatically incrementing values (AutoValues) work and tin can be selected at the tabular array blueprint stage. In MySQL, the values kickoff at i.

Connecting using ODBC

The showtime steps to making an ODBC connectedness are the same every bit for a direct connection. If an ODBC connection to MySQL is selected in the second stride, the page shown in Figure 9 appears in the Database Wizard.

Figure ix: Setting up a connectedness to an ODBC database

The ODBC data source need not have the same name as the database in MySQL itself. Here y'all must enter the name that is listed in the odbc.ini file. The simplest mode to do this is to read the proper name directly out of odbc.ini past using the Scan button.

Effigy 10: Choosing a data source

The name from the odbc.ini file appears. Here besides, when y'all connect to a database, other tables on the MySQL server can be read quite easily.

Steps 4 and v are identical to those for a directly connexion.

Connecting using JDBC

For a JDBC connectedness, the outset steps are the same. The deviation appears only with stride 3 (Figure 11).

The wizard asks for the same information as for a straight connection. The database proper noun is the 1 used by MySQL itself.

Use the Exam class push to test whether the annal mysql-connector-java.jar is accessible by Java. This annal must either be on the path of the chosen Java version, or directly embedded in LibreOffice.

All further steps are identical to the previous connections. Connections to other databases on the same MySQL server are attainable by any user who has access rights to them.

Effigy 11: Setting up a connection using JDBC

PostgreSQL

LibreOffice has a direct commuter for PostgreSQL databases, which is preinstalled. To ensure a secure connection, follow these brief instructions for the first steps after installing PostgreSQL.

Creating a user and a database

The following steps are necessary later installation using the package manager in OpenSUSE. You can assume similar ones in other operating systems.

ane) The postgres user must be assigned a countersign. This can be done using the operating system's utility.

ii) The Postgre server must exist launched by the administrator:
service postgresql start or rcpostgresql offset.

3) The postgres user logs in at the console with:
su postgres

4) An unprivileged database user, here called lotest, is created with a countersign:
createuser -P lotest

v) In order to permit the database user to connect to the database which is to be created, an entry in the var/lib/pqsql/information/pg_hba.conf file must exist changed. This file includes the methods used for identifying users at various levels. The method LibreOffice uses to communicate is the "password" method and not the "ident" method as set out initially in the file.

vi) The organisation user "postgres" logs in with "psql":
psql -d template1 -U postgres

vii) The system user creates the "libretest" database:
CREATE DATABASE libretest;

Direct connection to Base of operations

Choose the postgres entry in Pace i of the wizard. To make the connection, give the database name (dbname) and the host. Under some circumstances, it is necessary to requite the fully qualified hostname including the domain name.

Figure 12: Setting up a directly connection

The user hallmark (Step 3) is exactly the same every bit for MySQL.

The Save As dialog (Figure xiii) shows the diverse schema in PostgreSQL. The only one that can actually be saved to is the public schema, unless extended rights have been granted to this user.

Note

If tables from the internal HSQLDB database are copied to PostgreSQL, the Import Wizard uses the simple table names from HSQLDB, for instance Table1. However, importing under this name volition lead to errors. Instead, the schema name must be prepended, so that Table1 becomes public.Table1.

Figure 13: Saving to the public schema

When tables are existence created, Base might suggest data types which the current PostgreSQL installation cannot handle. For example, by default text fields are given the field type Text[character_data]. PostgreSQL cannot process this field type. Changing the type to Text[varchar] solves the problem.

The various schema appear in the table view of PostgreSQL (Figure xiv). In the public schema yous can see a table chosen Name.

If the database is being opened for the starting time time, you will see a neat many tables in the Information schema surface area. These tables, like those in the pg_catalog expanse, cannot exist read or written to past the ordinary user. These different areas are chosen schema in PostgreSQL. Users create new tables in the public schema.

Effigy xiv: Tabular array view of Postgre_SQL in LibreOffice Base

dBase databases

dBase databases have a format where all data is contained in divide, previously initialized tables. Links betwixt the tables must be made in program code. Relations are not supported.

The dBase format is especially suitable for the substitution and extensive editing of data. In addition, spreadsheet calculations can straight access dBase tables.

dBase has no fashion to prevent the deletion of, for example, media in a library database that continue to exist referenced in the media loans table.

Note

Now the simply dBase databases that are recognized are those independent in files with the ending *.dbf in lower-case letters. Other endings such every bit *.DBF are not recognized. (Problems 46180)

Figure 14: Setting upwards a connectedness to dBase files

The connexion is made to a specific folder. All *.dbf files in this folder volition be included and shown in the *.odb database and can be linked together using queries.

Tables in dBase have no master key. They tin can in principle exist described as corresponding to the worksheets in Calc.

Effigy 15: Tables in a dBase file

Tables can be created and volition then be copied as new files in the binder previously selected.

The number of different field types for a new dBase table is clearly less than when the internal HSQLDB format is used. In the post-obit effigy there are nonetheless some field types with the aforementioned type name.

Figure sixteen: Field types for a new dBase table

Base takes over the coding of the operating system. Therefore old dBase files easily develop errors when special characters are imported. The grapheme set up can exist corrected subsequently using Edit > Database > Properties > Advanced Settings (Figure 17).

Figure 17: Correcting the grapheme set

Note

The Import Wizard for dBase has problems with automated recognition of numeric field types and Yes/No fields (Bug 53027). This may require you lot to brand subsequent corrections.

Spreadsheets

Calc or Excel spreadsheets can likewise exist used as the table source for databases. If, however, a Calc spreadsheet is used, no editing of the table data is possible. If the Calc document is still open up, it volition be write-protected.

The only questions to be answered are the location of the spreadsheet file and whether or not it is password protected. Base and then opens the spreadsheet and includes all worksheets in the certificate. The first row is used for the field names and the worksheet names become the table names.

Relationships between spreadsheets cannot be ready up in Base, equally Calc is not suitable for use as a relational database.

Figure 18: Setting upwards a connection to a spreadsheet

Thunderbird address book

The Wizard will automatically seek a connection to an address book, for example as used in Thunderbird. The Magician will prompt for the location of the ODB file that will be produced.

Figure 19: Tables in a Thunderbird accost book

All tables are shown. As with Calc spreadsheets, the tables are non editable. Base of operations uses the table data simply for queries and mail merge applications.

Note

But the Personal addresses file is read as an address volume in Linux and macOS. Collected groups are currently simply visible as part of the Personal addresses. Groups from Nerveless addresses are not shown.

Text tables

In Base of operations y'all can create a complete database past accessing text tables. Text tables can as well exist accessed from inside an internal database.

Text tables inside an internal HSQLDB database

The *.csv format is a common commutation format between databases. Records are stored in a class that can be read and modified by a simple text editor. Individual fields are separated past commas. If a field contains text that includes a comma, the text fields are enclosed in double quotes. Each new tape begins with a new line.

For example, the contents of an address book which is in a format not supported by any Base commuter can either be imported via a *.csv file (using Calc as an intermediary if necessary) or the file is directly imported into the database as a text table. To be editable there, the *.csv file must include a field with unique values that can serve equally a principal key.

...

Figure 20: Creating a text table using Tools > SQL

A text table cannot be created using the graphical user interface. Instead you must use Tools > SQL to create a text table (meet Effigy 20). The fields in the text table must correspond in type and order to those that the text table makes available. For example the ID field must incorporate positive integers and the Birthday field must contain engagement values in the grade Year – Month – Day.

The table is non directly visible in the user interface. If another enlargement is to follow, use View > Refresh tables to make the tables available. The tabular array symbol indicates that this is not a "normal" database table.

The table is opened for editing and the primary cardinal field is changed to an automatically incrementing field.

Figure 21: Editing a text table

At present we must make a connection to an external text table using using Tools > SQL. The text table lies in the aforementioned folder every bit the database itself.

SET TABLE "Addresses" SOURCE "Addresses.csv;encoding=UTF-8";

Tip

The encoding=UTF-8 term works in many systems. In some cases you demand to use ansi instead of UTF-8.

Afterward this the text table is available for input in the normal manner. But the following points should exist noted:

• Text tables can be opened and edited simultaneously by external text programs. Loss of data cannot be excluded in these circumstances.

• Changes in records already written lead to the corresponding line in the original file being cleared and the new version added at the finish of the table. The tabular array view shown higher up presents four written lines with correctly sorted ID numbers. In the original file, the second record has been altered, leading to the following record sequence past ID: i, bare line, 3, 4, 2.

When connecting to a text file, the following parameters are bachelor.

Fix TABLE "Addresses" SOURCE "Addresses.csv;ignore_first=faux;all_quoted=true;encoding=UTF-viii";

"ignore_first=truthful" would mean that the first line is not read in. This makes sense if the line contains simply field headings. The internal default for HSQLDB is false.

By default, HSQLDB text fields are only placed in double quotes if they contain an internal comma, since the comma is the default field separator. If every field is to exist quoted, set all_quoted=truthful.

For further parameters, run across http://world wide web.hsqldb.org/md/1.8/guide/guide.html#set_table_source-department

SET Tabular array "Addresses" READONLY TRUE;

prevents anything from being written into the table. The tabular array is then available read-only like an address book from a post program. Setting write protection separately is only necessary when a primary central has been set for the table.

Text tables equally a basis for a standalone database

Every bit in the previous example, *.csv files are used as a data source. Using Base of operations, a binder containing the *.csv files is embedded as a data folder.

Nosotros brainstorm past connecting to an existing database. Here the format Text has been selected.

The path to the text files is looked up. In the folder, all files of the specified type will be listed later. For *.csv files, choose the second option.

At this stage you tin can already see a clear alarm that the files will exist opened read-only with no write admission.

In the Table view, all the tables in the specified binder are shown by their filenames but without the filename suffix. The tasks for creating tables are non active. The tables themselves can be read simply non written.

Access to the tables by queries is also limited to i table at a time and without the use of functions.

When such a database is used to search briefly in a *.csv file for records or to import a *.csv file into another database by using the re-create function, it has fulfilled its purpose. The corresponding *.csv file is just moved into the specified folder and can be directly searched or copied. Such text databases are not suitable for more general utilise.

Firebird

At some time, the old HSQLDB version used for internal databases volition be replaced by an internal Firebird database. If you wish to encounter what Firebird can offer, here is the process for connecting to an external Firebird database.

Equally the documentation is not as comprehensive every bit for MySQL or PostgreSQL, hither are the most important steps in installation.

Creating a user and a database

Linux provides Firebird packages through its package managers. After installation, the server must exist fix. The following steps under OpenSUSE 12.iii link to a operation Firebird database:

1) Sysdba is the username for the administrative account. The default password is masterkey. This should be changed in a production surroundings.

2) To change the password in a terminal:
gsec -user sysdba -pass masterkey -mo sysdba -pw newpassword

three) To go to a functioning database, you need administrator rights on the estimator. The organization user firebird must have a password assigned to information technology.

iv) The firebird user logs on at the console:
su firebird

5) A new user is created, shown here with the original default password for sdba:
gsec -user sysdba -pass masterkey -add lotest -prisoner of war libre

This creates a new user with the name lotest and the password libre.

6) Next, even so equally superuser, create a database for the user. For this we use the auxiliary programme isql-fb.
isql-fb

You lot encounter the following message:
Utilize CONNECT or CREATE DATABASE to specify a database
followed directly by the SQL> prompt. The appropriate entries are:

SQL> CREATE DATABASE 'libretest.fdb'
CON> user 'lotest' password 'libre';

If these tasks are carried out as the system ambassador root, the database will not exist assigned to the right user when networked. The database must exist entered as the firebird user in the firebird group. Otherwise the connection volition not work subsequently.

Direct connexion to Firebird

Choose the Firebird entry in Step 1 of the magician. To make the connectedness, give the database proper name (dbname) and the host. Nether some circumstances, information technology is necessary to give the fully qualified hostname including the domain proper name.

Figure 22: Setting upwardly a directly connection

The user hallmark (Step iii) is exactly the same every bit for MySQL.

When tables are being created, Base might propose data types which the current Firebird installation cannot handle.

Connecting to Firebird via JDBC

Beginning you will need to embed the Jar-archive into LibreOffice. Notwithstanding, at that place is no archive called firebird-*.jar. The electric current JDBC driver can be found at http://www.firebirdsql.org/en/jdbc-commuter/. The driver proper noun begins with Jaybird...

Re-create the archive jaybird-total-2.ii.viii.jar (or whatever the electric current version is) out of the zip file and place it either in the Java path of the installation or import information technology directly into LibreOffice every bit an archive. Come across the corresponding section on MySQL.

The following parameters are important when installing JDBC:

JDBC URL – jdbc:firebirdsql://host[:port]/<path_or_alias>

Driver bus proper noun – org.firebirdsql.jdbc.FBDriver

In the above example this becomes the URL

jdbc:firebirdsql://localhost/libretest.fdb?charSet=UTF-8

If you lot practice not specify the character set, you volition become this error:

When creating tables, take care that the formatting of respective fields (field backdrop) agrees from the outset. Otherwise LibreOffice will set the default format for all numeric values, which, strangely plenty, is a currency.

Subsequent alteration of field properties in tables are not possible but yous can enlarge the table or delete fields.

Firebird connection using ODBC

Get-go yous must download the appropriate ODBC driver from http://www.firebirdsql.org/en/odbc-driver/. This driver is usually a uncomplicated file called libOdbcFb.so. This file is usually placed in a more often than not accessible path in the system. Information technology must be executable.

In the odbcinst.ini and odbc.ini files, which are necessary to the system, the following entries are required:

odbinst.ini

[Firebird]

Description = Firebird ODBC commuter

Driver64 = /usr/lib64/libOdbcFb.so

odbc.ini

[Firebird-libretest]

Description = Firebird database libreoffice examination

Driver = Firebird

Dbname = localhost:/srv/firebird/libretest.fdb

SensitiveIdentifier = Yeah

In a Linux system, these ii files are in the /etc/unixODBC binder. The variable SensitiveIdentifier must e'er be set to "Yeah" so that entry into tables works when names and field definitions are non in upper example.

Connecting a database to an external HSQLDB

Tip

If for whatever reason the database has been opened simply there is no access to the tables, yous can employ Tools > SQL to enter the SHUTDOWN SCRIPT command. The database can so be closed and reopened. This will non work if at that place has already been an error message Error in script file.

The records in the database are stored in the *.odb file in the subfolder database. Here there are two files called information and backup. If the data file is defective, it can be restored from backup. To exercise this, you must first edit the backdrop file, which is also in the database folder. Information technology contains a line modified=no. Change this to modified=yep. That informs the system that the database was not correctly closed. When y'all restart, the compressed backup file will regenerate the data file.

Internal HSQLDB is indistinguishable from the external variant. If, as in the post-obit description, the initial access to the database is from the outside, no server function is necessary. Y'all but demand the archive program which is supplied with LibreOffice. Yous will find it on the path under /program/classes/hsqldb.jar. The use of this archive is the safest solution, as you and then get no version problems.

External HSQLDB is freely available for download at http://hsqldb.org/. When the database is installed, the post-obit steps must exist performed in LibreOffice:

If the database driver does not lie on the Coffee-Runtime path, it must be entered equally a Class Path under Tools > Options > Advanced.

The connection to the external database uses JDBC. The database file should be stored in a detail folder. This folder can be freely chosen. In the post-obit case information technology is in the home folder. The rest of the folder path and the proper noun of the database are non given here.

Information technology is of import, if data in the database are to exist written using the GUI, that adjacent to the database proper name the words ";default_schema=true" are written. This can exist extended with ";shutdown=truthful", then that the database is shut down by LibreOffice.

So:

jdbc:hsqldb:file:/abode/PathToDatabase/Databasename;default_schema=true;shutdown=truthful

In the folder y'all will find the files:

Databasename.backup

Databasename.information

Databasename.properties

Databasename.script

Databasename.log

The next step is to give the default user, if goose egg in the HSQLDB configuration is to be inverse:

This creates the connection and the database becomes accessible.

Circumspection

If an external database is edited with a version of HSQLDB 2.x, it tin no longer be converted into an internal database under LibreOffice. This is because of additional functions that are not present in version ane.8.x. This terminates the invocation in the instance of version 1.8.x while the script file of the database is beingness read in.

In the same fashion, an external database that has once been edited with a version of the 2d serial cannot afterwards exist edited with version 1.8.10, which is compatible with LibreOffice.

Parallel installation of internal and external HSQLDB databases

Including the external hsqldb.jar file in the form path tin, in some versions, forbid internal databases from being opened. Base gets stuck considering the drivers have the same name, and it tries to use the external driver for the internal database. This works the first time effectually. The second fourth dimension, yous go a message that the database cannot be opened as it was written with a newer version of HSQLDB.

To solve this problem, practise not place the hsqldb.jar file, which is required for external databases, on LibreOffice's grade path. Instead the form path for this file should be set by a macro, as shown below.

SUB Start
Const cPath = "/home/robby/public_html/hsqldb_test/hsqldb.jar"
DIM oDataSource Every bit OBJECT
DIM oSettings AS OBJECT
DIM sURL AS Cord
sURL = ConvertToURL ( cPath )
oDataSource = ThisComponent . DataSource
oSettings = oDataSource . Settings
oSettings . JavaDriverClassPath = sURL
Cease SUB

Here the hsqldb.jar file in a Linux system is on the path shown above. This path is passed to the database just opened as its driver file.

This macro is called merely once later on the *.odb file has been opened. It writes the corresponding connection to the Java class into the content.xml file in the *.odb archive:

< db:data-source-settings>
<db:data-source-setting

db:information-source-setting-is-list= "false"

db:information-source-setting-proper noun= "JavaDriverClass"

db:data-source-setting-blazon= "string" >

<db:data-source-setting-value>

org.hsqldb.jdbcDriver

</db:data-source-setting-value>

</db:data-source-setting>

<db:data-source-setting

db:data-source-setting-is-list= "false"

db:data-source-setting-proper noun= "JavaDriverClassPath"

db:data-source-setting-type= "string" >

<db:data-source-setting-value>

</db:information-source-setting-value>

</db:data-source-setting>

</db:data-source-settings>

Finally the Path could be written directly into content.xml without using a macro. However this method is not very comfortable for an ordinary user.

The simply other known method requires installation of two versions of LibreOffice in parallel and modification of the bootstrap file for i of them. This method is also not very comfortable for an ordinary user.

Changing the database connection to external HSQLDB

Internal HSQL databases accept the disadvantage that data storage involves a compressed archive. Only on pinch are all the data finally written. This can more easily lead to data loss than when working with an external database. The following section shows the steps necessary to successfully change an existing database from an *.odb annal to an external version in HSQL.

From a copy of the existing database, extract the database folder. Copy the contents into an arbitrary folder as described to a higher place. Prefix the database name to the resultant filenames:

Databasename.backup

Databasename.data

Databasename.properties

Databasename.script

Databasename.log

At present the content.xml file must be extracted from the *.odb archive. Utilize whatsoever simple text editor to find the post-obit lines:

<db:connection-data><db:connexion-resources xlink:href="sdbc:embedded:hsqldb"/><db:login db:is-password-required="false"/></db:connection-information><db:commuter-settings/>

These lines must exist replaced with a connexion to an external database, in this case a connection to a database with the proper name Marriage, in the hsqldb_data binder.

<db:connection-information><db:connection-resource xlink:href="jdbc:hsqldb:file:/home/robby/documents/databases/hsqldb_data/Marriage;default_schema=true"/><db:login db:user-proper name="sa" db:is-password-required="imitation"/></db:connection-data><db:commuter-settings db:java-driver-class="org.hsqldb.jdbcDriver"/>

If, every bit described higher up, the basic configuration of HSQLDB was not damaged, the username and the optional password must also agree.

After changing the lawmaking, content.xml must be put back into the *.odb archive. The database binder in the archive is now surplus to requirements. The data will in the future exist accessed through the external database.

Irresolute the database connection for multi-user admission

For multi-user access, HSQLDB must be made bachelor over a server. How the installation of the server is carried out varies depending on your operating system. For OpenSuSE, it is only necessary to download the appropriate package and to beginning the server centrally using YAST (runlevel setting). Users of other operating systems and other Linux distributions can likely find suitable communication on the Internet.

The home binder on the server (in SuSE, /var/lib/hsqldb) contains, amongst other things, a folder chosen data, in which the database is to exist filed, and a file chosen server.backdrop, which controls the admission to the databases in this folder.

The following lines reproduce the complete contents of this file on an instance calculator. It controls access to ii databases, namely the original default database (which tin can be used every bit a new database) and the database that was extracted from the *.odb file.

# Hsqldb Server cfg file.

# See the Avant-garde Topics chapter of the Hsqldb User Guide.

server.database.0 file:data/db0

server.dbname.0 firstdb

server.urlid.0 db0-url

server.database.1 file:data/union

server.dbname.1 union

server.urlid.one marriage-url

server.silent true

server.trace false

server.port 9001

server.no_system_exit true

The database.0 is addressed with the name firstdb, although the individual files in the data binder brainstorm with db0. Another database was added as database.one. Here the database name and file begin identically.

The two databases are addressed in the following way:

jdbc:hsqldb:hsql://localhost/firstdb;default_schema=true
username sa
countersign

jdbc:hsqldb:hsql://localhost/matrimony;default_schema=true
username sa
countersign

The suffix ;default_schema=true to the URL, which is necessary for write admission using the Base GUI, is permanently included.

If you actually demand to work on the server, yous volition want to consider if the database needs to be countersign-protected for security reasons.

Now you can connect to the server using LibreOffice.

With this access data, the server can exist loaded on its own figurer. On a network with other computers, you must requite either the host name or the IP accost to the server.

Example: A computer has the IP 192.168.0.20 and is known on the network by the name lin_serv. Now suppose there is another computer to exist entered for connection to the database:

jdbc:hsqldb:hsql://192.168.0.20/spousal relationship;default_schema=truthful

or:

jdbc:hsqldb:hsql://lin_serv/marriage;default_schema=true

The database is now connected and we can write into information technology. Chop-chop, nevertheless, an additional problem appears. The previously automatically generated values are suddenly no longer incremented. For this purpose we demand an additional setting.

Motorcar-incrementing values with external HSQLDB

To use autovalues, different procedures for table configuration are needed according to the version of LibreOffice. Common to all of them is the following entry under Edit > Database > Advance settings:

Adding GENERATED By DEFAULT Every bit IDENTITY(START WITH 0) causes the function of the automatically incrementing values for the primary key to be fix. The GUI in LibreOffice takes up this command, only unfortunately prefixes the argument with Non Nada, so that the control sequence for HSQLDB is not readable. Here y'all must ensure that HSQLDB is sent the to a higher place command so that the corresponding field will contain the master key.

In all versions of LO, reading out the latest value and incrementing to the next uses the CALL IDENTITY() command. This allows you to create a mini-*.odb file, examination information technology thoroughly, then simply remove the tables.

All the queries, forms, and reports will notwithstanding be usable, as the database for the *.odb file is all the same accessed in the aforementioned way, and the specific SQL commands tin exist used for the (real) external HSQLDB database.

Subsequent editing of connection properties

Especially with connections to external databases, a connection may not function quite as desired. The character set may not be right, or subforms may not role without errors, or something in the underlying parameters needs to be changed.

The post-obit screenshots illustrate how yous might alter the connectedness parameters for an external PostgreSQL database.

Under Edit > Database, you will find the choices Properties, Connection type, and Advanced settings. Choose Properties.

If the name of the data source has changed, it can be changed here. For an ODBC connection, the proper noun past which the database is called upwards is set out in the odbc.ini file. The proper name is usually not quite the aforementioned as the actual database proper name in PostgreSQL.

Is in that location a problem with the grapheme set? These problems can be solved using the second tab of the dialog.

An boosted special configuration of the driver is possible if a parameter should demand to be implemented that is non currently in the odbc.ini file.

If the connectedness blazon is selected, the whole contact with the information source can be altered. The following steps are similar to those in the Database Wizard from step 2 onward. So, for case, you can modify from ODBC to JDBC or a direct connection with LibreOffice's internal driver. This is useful if you are doing preliminary tests to determine which connection method is most suitable for a project.

According to the database system, there are different commands to create automatically incrementing values. If y'all need to practice something of this sort which is not currently possible with this commuter, and so you will need to do it by hand. This will require a control for creating an AutoValue field and besides 1 to query the near recent value.

The special settings accessible through Tools > Database > Advanced Settings bear on the interaction of external databases with Base in various ways. Some settings are grayed out as they cannot exist inverse in the underlying database. In the higher up example Replace named parameters with ? has been checked. It has been shown that otherwise the transmission of values from a chief form to a subform in PostgreSQL does non work. Only with this setting does the form structure in Chapter 4, Forms, work correctly.

Source: https://books.libreoffice.org/en/BG72/BG7202-CreatingADatabase.html

Posted by: biggsshoulmons.blogspot.com

Share this post