One of the programs that have been missing from the Open Source world is a simple database system. MS Access provides this capability on Windows platforms, but until OpenOffice 2.0 was released, with their version of Base, there wasn't anything equivalent (to my knowledge) in open source. The main reason for this is that until relatively recently, those using open source had more technical knowledge, and were much more likely to understand what a database is. MS Access blurs the lines and makes creating a database easy, but making it right still needed some knowledge. So, open source users were comfortable working with true relational database systems, like PostgreSQL, or MySQL.
(Note: OpenOffice is often referred to as OOo which means OpenOffice.Org)
But, sometimes it is much more convenient to have a nice graphical interface for working with the data in a database, or creating a simple database. This is the need that OpenOffice Base (and MS Access) fills. While it IS possible to create a "native" database, it will also connect to MySQL and Postgres (and others), to provide a way to generate quick reports, or edit the data. But, this process is not as clear cut as it might seem at first. Knowledge of the underlying systems is still needed. But, that learning curve is easy.
So lets look at the steps we need to connect to a MySQL database. I'll include both the server side setup for those who may need it, and then examine how to connect to the server from Base.
Server Side
First, you need a MySQL database installed. It doesn't matter too much if you are connecting to a Linux or Windows server (MySQL runs on both), but you do have to tell the server to allow remote connections. It also doesn't matter where you install MySQL - whichever box is running it is your server. This means you can install MySQL on your desktop and then treat "localhost" as the database server.
If the database server is NOT on the same computer as the client software (OOo Base in this case), then you need to tell MySQL to allow remote connections. There are two steps to this - first make sure MySQL is not configured for localhost access only, and then grant permissions to the appropriate user. If the database server IS on your local workstation, you might be able to skip the server side configurations.
I have MySQL installed on my Gentoo Linux server. On this box, there is a file found at /etc/mysql/my.cnf. This file had to be changed like this:
#security: #using "localhost" in connects use sockets by default #skip-networking #bind-address = 127.0.0.1 bind-address = 192.168.0.10
Here, we've changed the security section of the file to comment out the line that set the bind-address to the localhost address, and then added a similar line to set the bind-address to the server's IP address. If this is not done, then MySQL will not be able to communicate over the local network. This is smart really - you SHOULD be controlling access to database servers. So, this is a reasonable default value and I would recommend leaving it alone unless you do need to access the database directly from remote workstations.
Don't forget to restart the database services for this change to take effect. On my server the command "/etc/init.d/mysql restart" would take care of this.
The next part we need to handle is granting permission to a user account to allow it remote access. This is done from within MySQL itself (a database user account, not a Linux/Windows user account). How you do this depends on how you manage your database - some will be using phpmyadmin (a web interface to MySQL), while others will be using the command line. I prefer the command line myself, so here's the commands I needed to enter:
mysql -u myuser -p # the mysql command above will connect to the default database # for the specified user, and prompt for the appropriate passwod. # You can specify a specific database by adding the database name # at the end of the command (separated by a space) mysql>grant all privileges on mydatabase.* to myuser@remotehost identified by 'mypassword' Query OK, 0 rows affected (0.00 sec) mysql>quit
Here, we first establish a connection to the database. Use your own username/password and database name. When you are successful, you will be presented with the mysql console. Here we enter the command to grant all levels of access to all tables in the database (mydatabase.* - don't forget the period and star). This access is granted to the user specified by "myuser@remotehost" - user your username here, and substitute remotehost for the name or IP of the box you want the user to be able to connect from. The final section of the command sets the password for that user/host combination - the the mypassword part to be something private.
When the grant command is run, you should see the Query OK line. If you don't, you'll be looking at an error of some sort, which means you typed something wrong. Check the command again carefully. Or you might have connected to MySQL with a user that does not have permissions to change the privileges.
Then we simply quit the mysql console.
At this point, the database server is set for remote access. Remember the username, password, and database name - we'll need this info shortly.
Client Side
So, now we can fire up OpenOffice Base. If you are not presented with the Database Wizard, go to File => New => Database
Select the command to "Connect to an Existing Database", select "MySQL" from the list box, and then click the Next button.
On the next screen you have the option of using ODBC, or JDBC to connect to the database. These are just different communication frameworks for talking to a database. ODBC has been around longer than JDBC, but is normally not available (by default) on Linux systems. So, we'll use the JDBC method. This does mean though that you need to have a java runtime installed, which I'll assume is already in place. So, select "Connect using JDBC", and click the Next button.
We have two text boxes to fill in here - the name of the database, and the Server URL. Enter the database name (this would be the MySQL database name). The Server URL can be a special type of URL, but luckily the name (or IP address) of the server is all that is needed here. If your database server is on your local workstation, you should enter "localhost" here, otherwise you enter the name or address you would use to ping your server. Change the Port number if needed, but the default of 3306 will likely be sufficient.
The MySQL JDBC driver class is important - if this is not filled in for you already, enter "com.mysql.jdbc.Driver". Make sure to hit the "Test class" button to make sure OpenOffice is using java correctly. If you do not see a successful message, then this needs to be resolved before continuing - it's likely an underlying Java problem, which is a whole different topic.
Click the Next button when you are ready.
Here we need to enter the database username. This is the same name that we granted access to in the server side section above. Check the Password Required checkbox, then click on the Test Connection button. Enter the password when prompted. You should be greeted with a "The connection was established successfully" message. If not, then you need to review the steps above and see what was missed. (Probably the grant privileges command).
Click the Next button when ready.
This final page of the wizard allows us to save our settings, and then either create a table right away, or open the database for use. Click the Finish button to save the database.
And there we have it. At this point we can now create tables, queries, and reports. Or we can edit the data directly.
NOTE: The grant privileges command can be used to restrict what a user is allowed to do. So, if you are using a restricted user to connect to the database, some commands (like creating a table) may result in errors. And of course there are any number of settings that may restrict your access, or cause undesired results. Unfortunately I can't forsee everything you might encounter, but the above notes should be useful as a starting point.