Comments - LibreOffice Base

Hi Robert,

no problem, let's try to figure out together.

You said that MariaDB appears to be functioning meaning you have installed MariaDB correctl. The same goes for lobase. OK. You said above that "I have access to my database using ODBC through a localhost".

I understood this statement that following instruction in this KB article you managed to connect your MariaDB server with lobase client. And this is the whole meaning of this article.

This article is not showing you how to use all lobase functions with database, it is not intended to be, but is not restricted to present even that.

Btw. I have created this article and have updated with new information and this article is the wiki page, meaning article may have bugs and also you, Robert, if you find a bug, you can edit it and/or add additional information you find important to be visible in article.

I have gone again through all procedure in this KB article and I can confirm that if anyone is following the steps will be able to successfully connect LibreOffice Base client to MariaDB server through ODBC connection, what is the main point of the KB article.

Also I have created new table from lobase "Task" window with auto-increment key column and can confirm that after saving the table, it is visible and working correctly.

Regarding the relationships, relation design is not supported in LibreOffice Base with this ODBC connection type based on this https://ask.libreoffice.org/t/base-the-database-does-not-support-relations/23527/2 where is said "ODBC is broken with a known bug and has to my knowledge not yet been fixed." So it has nothing to do with MariaDB, but with the way how LibreOffice Base handles the relationship with specific driver. And yes, I could get the view of relationship following first link I have shared with you, it is possible to see/edit relationship but not to save (and make changes to data schema), but again this is limitation to lobase in conjuction with ODBC driver and not MariaDB server itself.

See also https://ask.libreoffice.org/t/mysql-relationships-not-shown-till-exit-and-re-enter-base/35111/2

Maybe Relation Design can be used with JDBC https://ask.libreoffice.org/t/how-do-i-set-up-a-jdbc-connector-for-mariadb-or-mysql-in-base/21360/14 but I don't know haven't tried.

Why is there only ODBC connector? I needed to verify in my settings does it work and wanted to contribute through KB. Feel free to add JDBC connection KB :) and let me know does Relation Design works.

Kindly regards

Hi Anel,

Thank you for responding.

I removed ODBC connectors from my computer. Then, I worked on connecting through JDBC.

I was able to set up LOBase with a JDBC connector by downloading mariadb-java-client and mysql-connector-java using YAST (I am running a current version of Opensuse Tumbleweed) and then following this basic set of instructions http://www.giustetti.net/wiki/index.php?title=Connect_libreoffice_to_mariadb.

However, after I fumbled my way through to get relationships working, I found that I could not set my tables properly with auto-increment. I stopped there realizing that I may had solved my problem with ODBC.

I deleted my JDBC odb files, reinstalled both mariadb-connector-odbc and unixodbc using YAST. I did NOT install unixodbc-dev or odbcinst (odbcinst is part of unixodbc_and openssh was already installed. I followed instruction for ini files.

I opened LOBase and follow the path of -- Open LOBase -> connect to an existing database -> MySQL -> Connect using ODBC -> etc. This brought me back to the same problem of Relationships not working. (I already had created mariadb databases and input users from a command line.)

I then went in to edit each table to match the foreign key Field Types to the same Field Type as the primary keys. I had used Field Type Integer[MEDIUMINT] with 7 characters for my primary keys. With Primary and Foreign Key Type Fields matching, Relationships worked with the ODBC connector.

So, to have the instructions you presented on this page work, I had 3 problems that when resolved had tables, auto-increment and Relationships working. These may be common sense to you, but as I am starting out with mariadb, LOBase, and ODBC, this was a learning curve for me:

1. use only letters and numbers in database names - DO NOT use underscores. 2. when "connecting to an existing database", select MySQL and then at the next choice, chose "Connect using ODBC". 3. Ensure that Field Types and character lengths of your Primary and Foreign Keys match BEFORE attempting to create relationships.

Give this a try and see if it works for you. I will slowly begin to get forms running, then queries, and finally reports. I will stop with JDBC at this time, but note that Relationships were working with it but there were a few other kinks still to address.

Thank you for your help