Please show a screenshot of the status bar of main window of ODB-file. So we could see which connector you are using.
Sorry, I do not have “Status bar” - it is grayed out in the View top menu selection.
I’m also showing a screenshot when opened both LibreOffice created ODB file using wizard suggested by Wanderer in this topic (and saved in a different name since it is missing forms, etc.) and the originl ODB file. The screenshot was taken with Edit / Database / Connection Type… window opened. Left side is the original ODB, right side is the wizard ODB. Note the difference in Database type: drop down options. This is why I could not mimic the settings.
Hope this additional info helps in debugging this situation; and thanks - jankom
@jankom : Isn’t a screenshot of the statusbar of ODB-file, but it also show how do you want to connect: JDBC-connection. And: The driver, which couldn’t be loaded, is the JRE of Ubuntu.
Try to create a internal HSQLDB database. Could you create tables there or do you get the same error?
You have added a MySQL-connector in class path. Do you have full rights to this connector? I have installed such connectors in my home directory.
Left screenshot I see you have chosen JDBC connection. So you should also chose MySQL (JDBC) on right side. MySQL Connector will work faster for big tables, but if you have used JDBC before I won’t change it.
@RobertG: as far as MySQL connector jar file it is in my home tree, and others have read access to it.
MySQL (JDBC) connection to the wizard ODB does not work:
Obviously the MySQL Connector is the wizard connection without Java, a built-in LibreOffice feature not available in OpenOffice.
So the problem is with JRE? I notice that in my Gentoo desktop the JRE is openjdk-bin-17 and the Desktop Mint has Ubuntu “java-11-openjdk”. Since openjdk-17 is available from Synaptic I tried it, but same error.
Sorry, I don’t understand “internal HSQLDB database”.
Btw the largest table in my database is about 13k rows and 12 columns, slowly growing.
One other thing: “View”-s do show up in the Tables section of the wizard ODB file, which is a good news. Worst comes to worst I can text copy the queries from the real ODB to the wizard ODB. Copying forms already works.
Still, I’d like to be able to connect to any database with JDBC.
@jankom : Try the wizard for creating a new database - no connection to an existing database. Chose internal HSQLDB database. You don’t need to register the database. Save the database file and try to change to the tables folder. Could you create a table to this database?
@RobertG: Could not connect. Interestingly, when I try to connect from Gentoo OpenOffice to the wizard ODB (not the just created test.odb) I get the same error message: “No SDBC driver…”
On the other hand I can open the test.odb from Gentoo OpenOffice, I can create a table in it, and later connect to it again from Gentoo, but not from Mint LibreOffice. The MySQL and ODB files are on the Linux Mint laptop, and I access them via home network with IP 192.168.x.x on both machines. The wizard ODB still connects on the Mint laptop and I can see the tables of my original database un the wizard ODB.
Strange…
jankom
For understanding: You created the embedded database. Please save the database in your home folder. In network folder you will get more problems, because the database will be opened in your temporary folder and will be packed again when closing in the folder the odb-file exists. So test it with this database in your home-folder of Gentoo and in your home-folder of Linux Mint.
Did you really install all packages, which are available for LibreOffice?
Sorry, I would like to understand:
I created test.odb as a LibreOffice ODB file. It is saved in my Linux Mint laptop home. After creation I could not connect. I still cannot connect from Linux Mint laptop LibreOffice base. The file is located in Linux Mint laptop, in my /home/myself/ directory tree. When I open the file in my Linux Mint laptop LibreOffice base program the Edit / Database menu item shows only Advanced Settings… choice. The Properties… and Connection Type… choices are grayed out.
Yes, I did a full installation of LibreOffice in my Linux Mint laptop with Synaptic.
As far as “network folder” - I don’t know. The Gentoo desktop machine accesses the test.odb file though an sfs mounted file system connected to my laptop home folder (sshfs user@192.168.x.x:/home /home), and after opening it with OpenOffice it is connected to the MySQL database located on my Linux Mint laptop /var directory. There the Edit / Database menu shows the same grayed out choices except the Advanced Settings as the laptop machine.
My simple objective is to be able to work on my original ODB file from either my laptop or desktop and use the macro in the ods file for Data Range Refresh. I really don’t care if it is Open Office or Libre Office as long as it is not non-linux. Presently I can only work on that ODB file on my Gentoo desktop with OpenOffice - but no macro, and I can live with that…
jankom
jankom
and thank you for staying with this issue - jankom
Please don’t mix connection for MySQL with default database HSQLDB.
Beispiel_Sport_Hsqldb.odb (33.4 KB)
Beispiel_Sport_Firebird.odb (35.4 KB)
I have added 2 files. First connects to internal HSQLDB, second connects to internal Firebird.
Save both in home directory of Linux Mint.
Start the one for HSQLDB.
Go to tables pane.
Post the error.
Do the same with the one for Firebird.
You could also try it with Gentoo, but you are working with OpenOffice in Gentoo. So Firebird wont work by default, because it isn’t supported by OpenOffice.
If we get connection to HSQLDB working we will try the next step for getting the connection to MySQL.
Yes, I do realize now that test.odb (or your Beispiel_Sport_Hsqldb.odb) is a special ODB file that has the database embedded in it. I went to MySQL console, and SHOW DATABASES; command does not list “test” as a database, but does list my “foo” database that is the subject of this question.
Results with your two files:
_Hsqldb.odb
Mint LibreOfficew has the same error:
Gentoo OpenOffice can open it OK,
_Firebird.odb
Mint LibreOffice can open it OK
Gentoo OpenOffice has the same error as the LibreOffice error with HSQLDB
as expected, because as you said OpenOffice does not support Firebird (I don’t know what that is, but that’s besides the question).
The strange thing is the SDBC driver, whatever that is.
Thanks again, - jankom
So let us see a little bit more:
Open the database file for internal Firebird in Linux Mint. Firebird is a database, which doesn’t need Java.
Go to “Reports” and start the report “Teilnehmerliste”. Error or does it work well?
Go to “Queries” and start “Use Wizard to create a Query”. Error or does the wizard open?
No problem, no error, Report shows, wizard opens.
So Java is running well on Mint. Seems Mint doesn’t install HSQLDB, but installs Firebird, which should be the default in future of LO.
Now: Were did you save the connector for MySQL-JDBC-connection? And: Is it really MySQL or is it MariaDB? Best will be to save this connector in the same home directory the *.odb-file has been saved.
Yes. I did some research and learned that HSQLDB is really another database written in Java, and since it worked for your sample file my JRE is OK. Interestingly, Synaptic shows that libhsqldb-1.8 was installed. Now I installed libhsqldb-2.6, but still cannot connect to your _Hsqldb.odb file.
I copied the mysql-connector-java-8.0.28.jar file (2476480 bytes) to the same place where the foo.odb (really penztar_base.odb) is located, but no change.
Thanks for your patience and persistence - jankom
Please show
Edit → Database → Properties → Additional Settings. Special the string in MySQL JDBC driver class. Should be com.mysql.cj.jdbc.Driver for your driver.
Jar-file should be added in Tools → Options → Advanced → Class Path
Bingo! That was it: “com.mysql.cj.jdbc.Driver”.
The jar was already in correct place, and I put the path back where I had it originally (still in the home tree).
Also, just to make sure I rebooted the machine and it still worked. The reason for reboot is because recently, when I replaced OpenOffice in my Mint machine and installed LibreOffice to solve the macro problem, initially connection succeeded, but after reboot it failed due to some certificate or key issues.
Now everything works as expected: I can access my foo.odb (for that matter any odb) file on the Mint machine from Mint LibreOffice or Gentoo OpenOffice. The MySQL server is on the Mint machine.
Thank you very much!
The question remains - why do we need the help of a super expert to make connection work? Is it because LibreOffice focuses on HSQLDB and Firebird, bells and whistles and wizards? I personally prefer the separate database server (MySQL or other), and use *Office as a gui front-end to make manipulation of a database (entering, updating, querying, summarizing, etc easier and simpler. In fact I have bash scripts to do the some stuff to the database.
Finally, how can I give you credit, “like” or something, and how to indicate to others on this board that this issue is finally solved; the “ask question” is correctly and entirely answered?
jankom
Set this one to solved with the post, which solves the problem.
Connection to any external databases will throw the same problem: You will need a special driver for this database. LibreOffice Base will support direct connections to PostgreSQL, MariaDB (MySQL) and Firebird as server databases. So if you chose a different driver (JDBC) you have to look for the connection settings, which aren’t part of LO. I have described the different connection strings in German Base Handbuch, but in English Base Guide I only see screenshots of a JDBC-connector, which won’t work with the old connection string. Seems somebody has made new screenshots and didn’t test the content …
Thanks again, understood - jankom