Created a database in MySQL, unable to connect via SBDC to LO Base

English
, , , ,
#1
  • Mac desktop (64 bit)
  • OS High Sierra 10.13.6
  • LO 6.0.6.2
  • Java Runtime Environment 10.0.2 installed and
    currently in use (LO Preferences >
    Advanced)

This is my first time creating a MySQL database and connecting it to LO.
I successfully created the database via the mysql command line: “Query OK, 1 row affected”.
I can also see this database in MySQL Workbench.

I downloaded and installed the native SBDC driver from MySQL Driver (Mac OSX Version Apache OpenOffice4.1). I restarted LO. When I open a Writer doc and go to Tools > Extensions, I see this SBDC driver listed there.

I then perform the following steps to connect my new database:

  1. New Database
  2. Connect to existing > MySQL
  3. Connect directly
  4. Database name: myfirstsqldb
  5. Server: localhost
  6. Port: 3306 (verified this is correct in MySQL Workbench; there is an active connection on this port)
  7. User name: root
  8. Tick the “password required” box
  9. Input my root password I set up originally with MySQL.
  10. Click the Test Connection box.

I get the following error message, “A driver is not registered for the URL sdbc:mysql:mysqlc:localhost:3306:myfirstsqldb"

What did I do wrong?

Cannot access data from LO Base file from forms
#2

Hello,

From what you have listed these are the same settings as on my current system (Linux Mint 18.3). Have been using v1.2 for a few years now and other SDBC versions have never worked for me. You may want to try the other version posted there for Mac (the one for AOO 4.0) or even the older v1.1.0 as it can’t really hurt & changing extensions is quick ( delete old & add in new).

If still not working, I have also connected using JDBC and ODBC connector from MySQL. Takes more to set up and they are somewhat slower than SDBC but work.

JDBC → Connector/J 5.1.46

Install instructions → Setting up LibreOffice to access MySQL database with JDBC driver

and

How to connect LibreOffice Base to MySQL using JDBC on Mac OS X

ODBC → Connector/ODBC 8.0.12

Will assist wherever possible but have little background in Mac - know about Java situation & that LO menus are a bit different to access :slight_smile:

#3

Ratslinger, thanks for your response.

Regarding the AOO SBDC driver— I tried both of the older OS X versions (4.0 and 1.1.0), but was not able to open them after downloading. Each one gave me the error message, "The extension ‘MySQL SDBC Driver for Apache OpenOffice’ does not work on this computer.”.

#4

I can look into the OBDC and JDBC drivers, but I really wanted to use the SBDC driver, as I understand that it works better / faster / smoother in connecting MySQL databases to Base. Is there really no other option for me?

#5

Sorry but none I know of. The SDBC driver has not been updated in years. If what you have tried doesn’t work, I don’t see much difference by using a JDBC or ODBC connector. At least these are being updated. I did use JDBC for some time when I first installed MySQL and had no problems. Currently I am migrating to PostgreSQL (Firebird 3 Server was second choice) each performing better with more capabilities than MySQL.

#6

Thanks again. So maybe it’s worthwhile for me to switch to PostgreSQL instead of MySQL?

#7

That’s tough to say. What you haven’t mentioned is what you are using Base for. With the inclusion of Firebird 3 embedded getting closer why isn’t this a consideration? I just finished a side by side test using my main DB from MySQL (almost daily use) and ran same in PostgreSQL, Firebird 3 server & Firebird 3 embedded and saw no problem in any with data or relative performance. I am moving to PostgreSQL for other reasons.

#8

I am using Base for a number of things: uploading financial and online sales channel spreadsheets so that I can more easily obtain the info I need (spreadsheets weren’t working well); business and personal bookkeeping; inventory management and purchase orders; personal recipe database; personal books and media database, etc. I have future plans to integrate a database with a website, so I would need to be able to use something like phpmyadmin.

#9

As far as using the Firebird 3 embedded database, right now I am using the latest stable version of LO, which I don’t think has this as an option. I’ve been wary of using something that’s still in experimental mode. I’m trying to make this all as easy as possible on myself. But I’m open to the possibility of using it, if you think that might be a better option.

#10

OK. Ultimately it’s your decision. I’m gravitating toward PostgreSQL because of spatial capabilities. Not a concern for you. Have tested a lot with many different DB’s and will keep most around for testing.

One impressive thing about using Firebird 3 server (been around for decades now) is that you can also take a DB, create what is a backup (.fbk file) and put it into a LO Firebird embedded .odb and it becomes portable. LO can also connect to a Firebird file (read DB).

#11

BTW You can turn on Firebird embedded thru menu Tools->options->LibreOffice->Advanced & click checkbox to turn on experimental features.

Now PostgreSQL has been around longer & is a bit more sophisticated than Firebird which is better than MySQL (or it’s sister MariaDB) at least from the use & research I’ve done (MY opinion). And as far as use on Websites, this is not an area I am comfortable in discussing.

Bottom line is that it’s a choice you will need to make.

#12

@LibertyBelle Just a note about php & databases. From php manual -

Yes, PHP can access virtually any database available today.

Also about Firebird - Interfacing PHP and Firebird

Also - phpMyAdmin is a free and open source administration tool for MySQL and MariaDB

#13

Ratslinger, thank you so much for all your help. I know it’s my decision, it’s just a bit overwhelming for a novice in this area. I’m digesting all this, and I may have more questions. You said, “One impressive thing about using Firebird 3 server (been around for decades now) is that you can also take a DB, create what is a backup (.fbk file) and put it into a LO Firebird embedded .odb and it becomes portable.” What do you mean by it becoming portable?

#14

Glad to help where possible.

Portable meaning the odb can contain what is on the server without it actually being connected to the server. Now this doesn’t mean the server DB is updated. Let’s say (as you mentioned) you have the server & a DB with recipes on a DB there (500 of them). Now sharing them with another means some mechanism to get it to them. You can actually make a copy of that DB & with some minor work place it in an .odb as an embedded Firebird DB.

#15

This process is presented in my answer on this post → Base: HSQLDB to Firebird migration for existing databases.

#16

OK. I am now thinking either Firebird 3 Server or Firebird embedded. Why might someone choose one of these over the other?

#17

Here is my preference. I choose to have a main NON embedded database which can be used from a variety of sources and not just LO. My main DB will be PostgreSQL (discussed earlier) but Firebird 3 Server will be kept for a variety of reasons. First it is a good DB. It also has some nice features as already mentioned (check out the docs mentioned in my link). What is a plus is the availability of Firebird embedded in conjunction with server. A lot of flexibility!

#18

If you have Firebird server then embedded is always an option. If using only embedded you may limit capabilities. From your list of needs, potential growth may be easier with server and embedded doesn’t go away.

Finally, if you try a server (whichever) & don’t like it you can always move to a different one although depending on data may take some effort. You can also have multiple servers installed. I have a number of them currently installed on Mint 18.3 & some others elsewhere.

#19

I think I will go with the Firebird 3 Server then. That’s perfect. Having many options is great, but it sure can make your head swim!

#20

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.