# LibreOffice Base [LibreOffice Base](https://www.libreoffice.org/discover/base/) is an open source RDBMS (relational database management system) frontend tool to create and manage various databases. ## Preparing the ODBC Connection First, make sure to prepare MariaDB Connector/ODBC as explained in [MariaDB Connector/ODBC](https://app.gitbook.com/s/CjGYMsT2MVP4nd3IyW2L/mariadb-connector-odbc). This includes * downloading [the latest MariaDB Connector/ODBC](https://mariadb.com/downloads/#connectors), * copying the shared library libmaodbc.so to /usr/lib/\[multi-arch], * installing the unixodbc, unixodbc-dev, openssh-client, odbcinst packages, and * creating a template file for the [ODBC driver](https://app.gitbook.com/s/CjGYMsT2MVP4nd3IyW2L/mariadb-connector-odbc/creating-a-data-source-with-mariadb-connectorodbc#configuring-mariadb-connectorodbc-as-a-unixodbc-driver-on-linux). * Install the ODBC driver from the template file by running: ```bash $ sudo odbcinst -i -d -f MariaDB_odbc_driver_template.ini odbcinst: Driver installed. Usage count increased to 1. Target directory is /etc ``` Verify the installation was successful in `/etc/odbcinst.ini` file (this path is obtained by the config info */-j*/ option, where drivers are installed in that predefined location). ```bash $ odbcinst -j unixODBC 2.3.6 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /home/anel/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8 $ cat /etc/odbcinst.ini [MariaDB ODBC 3.1 Driver] Description=MariaDB Connector/ODBC v.3.1 Driver=/usr/lib/x86_64-linux-gnu/libmaodbc.so UsageCount=1 ``` * Create a template file for the [Data Source Name (DSN)](https://app.gitbook.com/s/CjGYMsT2MVP4nd3IyW2L/mariadb-connector-odbc/creating-a-data-source-with-mariadb-connectorodbc#configuring-a-dsn-with-unixodbc-on-linux). A sample “MariaDB\_odbc\_data\_source\_template.ini” could be: | | | ------------------------------ | | \[MariaDB-server] | | Description=MariaDB server | | Driver=MariaDB ODBC 3.1 Driver | | SERVER=localhost | | USER=anel | | PASSWORD= | | DATABASE=test | | PORT=3306 | * Install data source: ```bash odbcinst -i -s -h -f MariaDB_odbc_data_source_template.ini ``` * Verify successful installation in the /.odbc.ini file ```bash $ cat ~/.odbc.ini [MariaDB-server] Description=MariaDB server Driver=MariaDB ODBC 3.1 Driver SERVER=MariaDB USER=anel PASSWORD= DATABASE=test PORT=3306 ``` * Verify successful installation also using the [isql](https://app.gitbook.com/s/CjGYMsT2MVP4nd3IyW2L/mariadb-connector-odbc/creating-a-data-source-with-mariadb-connectorodbc#verifying-a-dsn-configuration-with-unixodbc-on-linux) utility: ```bash $ isql MariaDB-server +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> show tables; +--------------------------------------------------------------------------+ | Tables_in_test | +--------------------------------------------------------------------------+ | Authors | | tbl_names | | webposts | | webusers | +--------------------------------------------------------------------------+ SQLRowCount returns 4 4 rows fetched ``` ## Start with LibreOffice Base Start Libreoffice Base from the terminal by running *lobase* (make sure to install the *libreoffice-base* package if needed). The default option is to create a new database, which is *HSQLDB*. In order to connect to a running MariaDB server, choose *“Connect to an existing database”* and choose *“ODBC”* driver as shown below: ![](https://2988006611-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FSsmexDFPv2xG2OTyO5yV%2Fuploads%2Fgit-blob-dd6556c85189e789471e5c3589180c4d215ad204%2Flibrebase_1.png?alt=media) After that, choose DSN (the one that we created in the previous step) and click *“Next”*: ![](https://2988006611-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FSsmexDFPv2xG2OTyO5yV%2Fuploads%2Fgit-blob-c41503061fc9d07d38e29e6f23ce03784b762e32%2Flibrebase_2.png?alt=media) Provide a user name (and password if needed) and again check the connection (with the *“Test Connection”* button) and click *“Next”*: ![](https://2988006611-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FSsmexDFPv2xG2OTyO5yV%2Fuploads%2Fgit-blob-55390f4a1b00cc76d9d55207ee11c107661af45f%2Flibrebase_3.png?alt=media) After that, we have options to register the database. Registration in this sense means that the database is viewable by other LibreOffice modules (like *LibreOffice Calc* and *LibreOffice Writer*). So this step is optional. In this example, we will save as *“fosdem21\_mariadb.odb”*. See [Using a Registered Database](#using-a-registered-database). ![](https://2988006611-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FSsmexDFPv2xG2OTyO5yV%2Fuploads%2Fgit-blob-71850fb3644aec205d3b883d5a30867a58f430db%2Flibrebase_4.png?alt=media) It opens the following window: ![](https://2988006611-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FSsmexDFPv2xG2OTyO5yV%2Fuploads%2Fgit-blob-74ae66adefbb26fe90f7a7bab50bfbde99995df6%2Flibrebase_5.png?alt=media) It consists of three panels: 1. “Database” window with the options, 2. "Tables", 3. "Queries", 4. "Forms", 5. "Reports". 6. "Tasks window (dependent on what is selected in the “Database” window). When “Tables” is selected, the options are: 7. "Create Table in Design View", 8. "Use Wizard to Create Table" and 9. "Create View". 10. "Tables" window - shows list of tables that are created. As we can see, there are system tables in the *“mysql”* database as well as *“test”* database. Let’s say we create a table using the REST API from JSON data from [posts](https://jsonplaceholder.typicode.com/posts), and another table using the same mechanism from [users](https://jsonplaceholder.typicode.com/users), and let’s call them *webposts* and *webusers*. In order to do so, we have to enable the **CONNECT** storage engine plugin and start with REST\_API. See more in the [CONNECT - Files Retrieved Using Rest Queries](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-files-retrieved-using-rest-queries) article. The queries we need to run in MariaDB are: ```sql CREATE TABLE webusers ENGINE=CONNECT TABLE_TYPE=JSON HTTP='http://jsonplaceholder.typicode.com/users'; CREATE TABLE webposts ENGINE=CONNECT TABLE_TYPE=JSON HTTP='http://jsonplaceholder.typicode.com/posts'; ``` The result in LibreOffice Base is as shown below: ![librebase\_6](https://2988006611-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FSsmexDFPv2xG2OTyO5yV%2Fuploads%2Fgit-blob-57afb0507eab694fe5679f0636067095248dc812%2Flibrebase_6.png?alt=media) Double clicking on the table opens a new window with the data displayed to inspect: ![](https://2988006611-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FSsmexDFPv2xG2OTyO5yV%2Fuploads%2Fgit-blob-ce9d1bf689d657a03362b7113c4b3b8437a9c9d2%2Flibrebase_7.png?alt=media) To create the table from the *“Tasks”* window, use the option *“Create Table in Design View”*, where one can specify specific field names and types as shown: ![](https://2988006611-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FSsmexDFPv2xG2OTyO5yV%2Fuploads%2Fgit-blob-e64fabd52cef9dae706064938e2d1b1beb94e24f%2Flibrebase_8.png?alt=media) From the “Tasks” window one can create a table using the option *“Use Wizard to Create Table”* to create some sample tables. One can fill the data in the existing table, or create and define the new table from the *LibreOffice Calc* module with simple copy-paste (in the *"Tasks"* window). ## Using a Registered Database Other modules can use the registered database, for example, open *"LibreOffice Calc"* and go to *"Tools"*, *"Options"* and you will see the *"odb"* file we registered when starting *"LibreOffice Base"*. *This page is licensed: CC BY-SA / Gnu FDL* {% @marketo/form formId="4316" %}