How to connect LibreOffice Base with MySQL

June

2023

linux

7 June 2023 15:45

In this article I talk about how to transmit information from MySQL to Linux to Libreoffice Base.

Preface

The Libreoffice package is included in the set of delivery of many, if not all Linux distributions. Also, it can be installed from the repository and Download in the form of Deb or RPM file.

Introduction

At work, about once a month, I made a reconciliation of the Excel source file with the MySQL database. Mysql has implemented communication with Libreoffice Base and the ODBC connector.

Libreoffice Base is an analogue of the MS Access program.
The database is recorded in the Linux operating system under a unique name available from Libreoffice Calc, Writer and Libreoffice Basic programs.

Between the Mysql and Libreoffice Base, I used \ ODBC connector. Later, it was possible to use the connection via JDBC.

Program connection scheme

DBMS MySQL <--> MySQL Connector ODBC/JDBC <-> Libreoffice Base <-> Libreoffice Calc

0. Installation MySQL and Libreoffice

MySQL installation is considered in the previous Article about MySQL.
How to install a LibreOFFICE package in Linux - see article Wiki.

1. Database

Database, as everyone knows, is designed to store information. As a rule, they are a set of interconnected tables (relational database). In a simple database, one of the tables is necessarily the main one (for example, a journal of operations or goods in a warehouse) - the rest are only reference books. In my case, one main table "Offers" was used, from which I made a selection using requests.

For testing purposes, I will show on an example, not on a real database. The composition of the fields is simplified for example.

Table Offers

