After installing LibreOffice on my Linux Mint laptop and registering my foo.odb I could not open a table or form, could not connect to database foo.
Blockquote
The connection to the data source “foo” could not be established.
Blockquote
With the informations you provided all I can do is directing you to the documentation. Especially Chapter 2 for a start. (I guess you made sure MySQL is running, wherever your database may be hosted…)
https://books.libreoffice.org/en/BG73/BG73.html
Thank you Wanderer. Chapter 2 worked - saved it as foo-1.odb. It even worked after rebooting my laptop. Now I have to tweak this to make it work for the real foo.odb file that already has forms and queries.
I’ll report this as [CLOSED] maybe tomorrow if I succeed. I did not dare to save it at the end of wizard as the real foo.odb.
jankom
Progress report - original problem not solved. Yes, based on Chapter 2 I can connect, but only with a new odb file. My existing odb (I called for this post as foo.odb) has forms, queries. How can I use that file in LibreOffice and make it connect to the database used by foo,odb?
Additional info - I did not want to make the original “Ask…” posting confusing, but on another machine (Gentoo Linux) I use OpenOffice, and connection works through home private network. Now I want to use LibreOffice on my Linux Mint laptop, but cannot connect. This is the problem.
jankom
First things first: Create backups of both .odb-files!
.
One approach: Don’t repair the old .odb, but copy queries first, then forms and reports one by one to the new odb. (Mark one in the old file, select copy from context menu, select queries/ form or report tab on destination and paste there. I assume the tables are already in the MySQL-File available.
.
Or the other way round: Usually second menu from left has a last entry database. Check what is noted there at connection and properties of the database. The try to change the old .odb to resemble the new one. I’d start with the connection, if different, then properties.
.
But I never needed to go through this steps myself…
Thank you for staying with this problem. I’ll try the copy method. Will report back Saturday.
Cheers,
jankom
Unfortunately nothing worked.
- tables include Views but I cannot connect to MySQL. The Views only show up in the tables section of the odb file, they are not in the MySQL database.
- cannot copy queries because it wants to connect to MySQL.
- Yes, I could copy Forms.
- As far as duplicating connection setting is not working either.
Background: the old odb and the underlying MySQL database was created long time ago in Ubuntu LibreOffice. Laptops expired (hardware failures), now I use an HP laptop with Linux Mint. Also, I have a desktop with Linux Gentoo and OpenOffice. So I accessed the odb file via home network and it worked for me.
Recently I made some improvements on the spreadsheet file that uses some of the Views of the odb file. Macros did not work well, so I turned to this forum for help. Solution was provided, and the solution was to use LibreOffice, not OpenOffice.
OK, I went back to my Linux Mint laptop, installed LibreOffice, and connection worked somehow. After reboot connection no longer worked. This is why I opened up this topic.
I can continue to use my system (application) of odb and ods files on the Gentoo laptop with OpenOffice and no macro.
The bottom line: why is it that LibreOffice cannot connect to an odb file working with OpenOffice and vice versa (I cannot connect either with the LibreOffice created odb file in my OpenOffice)? I tried all sorts of jar connectors from Oracle and elswhere. No wizards or jar files worked in OpenOffice or LibreOffice.
Thanks anyway - jankom
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?