Jumbabox

I thought I would OpenOffice through another test and see how easy it is to build a DataPilot on a MySQL database.

Related : MySQL ODBC driver Tutorial
Related : Using an OpenOffice Calc DataPilot with a Base Database

Step 1 - Setting up a connection for MySQL

What I first realised is that there is no direct way to link a DataPilot (in a Calc sheet) to a MySQL database, you will need to connect via a Base database.

Calc DataPilot -<- Registered Database -<- Base -<- ODBC -<- MySQL

In this example I will be using an ODBC connection which I have already setup in Windows ODBC Data Sources.

If you are having trouble configuring your MySQL ODBC configuration I suggest you read my post on configuring your MySQL ODBC driver, it runs through the tutorial from installation to the configuration.

So lets begin!

Open up a new Base database.

You will be presented with a Database Wizard asking if you would like to create a new database or connect to an existing database, check the Connect to an existing database radio button option, from the drop down list select MySQL and then click the Next >> button.

The next part of the Database Wizard will ask if you would like to connect using ODBC or connect using JDBC, click the radio button next to the connect using ODBC and click the Next >> button again.

This will take you to Step 3 of the Database Wizard, this step will ask you to Browse for a MySQL ODBC driver on your computer. Click the Browse button to open up a Data Source list, in this example I have previously set one up called JumbaBox (you can find out how I did this here).

You can see from my ODBC Data Source Administrator the JumbaBox data source. You can view your Data Sources by clicking on Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC) you can also get to this area by clicking the Organize… button from the Database Wizard (seen above).

Click the OK button after highlighting your data source and then click the Next >> button.

Step 4 of the wizard will ask you for a Username to the MySQL database, in my example my Username is root and I have not set a password, if you have set a password check the Password Required box, then click the Test Connection… button.

If you did not check the Password required check box you should receive a connection successful message box.

If you did check the Password required check box you should receive a pop up dialog box asking you for your password, make sure you check the Remember password until session check box to remove any annoying password pop up boxes, if your password is correct you should also get the same connection successful message box (seen above).

The last step of the Wizard will ask you to:

• Yes, register the database for me : Registering will enable you to use the connection we just setup inside any other applications in the OpenOffice suite.
• No, don’t register the database : In this example, not registering the database will just setup the connection for the Base database.
• Open the database for editing : Open up the blank Base database for editing
• Create tables using the table wizard : Opens up the create table wizard (giving you the ability to create tables in your MySQL database)

I selected to register the database as I want to use this database in Calc for the DataPilot and also selected to Open the database for editing as I would like to show some other handy features in OpenOffice. Click the Finish button to continue.

If you pick any of the options above you will still need to save your Base database, pick a name for your database and click the Save button.

Step 2 - Managing your MySQL Database through Base

You can now see that you are in OpenOffice Base database management area for the database we just saved, click on the Tables option. You should be able to see all the tables in your MySQL database, in this example I have only one table and that is called books.

Just like Access, Base is simply a database management tool. In this example I will create a query to show that you base a DataPilot both on a table and a query.

I will post in the future on how you can use OpenOffice Base as a front end GUI for MySQL.

Select the Queries option from the Database area, this will now make the Query area active.

Click on the Create Query in Design View… option, this will open up a Query Design area which is very similar to Access’s Query Design area.

The first prompt will ask you too add a table, in this example I will adding the only table I have in my database, books. Select your table or tables and click the Add and then the Close button.

I created a quick query to select all books which have the topic Java.

I then saved the query with the name SampleQuery.

To check if the query worked, I doubled clicked on the SampleQuery icon (seen above). A view of the resulting data should display inside the SampleQuery window.

Now I think we are ready to build our DataPilot in Calc!

Step 3 - Creating a DataPilot

Open up a blank OpenOffice Calc workbook.

Once your Calc workbook has opened, click on the Data > DataPilot > Start… option.

A Select source dialog box will pop up, click on the Data source registered in OpenOffice.org radio button and click the OK button.

After clicking the OK button the you will be asked to specify a:

• Database : A database which has been registered
• Data Source : An available data source from the database
• Type : The data source type; a Sheet (Table) or Query (in Base) or native SQL

Here are examples of different data types I could use:

Sheet Data Type

Selecting the Sheet data type is the same as selecting a Table. In this example the books table is the only available Data source.

Query Data Type

Earlier I decided to create a Query in Base based on the MySQL database, where I only wanted to select all books which had the topic of Java. In this example you can see from the Data source drop down box the SampleQuery query is available.

SQL Data Type

This data type allows you to enter any SQL statement into the Data source drop down box, in the example below I’m selecting all the records from the books table.

I decided to choose the Sheet data type, after you are happy with your Data source selection click the OK button. The DataPilot layout wizard should pop up.

After dragging and dropping the available fields, my DataPilot layout wizard now looks like this:

Clicking the OK button will create your DataPilot.

Done! So that’s how you build a DataPilot on a MySQL database in OpenOffice!

More details on DataPilot settings can be found in my post on building a DataPilot with an Base database.

If you have any problems or questions please comment.

Enjoy!