ID - Integer (Unique Key Field)
Name - name
Description - Description
Price - price
Display - a sign (0 or -1 - do not show (hidden entry / sold), 1 or 2 - display (on sale / promotion)

Create Database Primer; Use Primer; Drop Table if Exists `offers`; Create Table `offers` ( `offer_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL, `description` varchar(4096) COLLATE utf8_unicode_ci DEFAULT NULL, `price` int(11) DEFAULT NULL, `display` int(11) DEFAULT '1', PRIMARY KEY (`offer_id`) ) Engine = Innodb Auto_increment = 546 Default Charset = UTF8 COLLATE = UTF8_UNICODE_CI; INSERT INTO `offers` VALUES (1,'AAAA', '', 10000, 1),(2,'BBBB', '', 80, 1),(3,'CCCCC', '', 555, 0),(4,'DD', '', 7, 1); File Dampa BD `primer` для MYSQL: [Primer.sql.zip](primer.sql.zip) To create a database, we perform this script on behalf of the database user `root`: `mysql -u root -p` `source primer.sql` ### 2. Methods of connecting to MySQL All software methods for connecting to MySQL are listed on the page [https://www.mysql.com/products/connector/](https://www.mysql.com/products/connector/) - есть фирменные драйверы ADO.NET, JDBC, ODBC, Python и разработанные сообществом PHP Drivers for MySQL и др. I checked the method of connecting to MySQL - ODBC or JDBC. The advantage was given by ODBC, because I have been using it for a long time. But you can install any way: * ODBC Connector for MySQL - see paragraph 2.1 * JDBC Connector for MySQL - see Paragraph 2.2 #### 2.1 Installation of Connector ODBC for MySQL We find out the number of the OS version: lsb_release -a Download the connector ODBC distribution for your version of Ubuntu or Debian. To do this, go to the next page: [https://dev.mysql.com/downloads/connector/odbc/](https://dev.mysql.com/downloads/connector/odbc/) Where we download the distribution of the ODBC connector for MySQL for our operating system: If the Linux Mint distribution, find out the code base (Package Base) you can find out [request](https://duckduckgo.com/?q=linux+mint+vera+package+base+ubuntu+lts+version&t=lm&atb=v297-1&ia=web) в поисковике. For example, for Limux Mint 21.1 - the code base Ubuntu is 22.04. That is, for Linux Mint 21.x you need to load ** ubuntu linux 22.04 (x86, 64-bit), Deb Package ** (MySQL-Connector-odbc_8.2.0-2.0 -ubuntu22.04_AMD64.deb) without debugging characters of 1.9 MB. On the next page, click "No thanks, Just Start My Download". Install the loaded package of the ODBC-connector (Deb file) with a double click of the mouse with dependencies (3 pcs.). Run the executable file from the terminal `myodbc-installer` - он выведет справку по использованию коннектора из командной строки: myodbc-installer He will display a certificate under the program:

 [error] not enunch arguments given
+---
 myodbc-installer v8.02.0000                                 
+---

 Description                                                          

    This program is for managing MySQL Connector/ODBC configuration   
    options at the command prompt. It can be used to register or      
    unregister an ODBC driver, and to create, edit, or remove DSN.    

    The program has been designed to work across all platforms        
    supported by Connector/ODBC, including MS Windows and             
    various Unix-like systems.                                        

    The program requires that unixODBC version 2.2.14 or newer        
    has already been installed on the system.                         
    UnixODBC can be downloaded from here:                             
    http://www.unixodbc.org/                                          

 Syntax                                                               

    myodbc-installer

ODBC.ini with a password in open form

[Primer] Driver = MySQL ODBC 8.2 Unicode Driver Database = Realty No_Schema = 1 Port = 3306 PWD = 55555 Server = Localhost Socket =/Var/Run/MySQLD/MySQLD.SOCK UID = ROOT PWD = 11111


The difference between the two options, as it is not difficult to guess - with a password you do not need to enter the user name and password when selecting data from the database (slightly reduces safety).

The presence of a string Socket =/Var/Run/MySQLD/MySQLD.SOCK V /etc/odbc.ini is mandatory!

2.2 Installation JDBC Connector for MySQL
1) You need to download the distribution of the JDBC connector from the Connector/J page

In the form of Deb or RPM file (depending on the OS), for its Linux distribution:
 Loading page Connector/J: 

https://dev.mysql.com/downloads/connector/j/ 
2) install the Deb or RPM file.
3. Creating a database Libreoffice Base and its connection to MySQL
In any Libreoffice program, call the menu item

Файл - Создать - База данных 

In the first step  "Welcome to the master of databases Libreoffice"  select  "Connect with the existing database"  -  MySQL 

 
3.1 Option: Using ODBC Connector
At the second step, select - using the Connector ODBC  "Connection via ODBC (Open Database Connectivity)" :

 
At the next, third step of the master, enter the name or select using the "Review ..." button, the name DSN, previously set for the Connector ODBC

 
На предпоследнем, четвертом шаге выполняем тестирование. Если имя пользователя и пароль сохранены в /etc/odbc.ini, вводить их не нужно.

 
If /etc/odbc.ini is correctly configured, there will be no errors. Otherwise, edit the ODBC.ini file and perform testing again.
Click the ** “Ready!” Button at the bottom of the wizard and save the database to the ODF database file in the user's local folder.
The database with external tables from MySQL will open. Tables can be opened and watching data:

 

LibreOffice BASE LIBREOFCE is created and works.

3.2 connection Libreoffice Base to MySQL using JDBC
JAR class for MySQL is installed in the/usr/share/java folder/with the name Mysql-Connector-Java-8.0.33.jar
We go to Libreoffice Calc - Service - Parameters - LibreOffice - Enlarged capabilities to add the way to the Java.SQL.DRIVER class: the "Way of the Class" button - Open the Computer Root in the address - indicate the path /usr/share/java/Mysql-connector-java-8.0.33.jar

In (Tools - Options -Libreoffice - Advanced). Glachka  "Use the Java virtual machine"  should be turned on.

Creating a database in Libreoffice Base with a connection through JDBC
File - Create - database

Step 1 is the same for ODBC and JDBC - "Connect with the existing MySQL database

 
At step 2, select JDBC:

 
At step 3, you need to specify the parameters: the name of the database, server and the port Mysql:

Primer
Localhost
3306

In the "Class" field, we correct org.mariadb.jdbc.driver on  com.mysql.cj.jdbc.driver  

Attention: Class  java.sql.driver  indicated in some manuals, use the name "com.mysql.cj.jdbc.driver" 

Click the "Class Test" - the driver is successfully loaded.


  
 
At step 4, indicate the user name, you need a password and click the "Test connection"

 
Complete with the maintenance of the Libreoffice database to a hard drive file.
If the "Java.sql.driver class class" Class occurs cannot be uploaded ./connectivity/drivers/jdbc/jconnection.cxx:685 "

or "Com.mysql.jdbc.driver 'cannot be).

Solution:  Change Java  to Private Build 1.8, restart Libreoffice:



The name of the class in step 3, as indicated above, should be Com.Mysql.cj.jdbc.driver

and the class file in the service-parameters-Libreoffice-advanced opportunities to add the way to the class Java.sql.driver: the path to MySQL-ConNNECTOR-8.0.33.JAR or MySQL-CONNECTOR-J-8.0.33.JAR must be /USR/SHARE/JAVA/MYSQL-CONNECTOR-JAVA-8.0.33.JAR or MySQL-CONNECTOR-J-8.0.33.JAR
Note: Java class in JAR format is installed when it is launched by the rector Deb file, but it can also be downloaded from the page https://dev.mysql.com/downloads/connector/j/ - Platform independent - архив tat.gz в нём mysql-connector-j-8.0.33.jar First we remove to the home folder  without ways , then move the jar in/usr/share/java/.
The result with JDBC
The result differs from the ODBC version - all the database is visible, including systemic ones (because connection with the user Root). Ignoring the database name in my opinion is the disadvantage of the JDBC connector compared to connecting via ODBC.
 
MySQL tables through JDBC can also be opened in Libreoffice Base for viewing or editing.

. Do not forget to save the Libreoffice Base database to the *. ODB file.
Conclusion
The article discusses two methods of connecting from  Libreoffice Base  to the database  MySQL  -  ODBC  and  JDBC . Both methods are workers.
The next article
*How to execute a request from Libreoffice Calc to the MySQL database? 
                      
                                            
                                              


                        
                         Related publications
     
                                     
                
                 
                                            
                                            Troubleshooting L2TP/IPsec connection errors "Could not add IPsec connection" and "no acceptable traffic selectors found"
                    
                
            
                                             
                
                 
                                            
                                                Разбивка жесткого диска для установки Linux Mint на старый ПК с BIOS в режиме GPT
                    
                
            
                                             
                
                 
                                            
                                                Обновление Linux Mint 21.3 Virginia до Linux Mint 22.3 Zena
                    
                
            
                                             
                
                 
                                            
                                            Removing the Xanmod kernel from a Linux operating system
                    
                
            
                                             
                
                 
                                            
                                            Устранение отклонений при установке драйвера видеокарты nVidia